Page 1 of 1

DB2 EE stage - rows getting rejected

Posted: Mon Jul 31, 2006 5:43 am
by Kirtikumar
Hi

I am using the DB2 EE stage in upsert mode.

The rows are getting till the target DB2 EE stage but not getting inserted or updated in the table.

The logs are gerating following warning message:

Code: Select all

dbee_CCAccDim_tgt,0: Records Inserted: 0
Records Used for Update: 0
Records Rejected: 184,240
Total Records: 184,240.
There is no log in the Director showing the reason of this reject. If I replce this stage with DB2 API, the records get inserted/updated into target table.

Any inputs will be appreciated.

Posted: Mon Jul 31, 2006 6:01 am
by opdas
What does the message file says?

Posted: Mon Jul 31, 2006 6:10 am
by Kirtikumar
What is message file? If it has to do with message handler, then I am not bothered abt why the log is showing message as info and not warning.

I am worried about why the rows are not getting inserted or updated in the table.

Posted: Mon Jul 31, 2006 7:47 am
by vmcburney
To quickly see the rejection error message add a peek stage to your job with a reject link out of your EE database stage. Within the DB stage set the "Reject" optional property to true, you should be able to find it somewhere in the properties list. Re-run your job. The sql code and sql error code should go down the reject link to the peek stage where you can view them in the Director log.

Long term you should think about doing this in all your jobs and sending the sql error messages to some type of message handling and reporting process or file.

no columns name SQLSTATE/CODE

Posted: Wed Aug 02, 2006 5:02 am
by Kirtikumar
Vincent,

Thanks for this useful info.

What I was expecting was like DB API stage it should throw warnings with SQL codes in DS director. So if this job is in the sequence, sequence should stop.

But if this is genearated as info message then it is very difficult to catch it.

Also, when I used a seq file, only rejected rows were stored in this file and there was no sqlcode stuff. When I used peek as u suggested, it gave me sql code and state value in DS logs even though there was no column in peek stage with column names SQLSTATE/CODE. So is there any way to catch these codes on this reject link and then do some processing some thing like generating a file with detailed error message.

Why is it not showing the columns SQLSTATE/CODE and adding it directly in DS logs?

Posted: Wed Aug 02, 2006 9:23 pm
by vmcburney
There is a trick to it. Turn on colum propogation out of the DB2 stage reject link and type in the two column names manually. They will appear! For a peek stage it automatically shows all propogated columns such as the existing rejected columns and the two new database columns. Changing this to a sequential file output requires you to name the columns where the output of the peek stage should show you the column names and column types.

Posted: Wed Aug 02, 2006 10:41 pm
by kumar_s
You can get these tricks if you go through the blog released by vincent on "Unplesent discharge".
Perhaps what Om Prakash would have suggested you is to check the log file generated by DB2 in its server. That would give more information on teh reason of reject.

Posted: Thu Aug 03, 2006 12:46 am
by Kirtikumar
Vincent & Kumar

Thanks for your inputs. I will try this with column propagation and then naming the columns as they were in the logs. I think we should do some sort of rejection handling stuff for DB2 EE stage.

Kumar, where can I find the blog you have mentioned below?

Posted: Thu Aug 03, 2006 1:36 am
by kumar_s
http://blogs.ittoolbox.com/bi/websphere ... akage-7169

Go thruogh this, you can find the link to exact blog.

Posted: Thu Aug 03, 2006 2:25 am
by vmcburney
Hey heah, I had forgotten about that blog entry! It even has a link to my wiki entry on retrieving SQL codes for DB2 stages! The other article of interest is Data Quality Firewall 2: inside an ETL job which has some bitmaps of how to pass your reject flow into a shared container.

Posted: Thu Aug 03, 2006 4:29 am
by Kirtikumar
Thanks Vincent!!!

The article is a great help to handle the rejects. In server we used to handle the errors and warnings using link variables.
In PX it is a more time consuming process.

Posted: Thu Aug 03, 2006 7:13 pm
by kumar_s
Kirtikumar wrote:Thanks Vincent!!!

The article is a great help to handle the rejects. In server we used to handle the errors and warnings using link variables.
In PX it is a more time consuming process.
Just wait for few days, you can find another blog for this from the blog-man. :wink: