DB2 enterprise stage - Reject handling

Aparna_A · Post by **Aparna_A** » Wed Nov 29, 2006 1:26 pm

Hi,

This is in regard with the DB2 EE stage...
I have done a search in the forum..but was not able to find an appropriate solution

The problem is as follows.

The job is a simple one..Seqfile -> transformer -> DB2 enterprise stage(Upsert strategy ) ---> Seq file (to capture rejects)
The DB2 table has one attribute whose data type is DATE.

When all the records are good records (i.e., no rejects are happening from Database), we are able to see all the records in the table.
But whenever we have few rejects in the source file (ex: invalid date ), these rejects are being passed to the reject file, but at the same time we are not able to account for all the other good records in the DB Table.

When the same file (with rejects) was run with a commit interval of 1(one), all the good records were inserted into the table.

Is it that when some records are rejected, the database is not able to commit the other inserted records!! Why are those good records missing?
The input link to the DB2EE stage shows the correct record count, and the reject link also shows the correct count...In the datastage director also, we are getting the correct record count (Inserted+Updated+Rejected), but still these records are missing in the database.

Can somebody suggest what is the problem and what can be the solution?

Thanks

Nageshsunkoji · Post by **Nageshsunkoji** » Wed Nov 29, 2006 6:09 pm

Hi Aparna,

Please go through this link, you can get very clear idea about Reject handling.

http://blogs.ittoolbox.com/bi/websphere ... akage-7169

trobinson · Post by **trobinson** » Wed Nov 29, 2006 9:06 pm

Seems pretty straightforward, therefore I'm not sure I understand the problem. If all the good records are committed when the commit level is 1 and some good records are being dropped when the commit level is set to a number higher than 1 then isn't it obvious that when a rollback occurs when a bad date record is encountered, some good records get rollbacked too?

khanparwaz · Post by **khanparwaz** » Wed Nov 29, 2006 10:36 pm

trobinson wrote:Seems pretty straightforward, therefore I'm not sure I understand the problem. If all the good records are committed when the commit level is 1 and some good records are being dropped when the commit level is set to a number higher than 1 then isn't it obvious that when a rollback occurs when a bad date record is encountered, some good records get rollbacked too?

If commit level is set more than 1 then unless the number of records inserted is more than the commit level the records will not be commited. So in your case this may be the problem. What is the commit level set other than 1 ?
Say for example if the commit level set is 100. You have 110 records from source out of which 11 records are rejected means you have 99 good records. These 99 records get inserted into table but as the commit level is 100 these records dont get commited and you will not see them in the table.
So please check your commit level and the number of good records. If number of good records is less than commit level then this is the problem.

chulett · Post by **chulett** » Thu Nov 30, 2006 12:49 am

khanparwaz wrote:If commit level is set more than 1 then unless the number of records inserted is more than the commit level the records will not be commited. So in your case this may be the problem. What is the commit level set other than 1 ?

I'm sorry, but unless this is in regard to a specific bug with the DB2 EE stage - this is completely false. I mean, think about it... the only time all records are successfully committed is when you either use a commit level of 1 or the number of records loaded is an exact multiple of the value?

What you should be finding is that records are commited every X records, whatever X is - and the remainder are commited when the stage closes normally.

Aparna_A · Post by **Aparna_A** » Thu Nov 30, 2006 1:28 pm

Ya ..thats true..if the commit point is greater than the number of records being loaded, then a commit will be issued when the stage / job closes (correct me if i am wrong).

Scenario 1:
Input number of records : 90
Bad records : 0
For this scenario, all records were loaded (and committed), irrespective of the commit point being 1 or 2000 (or any value).

Scenario 2:
Input number of records : 90
Bad records : 2
For this scenario, all records (ie., 90-2 = 88 ) were loaded (and committed) ONLY when the commit point is 1. For any other commit point, the number of records loaded was less than 88 and this number is not the same for every run.
Why is this happening only when DB is rejecting records...
Is it a bug in Enterprise stage or am I presuming any?

Thanks