db2 timestamp format not loading

kristo5747 · #1 (**permalink**) 09-19-11, 12:27

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

Code:

    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

Code:

    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

Code:

    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:

Code:

    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:

Code:

SQL0574N  DEFAULT value or IDENTITY attribute value is not valid for column

.

Timestamp format in source my data file looks like this

Code:

    2002/06/18 17:11:02.000

How can I format my timestamp columns?

ARWinner · #2 (**permalink**) 09-19-11, 12:46

Read the manual on the LOAD command and look for the MODIFIED BY TIMESTAMPFORMAT clause.

Andy

Marcus_A · #3 (**permalink**) 09-19-11, 14:27

Timestamp data is always stored the same way in an internal DB2 format, so don't try and modify the create table statement. As mentioned by ARWinner, you need to do more research on how to specify format for load command (or format you want when you query the data).

Internet Services

Web Development

Digital Marketing

Consumer Tech