DSXchange

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?

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.

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

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.

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

Can I use the "BUSINESS RULE" stage to implement the same?
If yes how?

No.

The best you can do is to identify every row loaded so that you can unwind the load.