Get SQL State of DB connector

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
DrackThor
Participant
Posts: 3
Joined: Mon Dec 28, 2015 2:16 am
Location: Austria

Get SQL State of DB connector

Post by DrackThor »

Hello everybody!

I hope anyone can help me with this issue.
I need to use a DB connector stage (eg. DB2 connector) to do some SQL stuff (delete, update, insert..) and always get the returning SQL state, because I need to react on this SQL state in my sequence.

E.g.: I want to update some records in my DB connector and, if no update was made due to an empty where clause result, abort my sequence. Because DataStage does not recognize an error, when no updates were made, I can't react properly to the SQL operation.

Does anyone of you have experience with controling sequence jobs according to SQL codes?

Thanks for your help!
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Welcome aboard. Willkommen.

Create a reject link from your Connector.

On the page for the reject link you will find the ability to specify what error information you want added to the link, and under what circumstances.

Downstream of that, you can parse the SQL State out of the error text.

You may need to add a BASIC Transformer stage to your job so that you can react instead to the link row count into the Connector stage.
Last edited by ray.wurlod on Fri May 06, 2016 12:00 am, edited 2 times in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Re: Get SQL State of DB connector

Post by chulett »

Welcome.
DrackThor wrote:Because DataStage does not recognize an error, when no updates were made, I can't react properly to the SQL operation.
For my own edification, not knowing DB2, does the database itself consider an update of zero records an error? I ask because Oracle doesn't, i.e. it will happily and successfully update zero records.

Ha! Ray snuck in while I was... composing. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
DrackThor
Participant
Posts: 3
Joined: Mon Dec 28, 2015 2:16 am
Location: Austria

Post by DrackThor »

Thanks ray and chulett for your answers!

1. I do know the feature of a parallel Job's DB-Connector to reject an SQL-Errorcode + Errortext, but I want to get ALL the SQL-States, not only the errors.
E.g.: I insert 2000 records with 500 records per transaction, which makes four transactions which makes 4 SQL codes. If the last one fails I also want the SQL-Codes of the successful transactions, not only the failed one.

2. @chulett: DB2 also doesn't recognice this special situation as an error, due to that also DataStage takes this situation as a successful operation and does not forward any SQL-code to a reject link or something -> no SQL-state for me :cry:

I hope I could make myself clear
Thanks for your answers guys! :)
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

As a general rule, a DataStage Connector will be silent about a successful commit. It may be possible to use Before-SQL to set a state that causes the database to report successful commits.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I would also suggest involving your official support provider, see if they can help here. Let us know what, if anything, you find out!
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply