DB2 sqlcode -253 err on non-atomic insert, recs not written

JimPeterson · Post by **JimPeterson** » Mon Jan 05, 2009 10:20 am

I have a job that creates rows for a DB2 table. The last link shows that x rows are written, there are no warnings in the DStage log for the DB2 stage, but after the job completes a count of the rows in DB2 shows x minus 49. We can see some -253 sqlcodes in the DB2 log, error on non-atomic insert. The DB2 stage is set to auto-buffer, so what I suspect is happening is that DStage is buffering the data, DB2 encounters an error of some sort with a mass insert and fails to write the rows, but it doesn't report this back to DStage. The only way we know there is a problem is by checking row counts and then checking the DB2 log. Has anyone seen anything like this before, and is there a fix?

Thanks in advance for your help.

MarkB · Post by **MarkB** » Tue Jan 06, 2009 6:03 am

So according to the error log you are getting some row inserts but erors and/or warnings. What are you using in DataStage - the DB2 UDB API stage or the DB2/UDB Enterprise stage?

If using the UDB API stage, have you tried setting the Buffering Mode to default instead of Auto and then running the job? Just a thought.

JimPeterson · Post by **JimPeterson** » Wed Jan 07, 2009 11:15 am

MarkB wrote:So according to the error log you are getting some row inserts but erors and/or warnings. What are you using in DataStage - the DB2 UDB API stage or the DB2/UDB Enterprise stage?

If using the UDB API stage, have you tried setting the Buffering Mode to default instead of Auto and then running the job? Just a thought.

The DStage log doesn't indicate that any records are being dropped, it reports x inserts when in fact x-49 get written. I'm using the API stage. Isn't Auto the default buffering mode? I'll check that with our administrator and see if tweaking that has an effect on the output. Thanks.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 07, 2009 4:23 pm

Anything in the DB2 logs (for example duplicate key errors)?