link.DBMSCODE
Moderators: chulett, rschirm, roy
link.DBMSCODE
I have a simple job,where i have a source file with 2000 records
and a target oracle stage.I have a constraint
link1.col='XYZ' than i load the data into Oracle table.
so in the source there are 20 records which match this constraint.
10 records in the source file are duplicates,so i am getting the
warning message
"ORA-00001: unique constraint violated".
DBMS.CODE=ORA-00001
so i want to capture these 10 rejected records into a sequential file.
i give the following constraint in the
rejectslink
link1.DBMSCODE ='ORA-00001'.
But i see that all the other records which do not match the
constraint link1.col='XYZ' are also captured into the rejects.
How can i capture just the 10 records into the rejects file.
Thanks
sheema
and a target oracle stage.I have a constraint
link1.col='XYZ' than i load the data into Oracle table.
so in the source there are 20 records which match this constraint.
10 records in the source file are duplicates,so i am getting the
warning message
"ORA-00001: unique constraint violated".
DBMS.CODE=ORA-00001
so i want to capture these 10 rejected records into a sequential file.
i give the following constraint in the
rejectslink
link1.DBMSCODE ='ORA-00001'.
But i see that all the other records which do not match the
constraint link1.col='XYZ' are also captured into the rejects.
How can i capture just the 10 records into the rejects file.
Thanks
sheema
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
This doesn't make a lot of sense. Without the 'Reject Row' checked, all that will go down the link are any rows that satisfy the constraint. So if what you have in that constraint is:sheema wrote:The Reject Row box is unchecked and the array size is also set to 1.
The reject link is the last link in the link ordering too.
But still i do not get the expected rows into rejects file
Code: Select all
link1.DBMSCODE ='ORA-00001'
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your Transformer stage has two outputs; one going to the Oracle table (let's call this LinkA) and one handling rejects (LinkB). LinkA has NO constraint expression. LinkB is marked as handling rejects. Define an integer column on LinkB derived as the output link variable LinkA.DBMSCODE.
If you need other constraints, do this in a preceding Transformer stage. Handle the database rejects separately.
If you need other constraints, do this in a preceding Transformer stage. Handle the database rejects separately.
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:
Code: Select all
----> Transformer1 -----> Transformer2 -----> Oracle
| | LinkA
| | LinkB
V V
Rejects1 Rejects2
Transformer2 looks after any rows rejected by Oracle, by having LinkA with no constraint expression and LinkB handling rejected rows.
On LinkB, as well as any columns from the input link, you can define extra columns, for example DBMS_Code. This should be an integer. Its value is the error code returned from Oracle through the link variable LinkA.DBMSCODE.
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.
Target Table Rejection
Sheema,
Give Constraint as <outputlink.rejected> for Rejected Rows link.
And make sure in source you have given filter condition.
Thanks,
Rachitha.
Give Constraint as <outputlink.rejected> for Rejected Rows link.
And make sure in source you have given filter condition.
Thanks,
Rachitha.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes, but you will need separate columns on the rejects link for the DBMSCODE or LASTERR or SQLSTATE link variables from the ten other output links. Name these columns appropriately, such as Table1.DBMScode.
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.