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.
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.
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?
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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?
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.
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
