Date formatting for DB2 load utility

Shilpa.Bhat · #1 (**permalink**) 12-21-07, 05:40

Hi,

I am trying to populate date and time into DB2 table using DB2 load utility script.

The date coming from source is in the format of YYYY-MM-DD(eg.2006-12-01) and the time in the form of hh:mm:ss

i am using the following utility script to perform the operation:

Code:

db2 LOAD CLIENT FROM <source filename> OF ASC modified by identitymissing dateformat="YYYY-MM-DD"  timeformat="HH:MM:SS"  
striptblanks reclen=32 method L (1 12,13 13,14 24,24 32) messages <error msg filename>INSERT INTO Table Name(Col1,Col2,Col3,Col4,Col5)

But this is giving a error :
SQL3192N In the filetmod a user specified format "DATEFORMAT" beginning with
the string "." is not valid.

Could you help me out with this.

Regards,
Shilpa Bhat

fdegrijs · #2 (**permalink**) 12-21-07, 11:38

dear Shilpa.Bhat,

You should give all srings within single quotes, so '2007-12-21' would do it.

Regards,
FdeGrijs

Shilpa.Bhat · #3 (**permalink**) 12-24-07, 01:34

even after using single quotes 'YYYY-MM-DD',it still is giving the same error.

SQL3192N In the filetmod a user specified format "DATEFORMAT" beginning with
the string "." is not valid.

n_i · #4 (**permalink**) 12-24-07, 10:25

I'm thinking that may be the quotation marks get interpreted by the shell. Try enclosing the entire DB2 command in quotes:

Code:

db2 "load client ... modified by dateformat=\"YYYYMMDD\" ..."

Peter.Vanroose · #5 (**permalink**) 12-25-07, 09:09

Quote:

Originally Posted by n_i

I'm thinking that may be the quotation marks get interpreted by the shell.

Indeed; single quotes are even easier: (assuming this is a unix shell, not cmd.exe)

Code:

db2 'LOAD CLIENT FROM <source filename> OF ASC
     modified by identitymissing dateformat="YYYY-MM-DD"
     timeformat="HH:MM:SS" striptblanks reclen=32
     method L (1 12,13 13,14 24,24 32) messages <error msg filename>
     INSERT INTO Table Name(Col1,Col2,Col3,Col4,Col5)'

Internet Services

Web Development

Digital Marketing

Consumer Tech