How to capture oracle exceptions in DS Px
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 13
- Joined: Fri Oct 08, 2004 6:19 am
How to capture oracle exceptions in DS Px
Hi.
I need to capture oracle raised exceptions like Inserting Null into Non-nullable field, lookup failure exception, or any other exception that would cause datastage to abort the job while running. I wish to capture such atributes in a separate table. Please suggest if there is a method to do so in Datastage Px.
I need to capture oracle raised exceptions like Inserting Null into Non-nullable field, lookup failure exception, or any other exception that would cause datastage to abort the job while running. I wish to capture such atributes in a separate table. Please suggest if there is a method to do so in Datastage Px.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
If you are doing an update and not a bulk load then you can use an output reject link. When you choose a write method of Upsert you will see the option appear for "Output Reject Records = False". Set this to try and create an output link leading to a staging file or to your database errors table. You will see the link automatically take the dashed appearance of a reject link.
Failed update rows will be sent down this link. I am not sure if there is any way to get the error message or whether you have to refer back to the job log for these details.
Failed update rows will be sent down this link. I am not sure if there is any way to get the error message or whether you have to refer back to the job log for these details.
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
SQLCODE is included in reject link from Oracle Ent. stage?
I am using a reject link from an Oracle Enterprise stage to capture rejects from an Upsert as you describe. I am particulary interested in the Oracle SQLCODE, but it does not seem to be "automatically" included in the reject link. Can you tell me exactly how you built your reject link?
Thank you.
Thank you.
ailuro wrote:Automatically included in the reject link of an Oracle Enterprise stage with properties mentioned in vmcburney's reply is the sqlcode (Integer).
E.g., -1400 (cannot insert NULL)
Re: SQLCODE is included in reject link from Oracle Ent. stag
Hi mmorelli
Make sure that:
Ora Ent Stage > Input > Properties > Write Method = Upsert
The column sqlcode:Integer will then be included as the last item under:
Output > Columns
Also, don't forget to set:
Input > Properties > Output Reject Records = True
děkuji.
Make sure that:
Ora Ent Stage > Input > Properties > Write Method = Upsert
The column sqlcode:Integer will then be included as the last item under:
Output > Columns
Also, don't forget to set:
Input > Properties > Output Reject Records = True
děkuji.
คาร์โลÂ ตัน
Re: SQLCODE is included in reject link from Oracle Ent. stag
Hi děkuji,
Thanks very much for you help. Unfortunately, a "sqlcode:Integer" column is not automatically being added to our Ora Ent stages when they are configured as you describe.
I wonder if we're using different versions. We have 7.1r2, and I'm using the Oracle Enterprise Stage, apparently named "PxOracle", version 1.0.0, against Oracle 9i.
Your input is greatly appreciated.
Mike
Thanks very much for you help. Unfortunately, a "sqlcode:Integer" column is not automatically being added to our Ora Ent stages when they are configured as you describe.
I wonder if we're using different versions. We have 7.1r2, and I'm using the Oracle Enterprise Stage, apparently named "PxOracle", version 1.0.0, against Oracle 9i.
Your input is greatly appreciated.
Mike
Hi Mike
Ah, we're using v7.5.
Can you manually specify the table definition of the reject link? In v7.5, the columns are read-only and automatically taken from the input link with the additional sqlcode.
If you can, try to specify the table definition as v7.5 would.
PS. "děkuji" means "thank you"
Ah, we're using v7.5.
Can you manually specify the table definition of the reject link? In v7.5, the columns are read-only and automatically taken from the input link with the additional sqlcode.
If you can, try to specify the table definition as v7.5 would.
PS. "děkuji" means "thank you"
คาร์โลÂ ตัน
Using Modify stage to capture SQLCODE on rejects from Oracle
FYI, if anyone is using 7.1r2 and is stuck like I was, a colleague figured it out:
Send the Oracle reject link to a Modify stage which has the specification "SQLCODE=SQLCODE". Then include a column named "SQLCODE" in the output from the Modify stage.
Send the Oracle reject link to a Modify stage which has the specification "SQLCODE=SQLCODE". Then include a column named "SQLCODE" in the output from the Modify stage.
-
- Premium Member
- Posts: 291
- Joined: Wed Sep 26, 2007 11:23 am
- Location: Madrid, Spain
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 291
- Joined: Wed Sep 26, 2007 11:23 am
- Location: Madrid, Spain