Roll back issue in a service

Dedicated to DataStage and DataStage TX editions featuring IBM<sup>®</sup> Service-Oriented Architectures.

Moderators: chulett, rschirm

Post Reply
kennyapril
Participant
Posts: 248
Joined: Fri Jul 30, 2010 9:04 am

Roll back issue in a service

Post by kennyapril »

I have a service oriented job which has around 30 to 40 odbc PX connectors.

I use most of the connectors to insert or update, When the service is called and a request is sent sometimes the service aborts which ends with inserting or updating only some of the tables, my requirement is if the service aborts then there should be a roll back so that either ALL or NONE of the tables should be loaded if an error occurs.

At present my settings in ODBC connectors are
record count 2000
array size 2000
Autocommitmode OFF
IsolationLevel Read Uncommitted

Please suggest me any changes for the roll back transaction
Regards,
Kenny
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Is it an "always on" service with ISDinput Stage? (or a batch job invoked by an incoming service request [both kinds of jobs can be enabled for ISD]) ?

What release? 8.1? 8.5?

Do you have Server also?

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Also....what is your request rate?

and is this unit of work "per incoming request" or "for the whole day" of the service being running (or other unit of time), etc.?

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
kennyapril
Participant
Posts: 248
Joined: Fri Jul 30, 2010 9:04 am

Post by kennyapril »

It is a batch job which is invoked as a service from ISD and the job as WISD input and WISD output stages.

This is 8.0.1 release and the job is a parallel job.

As this is always on service it runs 24*7 i.e always.

The request is around 6 fields and this is validated with some tables and then inserted or updated into some tables and then finally the response is two fields.


Thanks
Regards,
Kenny
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Really no easy way to do this in EE prior to 8.5 (where we have the end-of-wave and the ability to do multiple input links to a single Connector).

Best advice is to re-write it as a Server Job. Provided that all the tables are in a single database, set up your logic so that it ultimately goes thru one final Transformer with many output links into a target relational stage (this technique is most consistent with ODBC Stage).

You can put all the links under a single unit of work...

I like to control it explicitly...having a seventh and eighth link that do COMMIT or ROLLBACK after inspecting the specific return codes of the other six....

...and then a ninth link to send back a nice message (or not) to the waiting invoker of the service....

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
kennyapril
Participant
Posts: 248
Joined: Fri Jul 30, 2010 9:04 am

Post by kennyapril »

To do the same design in a server job I cannot see some stages in server palette such as lookup,funnel etc


I have around 10 links coming from 10 transformers and then Funnel stage to WISD output.

Based on this where do I need to use the output links?

please suggest
Regards,
Kenny
lstsaur
Participant
Posts: 1139
Joined: Thu Oct 21, 2004 9:59 pm

Post by lstsaur »

Server job doesn't have Funnel, Lookup stages.
kennyapril
Participant
Posts: 248
Joined: Fri Jul 30, 2010 9:04 am

Post by kennyapril »

are there any stages with the same properties in server job?

how can the links do commit or roll back ?

I never designed a server job......when do we use the server jobs?
Regards,
Kenny
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Ah ....a long discussion that will probably take this thread into many pages.

Suffice to say that they are useful for specific things, especially those that need to take up very little resources (one or just a few processes) and are focused (especially prior to 8.5) on single row activities....making them perfect for ISD type Jobs like yours.

Nearly every critical stage has a match on Server (or vice versa)....Link Collector will bring links together, assuming they have same metadata.....put transformers on them in front and after your Link Collector....

You will have to do a lot of playing. But this is the way to get a single unit of work to multiple tables in the same database. Contact me offline....I think I have a .dsx that goes into it.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Yah... the key in Server is the ability to use a single target ODBC stage for all of this work and then a single COMMIT or ROLLBACK for the entire unit of work. Ernie helped me do this very thing (on a somewhat smaller scale) a number of years ago. And, as noted, not something easily conveyed in a post or three.
-craig

"You can never have too many knives" -- Logan Nine Fingers
kennyapril
Participant
Posts: 248
Joined: Fri Jul 30, 2010 9:04 am

Post by kennyapril »

Thanks for the Info.

Actually we have 8.5 in development and planning for 8.5 in test and prod eventually.

But as we dont have test and prod in 8.5 and 8.5 is incompatible to 8.0 we are
designing in 8.0 dev.

I will try to design it in 8.5 once we get test and prod, can you please let me know what changes do I need to do to the present parallel job if I do it in 8.5 based on my rollback issue.

Thanks
Regards,
Kenny
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

There will be lots to learn and play with. Among the Stages to review are:

...new Connector, which can take multiple input links (so that multiple INSERTs (etc) to different tables can be in the same unit of work.

...end of wave...so that you can control what the transaction boundary is.

This is simpler than doing it in Server, but it can't be controlled as explicitly. I've tested it, but haven't had a chance to exercise it for return codes, etc.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
Post Reply