Archives of the TeradataForum

Message Posted: Tue, 27 May 2008 @ 22:20:58 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Donna:

The .EXPORT OUTFILE /xx/xxxx/xxxx/xxxxxxx/ MODE RECORD FORMAT TEXT; is a FastExport command.

The MODE RECORD asks for no null indicators to be used and of course, FORMAT TEXT says character type of data. FastExport looks at the data that it is creating to determine if it is fixed or variable length. Varchar pretty much guarantees a variable length record and FastExport would put a 2-byte binary variable length indicator (vli) on the front of each record written into the file. It will not do this with fixed length records.

The equivalent BTEQ command would be: .EXPORT FILE=/xx/xxxx/xxxx/xxxxxxx/

As I mentioned earlier, the downside to this is that it would output a first record containing the names of the column(s) selected. The record would need to be deleted before the load.

If they used FastExport with the above command and if I remember correctly, each column that was concatenated was converted to fixed length before the concatenation - so it should be fine.

As mentioned by someone else, you could get rid of the last '|' symbol. However, I don't think that is the problem.

Your FastLoad used: SET RECORD VARTEXT "|"

Oh, I looked in my trash bin and found your original posting. I think what I was mentioning above is taking place - variable length records. The export script does this for each column: COALESCE(CAST(last_activity_dt AS CHAR(10)),'~')||'|'||

This means that each part of the final row that has a value in last_activity_dt will be 13 characters long. However, the '~' will be 1 byte long. Hence a variable length record and a vli. Check the input file and see if it has what looks like garbage at the front of each record.

If so, I would suggest changing the select to: CAST(COALESCE(last_activity_dt,'~') AS CHAR(10)||'|'|| (for each of the columns) This way, every record will have the same number of characters between the '|' and all records will be fixed length. So, no vli should be used.

That's my conclusion and I hope it helps you.

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

	<Prev	Next>		<<First	<Prev	Next>	Last>>