I'm trying to load a file into DB2 database table using DB2 Load. I'm able to load the file, using the following statement from the unix prompt.

db2 +p -x load from BOM_Sales_20130326.txt of DEL MODIFIED BY COLDEL\| timestampformat=\"YYYY-MM-DD hh:mm:ss\" insert into GEO_SALES.SDM_STL_VFRSK_SALES NONRECOVERABLE

But, when I try to call DB2 Load from a script after parameterising many of the options, it does not seem to work.

For example, I have re-written the statement as follows:

db2 +p -x "load client from $FILE_DIR/$filenm of DEL MODIFIED BY COLDEL\$FILE_DELMTR timestampformat=\"YYYY-MM-DD HH:MM:SS\" insert into $TGT_SCHEMA_NM.$FILE_STG_TBL_TGT NONRECOVERABLE"

All the variable are assigned with correct values prior to calling the statemant. But it seemed to fail due to wrong timestampformat. The error is as follows:

SQL3191N The field in row "F2-1", column "1" which begins with "2013-03-26|STL|5678|D|3212345" does not match the user specified DATEFORMAT, TIMEFORMAT, or TIMESTAMPFORMAT. The row will be rejected.

In fact, my need is to give the value "timestampformat" also as a parameter, as follows: db2 +p -x "load client from $FILE_DIR/$filenm of DEL MODIFIED BY COLDEL\$FILE_DELMTR timestampformat=$DTTIME_FMT insert into $TGT_SCHEMA_NM.$FILE_STG_TBL_TGT NONRECOVERABLE"

Since it was not working, just for the testing purpose, the variable $DTTIME_FMT was replaced with \"YYYY-MM-DD HH:MM:SS\". As said earlier, that too does not seem to work.

Request your help to fix this and also like to know whether I can parameterise the all these load options as I've tried.

Thanks, Tom

  • @ChrisAldrich The first paragraph says "unix prompt" if that helps.
    – WarrenT
    Apr 29, 2013 at 20:07
  • curious as to why you have client in your script but you don't on the unix prompt? Apr 29, 2013 at 20:56
  • Sure looks to me like your data doesn't match the TIMESTAMPFORMAT you have specified. The rejected row looks like a date (YYYY-MM-DD), not a timestamp in YYYY-MM-DD HH:MM:SS format Apr 30, 2013 at 4:49

1 Answer 1


Create your statement as follows and check your data format in your table and ensure that you match the time as well (for example, |2011-01-23 14:44:36|2011-01-23 15:44:44|20140512| is the my time format in my DDL)

load from myexport.del of del modified by coldel| delprioritychar dateformat=\"YYYY-MM-DD\" timestampformat=\"YYYY-MM-DD HH:MM:SS TT\"  insert into mytable

Good luck

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.