DSXchange

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!

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.

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.

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

I hope I could make myself clear
Thanks for your answers guys!

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.

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