db2 timestamp format not loading

Question

I am trying to bulk-load data with timestamps into DB2 UDB version 9 and it is not working out.

My target table has been created thusly

CREATE TABLE SHORT
(
 ACCOUNT_NUMBER INTEGER 
, DATA_UPDATE_DATE TIMESTAMP DEFAULT current timestamp
, SCHEDULE_DATE TIMESTAMP DEFAULT current timestamp
...
0)

This is the code for my bulk load

load from /tmp/short.csv of del modified by COLDEL, savecount 500 messages /users/chris/DATA/short.msg INSERT INTO SHORT NONRECOVERABLE DATA BUFFER 500

No rows are loaded. I get an error

SQL0180N  The syntax of the string representation of a datetime value is 
incorrect.  SQLSTATE=22007

I tried forcing the timestamp format when I create my table thusly:

CREATE TABLE SHORT
(
 ACCOUNT_NUMBER INTEGER 
, DATA_UPDATE_DATE TIMESTAMP DEFAULT 'YYYY-MM-DD HH24:MI:SS' 
, SCHEDULE_DATE TIMESTAMP DEFAULT 'YYYY-MM-DD HH24:MI:SS' 
...
0)

but to no avail: SQL0574N DEFAULT value or IDENTITY attribute value is not valid for column. Timestamp format in source my data file looks like this

2002/06/18 17:11:02.000

How can I format my timestamp columns?

Ian Bjorhovde Ian Bjorhovde · Accepted Answer · 2011-09-20T05:43:59

Use the TIMESTAMPFORMAT file type modifier for the LOAD utility to specify the format present in your data files.

load from /tmp/short.csv
    of del
    modified by COLDEL, TIMESTAMPFORMAT="YYYY/MM/DD HH:MM:SS.UUUUUU"
    savecount 500 
    messages /users/chris/DATA/short.msg 
    INSERT INTO SHORT 
    NONRECOVERABLE

db2 timestamp format not loading

2 Answers