Archives of the TeradataForum

Message Posted: Thu, 14 Nov 2002 @ 20:13:48 GMT

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

<-- Anonymously Posted: Thursday, November 14, 2002 15:08 -->

I am looking for some insight as to what I am doing wrong with my MultiLoad script. The data was exported from Teradata using FastExport and will be loaded back into Teradata using MultiLoad Upsert. I have tried everything that I can think of with the LAYOUT command and FIELD definitions in Multiload to get this to work. I have also scoured the documentation and knowledge repository on NCRAtYourService. I have a sneaking suspicion that the character in position 2 (two) which is a hidden character (which displays as a square in Microsft Word) is throwing the utility off. Stripping these characters would be time consuming as the four data files range from 150MB to 390MB each.

Fast Export SQL simply concatenated all of the data together. The first two characters in the sample data below were inserted by the FastExport utility.

Export command from Fast Export:
.EXPORT
   OUTFILE "C:\TEMP\FE_AGG_TEST1.DAT"
   FORMAT TEXT
   MODE RECORD;
.END EXPORT;

Field Definitions in table:
      COL1 SMALLINT NOT NULL,
      COL2 SMALLINT NOT NULL,
      COL3 SMALLINT NOT NULL,
      COL4 DATE FORMAT 'YYYYMMDD' NOT NULL,
      COL5 DECIMAL(11,2) NOT NULL COMPRESS 0.00 ,
      COL6 INTEGER NOT NULL COMPRESS 1 ,
      COL7 DECIMAL(11,2) NOT NULL COMPRESS 0.00 ,
      COL8 DECIMAL(11,2) NOT NULL COMPRESS 0.00 ,
      COL9 DECIMAL(11,2) NOT NULL COMPRESS 0.00 ,
      COL10 INTEGER NOT NULL,
      COL11 DECIMAL(11,2) NOT NULL COMPRESS 0.00 ,
      COL12 INTEGER NOT NULL,
      COL13 DECIMAL(11,2) NOT NULL COMPRESS 0.00 ,
      COL14 DECIMAL(11,2) NOT NULL COMPRESS 0.00 ,
      COL15 DECIMAL(11,2) NOT NULL COMPRESS 0.00

Current MultiLoad Layout:

.LAYOUT AGG_DATA;
.FIELD COL1 4 VARCHAR(6);
.FIELD COL2 * VARCHAR(6);
.FIELD COL3  * VARCHAR(6);
.FIELD COL4 * VARCHAR(8);
.FIELD COL5 * VARCHAR(13);
.FIELD COL6 * VARCHAR(11);
.FIELD COL7 * VARCHAR(13);
.FIELD COL8 * VARCHAR(13);
.FIELD COL9 * VARCHAR(13);
.FIELD COL10 * VARCHAR(11);
.FIELD COL12 * VARCHAR(13);
.FIELD COL13 * VARCHAR(11);
.FIELD COL14 * VARCHAR(13);
.FIELD COL15 * VARCHAR(13);

MultiLoad Import:
.IMPORT
   INFILE "C:\TEMP\FE_AGG_TEST1.DAT"
   FORMAT VARTEXT '|' DISPLAY ERRORS
   LAYOUT AGG_DATA
   APPLY UPSERT_DML;

I have tried:
FILLER field to skip the first 2 characters
FILLER field to skip the first three characters
Set the starting position of the PKY_ID FIELD to 4 instead of *
Removed the last field definition to give
Counted and recounted the number of FIELD definitions comparted to the
table

Resized the VARCHAR datatypes increasing and decreasing various fields

Sample Data:

R|49|700|45|20020202|114.19|19|63.71|.00|198.62|16|1285.15|427|1138.05|19.40| 742.65
S|62|850|149|20011006|380.52|150|106.30|.00|.00|98|3276.31|1138|2138.23|44.35 |185.54
W|775|544|106|20020126|3338.00|70|2875.13|.00|191.12|69|4065.61|224|3452.58|10.23|297.52
S|93|23|174|20011006|684.29|25|496.48|.00|285.89|25|2802.91|681|2082.72|16.93 |682.41
T|545|200|16|20011229|995.72|58|691.16|.00|384.70|57|4190.78|803|2881.91|14.35|923.70
X|96|116|71|20011006|1653.19|127|1062.43|.00|474.54|110|7411.19|2046|5041.12| 97.63|910.12

Error Message Returned by MultiLoad:
UTY4017 Not enough data in vartext record number 1

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