Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18

    Unanswered: db2 timestamp format not loading

    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?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Read the manual on the LOAD command and look for the MODIFIED BY TIMESTAMPFORMAT clause.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •