Roll back issue in a service
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
Roll back issue in a service
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
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
Kenny
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
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>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
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
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>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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
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
Kenny
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
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>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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
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>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
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
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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
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
Kenny
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
...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>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>