DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
DrackThor
Participant



Joined: 28 Dec 2015
Posts: 3
Location: Austria
Points: 34

Post Posted: Thu May 05, 2016 11:38 pm Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Server
OS: Unix
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54072
Location: Sydney, Australia
Points: 293282

Post Posted: Thu May 05, 2016 11:56 pm Reply with quote    Back to top    

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.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne

Last edited by ray.wurlod on Fri May 06, 2016 12:00 am; edited 2 times in total
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42283
Location: Denver, CO
Points: 217108

Post Posted: Thu May 05, 2016 11:58 pm Reply with quote    Back to top    

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

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
DrackThor
Participant



Joined: 28 Dec 2015
Posts: 3
Location: Austria
Points: 34

Post Posted: Wed May 11, 2016 11:37 pm Reply with quote    Back to top    

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 Crying or Very sad

I hope I could make myself clear
Thanks for your answers guys! Smile
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54072
Location: Sydney, Australia
Points: 293282

Post Posted: Thu May 12, 2016 12:37 am Reply with quote    Back to top    

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.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42283
Location: Denver, CO
Points: 217108

Post Posted: Thu May 12, 2016 6:23 am Reply with quote    Back to top    

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

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours