catching rejected records - parallel job
Moderators: chulett, rschirm, roy
catching rejected records - parallel job
i am trying to catch rejected records while inserting records into oracle database. tried with server job, even though log has the error message, none of the variables like REJECTEDCODE or DBMSCODE had values in it.
it had values when there is an issue with the column values. what i want to catch is 'foreign key constraints or unique key constraints rejetcs'. opened a ticket with IBM, he said server stages have issues, try parallel job. so i created a parallel job , and i need to know how to capture those rejected records. it does have variables like DBMSCODE or REJECTEDCODE . pl tell me how do i capture the rejects.
it had values when there is an issue with the column values. what i want to catch is 'foreign key constraints or unique key constraints rejetcs'. opened a ticket with IBM, he said server stages have issues, try parallel job. so i created a parallel job , and i need to know how to capture those rejected records. it does have variables like DBMSCODE or REJECTEDCODE . pl tell me how do i capture the rejects.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If I recall correctly, you can use a reject link from the Oracle Enterprise stage. This gives you options to add extra columns to the reject link (look in the available options to add).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Having set it to true and drawn the reject link, check the Columns on the reject link - you will see sqlcode there (automatically, not an option as I at first thought).
To do insert only, you need to check first whether the key already exists in the table. Depending upon the size of the table, use a sparse lookup or pre-load a list of keys into something that can serve a reference input link to a Lookup stage. Use a Filter stage to transmit only rows for which the returned key value is null.
To do insert only, you need to check first whether the key already exists in the table. Depending upon the size of the table, use a sparse lookup or pre-load a list of keys into something that can serve a reference input link to a Lookup stage. Use a Filter stage to transmit only rows for which the returned key value is null.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Which piece of this was unclear?
ray.wurlod wrote:To do insert only, you need to check first whether the key already exists in the table. Depending upon the size of the table, use a sparse lookup or pre-load a list of keys into something that can serve a reference input link to a Lookup stage. Use a Filter stage to transmit only rows for which the returned key value is null.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 56
- Joined: Mon Oct 16, 2006 7:32 am
Ray ,
I dont think we can have reject captured for oracle enterprise stage, when we use bulkload option.we have to see the log file in scratch directory to find this . Also one important point to note is eventhough there is error and
the error count is below 50 , datastage will report that everything was sucessfull .This scenario is because datastage doesn not allow us to modify the errors parameter for sqlldr and it sets the errors=50 by default.
I dont think we can have reject captured for oracle enterprise stage, when we use bulkload option.we have to see the log file in scratch directory to find this . Also one important point to note is eventhough there is error and
the error count is below 50 , datastage will report that everything was sucessfull .This scenario is because datastage doesn not allow us to modify the errors parameter for sqlldr and it sets the errors=50 by default.
JT
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That is true for write mode Load. You must check the log (and maybe bad) file from sqlldr.
However, the OP is using Upsert mode, and bemoans the fact that there's no Insert only option with that.
However, the OP is using Upsert mode, and bemoans the fact that there's no Insert only option with that.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Server solution: the DBMSCODE will be available in the transformer after the link that goes to the Oracle OCI stage. This code value can be used in a constraint on a "reject" link to for example a Sequential file. If the DBMSCODE is non-zero, the row that just failed on the above link would flow down the "reject" link.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
I wrote in some detail about trapping rejected rows from the Enterprise database stage in this blog post. The easiest way to work out what is going on in your reject link is to point it to a Peek stage and run the job. You should be able to see the database message fields and values in the Director log. The reject link out of a database stage is only valid if you have the right reject option switched on and have selected an action that supports rejects: inserts, upserts, updates, deletes. The bulk load has its own method of rejecting external to DataStage.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
all the stages in Datastage Enterprise ediion 7.5.2 cant capture 'unique key constraints or foreign key constraints' even though you see the error in the log. i tried with server DRS , oracle, odbc stages, and also with parallel oracle enterprise, DRS and odbc stages. none of it worked. i did this along with ibm support guy on webex. if u provide a reject link in parallel job, it gives 'unable to identify sql code error'. IBM guy said he will send me a patch.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: