I encountered some constraint issues when loading data from file into table

SQL3109N  The utility is beginning to load data from file
SQL3500W  The utility is beginning the "LOAD" phase at time "07/30/2012
SQL3519W  Begin Load Consistency Point. Input record count = "0".
SQL3520W  Load Consistency Point was successful.
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".**

db2 LOAD client from data  of del MESSAGES msg replace INTO TABLE_L ;

I thought this message is telling me that we have an constraint issue on row 440439, but seems it is not the case. anyone know how to read the messages ?

  • The previous error occurred - What was the previous error? No log entry for it? – Philᵀᴹ Jul 31 '12 at 11:23

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.

  • I have added more details, all I want to know is about where is error line, so that I can take a look at the constraint. and thanks for the advice of poking around the infomation center, just wonders someone might know this for shortcut – zinking Aug 1 '12 at 0:57
  • @zinking - updated my answer to reflect your update. – Chris Aldrich Aug 1 '12 at 12:55
  • it is just not that 440439th row. that's why I am puzzled, but after filtering the 9th null column, we did find some record, but it is just not that row. this is exactly what I am asking for. @ChrisAldrich – zinking Aug 2 '12 at 1:16
  • @zinking - According to the documentation above, that id is a "token" used to represent the row. That makes it tougher. My thoughts were either the Nth row (which you said it isn't) or the primary key of the row. – Chris Aldrich Aug 2 '12 at 13:20

The error message basically says this: For the line 440439, there is nothing between delimiters for 9th column. So it will be regarded as NULL but the table does not allow NULL values for 9th column. So the line 440439 is not loaded (inserted) into table.


I am new to DB2 but after a series of troubleshooting and success I can give you some help.

When files are exported be careful to ensure that they are well-delimited.

For example, with the following table

    (C1     VARCHAR(30)  ,
     C2     TIMESTAMP    , 
     C3     DECIMAL(7,2) ,
     C4     CHAR(1))

Your file must be delimited as follows:

"Hummel",,187.43, H
"Grieg", 2006-05-23-, 66.34, G
"Satie", 2006-05-22-, 818.23, I

Some exporting tools like Aginity will combine two to tree fields in one field. Therefore it is read as a larger field by the system. Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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