Hello,
I am doing an insert from a Sequential File to a Db2 Stage table. There are no contraints for the input link. If the target table consists of an incoming row then the row will not be inserted. I have a reject link to capture the records that were not inserted. The problem I am facing is that the reject link does not capture these records....
how do i enable my job to capture these records ?
reject handling
Moderators: chulett, rschirm, roy
-
JoshGeorge
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Firstly you might want to re-design your job. Look up the target table before inserting and reject the existing records explicitly using constraints. From reject link you can 'capture' rejected records.
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
All I did was checked the reject row option in the constraint section for the link that goes to the reject file.
When the Update Action in the Db2 Stage was set to update or insert new rows....and when i place a character value to a numeric field in the transformer, I am able to capture all the records as rejected record due to incorrect assignment.
But when I change the update action to "Insert rows without cleaning" The rejects are not captured and neither is it stated in the director that N rows were rejected. Further when I try to test for duplicate records by trying to insert existing records again, no records are inserted and these records are not captured as rejects either.
When the Update Action in the Db2 Stage was set to update or insert new rows....and when i place a character value to a numeric field in the transformer, I am able to capture all the records as rejected record due to incorrect assignment.
But when I change the update action to "Insert rows without cleaning" The rejects are not captured and neither is it stated in the director that N rows were rejected. Further when I try to test for duplicate records by trying to insert existing records again, no records are inserted and these records are not captured as rejects either.
chulett wrote:How is your 'reject link' configured?...
Simply changing the update action shouldn't cause your reject row to suddenly stop working. I'm not familiar with the DB2 stage, though, so can't say if that's a bug or something else is going on. If that stage has either Transaction Size and/or Array Size options, try setting both to 1 and see if that makes it work.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Yes when i set them both to 1 I am able to capture...What is the reason for this ?
chulett wrote:Simply changing the update action shouldn't cause your reject row to suddenly stop working. I'm not familiar with the DB2 stage, though, so can't say if that's a bug or something else is going on. If that stage has either Transaction Size and/or Array Size options, try setting both to 1 and see if that makes it work.
The OCI stage has similar issues. It will still capture rejects but if the Array Size is not equal to 1 it will capture the wrong information. From what I recall, the docs specifically say to set it to 1 for proper reject row capturing. Note this is specifically in regards to capturing physical rejects barfed back up from a database stage. And all you need to set is the Array Size to 1, the Transaction Size can be whatever you please.
As to the why, you'd have to open a support case and ask Ascential / IBM.
As to the why, you'd have to open a support case and ask Ascential / IBM.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: reject handling
Thanks
cosec wrote:Hello,
I am doing an insert from a Sequential File to a Db2 Stage table. There are no contraints for the input link. If the target table consists of an incoming row then the row will not be inserted. I have a reject link to capture the records that were not inserted. The problem I am facing is that the reject link does not capture these records....
how do i enable my job to capture these records ?
Few years back, I had a long discussion with some Datastage people regarding this same issue while working on performance tuning project on Datstage and UDB DB2.
Basically, they were setting the ArraySize=1, in almost all the jobs they had because they wanted to capture any rejects from the database. Some of the load had almost 35-40 million rows. What I couldn't understand was why do we need to capture the reject in that way?
Lets take a simple scenario. I have a sequential file as a source and DB2 as target where I am doing insert. I want to only insert only new rows to the target and reject any duplicate that will be thrown by Datastage. Well, the eaiest way if to just have source, transformer, target and a reject with Array Size & Transaction Size =1. Source had almost 10 million rows. Total time to complete the job was about 5 hours.
So, as a work around, we designed 2 jobs:-
In first job we dumped the sequential file data in another DB2 table.
In second job, we wrote a SQL that joined new DB2 table and Target table and got only new rows that was sent to target for insert. In the same job we write another SQL that gave us all existing rows that was dumped in a text file (treat this as a reject file).
Time taken for completion was 10 minutes. Yes 10 minutes!!! Of coarse not using Array Size and Transaction Size as 1 helps in speeding the job!
Bottom line, use intelligent way to capture rejects. Personally I have never been in favor of using those 2 settings to capture rejects. My thought is the job should be designed such that there should be NO rejects at all. Capture and filter the unwated data before it hits the target
Basically, they were setting the ArraySize=1, in almost all the jobs they had because they wanted to capture any rejects from the database. Some of the load had almost 35-40 million rows. What I couldn't understand was why do we need to capture the reject in that way?
Lets take a simple scenario. I have a sequential file as a source and DB2 as target where I am doing insert. I want to only insert only new rows to the target and reject any duplicate that will be thrown by Datastage. Well, the eaiest way if to just have source, transformer, target and a reject with Array Size & Transaction Size =1. Source had almost 10 million rows. Total time to complete the job was about 5 hours.
So, as a work around, we designed 2 jobs:-
In first job we dumped the sequential file data in another DB2 table.
In second job, we wrote a SQL that joined new DB2 table and Target table and got only new rows that was sent to target for insert. In the same job we write another SQL that gave us all existing rows that was dumped in a text file (treat this as a reject file).
Time taken for completion was 10 minutes. Yes 10 minutes!!! Of coarse not using Array Size and Transaction Size as 1 helps in speeding the job!
Bottom line, use intelligent way to capture rejects. Personally I have never been in favor of using those 2 settings to capture rejects. My thought is the job should be designed such that there should be NO rejects at all. Capture and filter the unwated data before it hits the target