I am attempting to utilize the load command to load data from a "|" delimited file into a table. The table contains four timestamp columns. The data in two the columns is in the format "YYYY-MM-DD hh:mm:ss" and the last two columns are in the "HH:MM" format.
The load statement I am using is:
db2 'LOAD FROM iordl.unl OF DEL MODIFIED BY pagefreespace=0 totalfreespace=0 TIMESTAMPFORMAT="YYYY-MM-DD hh:mm:ss" coldel| chardel"" decpt. MESSAGES iordl.msg INSERT INTO EXCEED.iordl COPY NO INDEXING MODE AUTOSELECT'
The load fails of course because two of the timestamp columns do not match the format defined with TIMESTAMPFORMAT.
My question is, is it possible to change the value of the TIMESTAMPFORMAT to match the data in each column? I have read the documentation concerning the TIMESTAMPFORMAT modifier but have not found anything regarding multiple entries in a single load statement. Any assistance in this manner would be greatly appreciated. Thank you in advance.
Currently DB2 UWL load and import utility only supports a single TIMESTAMPFORMAT for the entire datafile. Unfortunately there isn't a way for you to specify a different TIMESTAMPFORMAT for the last two timestamp fields.
If you cannot re-generate the input file with the last two timestamp in the same format as the first two, you could try to first load the data into a similar temporary table with last columns as time or varchar, then attempt an insert with sub-select back into the original target table.