MVS Job - Rollback all the Inserts/Updates

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
apraman
Participant
Posts: 47
Joined: Mon Sep 12, 2005 5:26 am

MVS Job - Rollback all the Inserts/Updates

Post by apraman »

In MVS jobs, is there any way to ROLLBACK the ALL INSERTS/UPDATES made from job if any ONE OF THE INSERT/UPDATE FAILS?

For example:

From a main flow, I am trying to
1 - Insert db2 Table1
2 - Update db2 Table 2
3 - Insert db2 Table 3

My Insert 1 and Update was done successfully
BUT Insert 3 fails due to some reason.
Now I need to ROLL BACK the Insert 1 and Update 2.

It is noticed that the Insert 1 and Update was reflected in the respective table and JOB has finished with RC <> 0.

1. Can I implement within the MVS jobs? If yes, How to do it within MVS jobs?
OR
2 . Do I need to change the Generated COBOL Code for the Job?
Arun
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I do not believe that this level of transaction control is possible in DataStage/390. Nor can I envisage any mechanism where changing the generated COBOL could do it. Your problem is that the COMMITs for 1 and 2 have already been issued. You need some mechanism for wrapping all three in an outer transaction, and for rolling that back. As I said, I don't think you can do it easily (at all?) in DataStage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

If you set the "Rows per commit" value to zero (which is the default) on the Environment page under Job Properties, then the only commit will be the implicit commit that happens when the COBOL program ends.

Any unexpected SQLCODE will cause the COBOL program to issue a rollback and exit with RC=0004.
Do I need to change the Generated COBOL Code for the Job?
NEVER make any manual changes to generated code!

Mike
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I don't think that the rows/transaction setting gives global transaction control - each is only for its own link. Therefore an error on Table3 would issue the rollback for Table3, but not for Table1 and Table2. At least that's how I understand it.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

If I recall correctly, I don't think you get any link-level transaction control at all in a mainframe job. I think the global setting is all that is available. I don't recall how the generated code increments its internal commit counter.

Mike
apraman
Participant
Posts: 47
Joined: Mon Sep 12, 2005 5:26 am

Post by apraman »

Can I use the "BUSINESS RULE" stage to implement the same?
If yes how? :idea:
Arun
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

No.

The best you can do is to identify every row loaded so that you can unwind the load.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply