DB2 Load Failure due to timestampformat

db2load

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

Best Answer

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

Related Solutions

DB2 LOAD data into table – Interpreting the error messages

According to IBM SQL3227W states:

"Record token token1 refers to user record number token2. Explanation

An error or warning was encountered during LOAD, IMPORT or EXPORT of a table. CPU parallelism was greater than 1 at the time the problem was encountered, and an SQL message was written which identified the user record with a special unique token. This message serves to map the unique record token to the record number of the source user data. User response

Refer to the original SQL message returned for appropriate action."

So this refers you back to the previous error. And the next one SQL3185W says:

"The previous error occurred while processing data from row row-number of the input file. Explanation

This message provides the identification of the row where the error occurred for the previous message listed in the message file (for example, SQL3306). User response

No action is required."

So...Phil nailed it on the head, we really need to see what comes before these messages. Do you have a log of the output that you could post to your question? We could better help you with interpreting results then.

That being said, I would highly encourage you to poke around Information Center like I did as it can best help you find what those pesky "SQL" messages mean. The links I found were for 9.5. Just switch to the version of DB2 that you are using to make sure you have the most accurate information. I use Information Center for just about everything.

EDIT: Your updated helps a lot. Here is the error:
SQL3116W  The field value in row "F12-33856" and column "9" is missing, but
the target column is not nullable.
SQL3185W  The previous error occurred while processing data from row
"F12-33856" of the input file.
SQL3227W  Record token "F12-33856" refers to user record number "440439".**
In short, it looks like a null was written to "column 9" of our table and that you have a not null constraint on that column. Now the trick is locating which record had the issue. It is marked with two tokens it looks like. (F12-33856 and 440439). I guess I'd start with 440439 first. That seems to me like it could be the primary key. Just a guess. But at least you know your issue is a null value for a non-null field.

Db2 – Parallel inserts cause deadlock on DB2

Also check the LOCKTIMEOUT, DLCHKTIME, and LOCKSIZE parameters. This article from DBI has been helpful to know what to set them to. It slants toward warehousing, but does mention settings for OLTP as well.

Related Question