I am struggling to load a simple pipe | delimited file into a table. The table is loaded but the UPI is populated with NULL or a different value.
Table Structure --------------- CREATE SET TABLE DV1E_WORK_IN.emp1 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( EmpNo INTEGER, Empname VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC) UNIQUE PRIMARY INDEX ( EmpNo );
Flat file being loaded -------------------- 456|ERere 12345454|Prpr 331|sasa
Data loaded in Emp1 after mload execution -------------------------- Empno Empname ------ --------- NULL ERere 12345454 Prpr 331 sasa
i have two questions- 1) Why the empno for first record i.e. Empno 456 is getting populated as NULL? If all the other rows except the first row is being loaded corrrectly then what's wrong with the first record? 2) It seems for FORMAT VARTEXT the layout should(not must) specify fields with datatype as VARCHAR. When target table contains Date/Datetime columns then how do we cast them? Can we do this in the DML statement?
First, I think you should be careful to always remove your id and password when sharing scripts.
1. If the first record fails but all the other records are ok then there may be some unprintable character in that first column first row.
2. You have to specify VARCHAR because the input file is ASCII characters. Datatype DATE is an internal format represented as a 4 byte binary data. So, mload reads the character field containing CHARACTERS (not numbers). Then these are cast internally as DATES. If your file were NOT character representations then you would have to specify the field as DATE, or INTEGER etc.
You can prove this by trying a Fastexport. Create a table with a date column. Then use FastExport and specify FORMAT TEXT to extract.
Next, try the same export but specify FORMAT BINARY.
You will see that the characters are represented as ASCII in the FORMAT TEXT example and in the FORMAT BINARY the date is unreadable.