|
View previous topic :: View next topic
|
| Author |
Message |
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
I want to know what is the role of using explicit ROLLBACK in a cobol program in the error handling paragraph..In my program when SQLCODE not = 0 / 100 then they do an explicit ROLLBACK and abort the module throwing the error message.
As far as my knowledge when the program terminates abnormally then the rollback hapens automatically in DB2 right? Pls correct me if my understanding is wrong!
Thanks, |
|
| Back to top |
|
 |
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
DB2 will roll back the current unit of work when the program is terminated abnormally, so the explicit ROLLBACK statement is redundant.
There could be application designs that will require explicit ROLLBACK of the business transactions performed, followed by some updates to e.g. logging tables. These updates must then be explicitly committed before abending the program, as abending will still terminate the current unit of work with rollback. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
normally,
if a program abends,
you do not have the opportunity to issue any instructions.
a non-zero sql code is not a program abend. it is the result of a db2 process,
where a <zero return code indicates the sql was not successful
and a >zero return code indicates a warning.
an explicit roll-back allows an application to terminate normally,
which is a much less resource intensive process than forcing an abend in a module,
which requires the op-system to go thru tons of code to process the abend. |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
Scenario
=========
Let me explain:
In the cobol program which I am refering to, when the SQLCODE from an update table statement is NOT = 0 or 100 they do 2 things.
a) First the rollback para is performed,In which they do the an explicit ROLLBACK and do a final update on the table.
b)Next they call a subroutine to force abend the program displaying the query information which failed and the SQLCODE.
Problem
==========
But the problem is when they display the SQLCODE then it shows 0 in the spool, because the rollback statement's SQLCODE overiddes the original SQLCODE returned from the update query. This is meisleading and wrong..
Understanding
==========
So I am thinking is it wise to eliminate the explicit rollback from the code, as the DB2 should automatically do a rollback when the update query fails and the pgm terminates. If I eliminate the explicit rollback then the SQLCODE displayed in the spool will be corrected.
Please correct me if my understanding is wrong!
Thank You |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
| Why not just save the SQLCODE in another variable BEFORE doing the ROOLBACK and displaying this other variable? That would probably require much less work than actually figuring out the impact of changing the way the code works now with the ROLLBACK. |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
Dick, your signature is really funny  |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| Robert Sample wrote: |
| Why not just save the SQLCODE in another variable BEFORE doing the ROOLBACK and displaying this other variable? That would probably require much less work than actually figuring out the impact of changing the way the code works now with the ROLLBACK. |
Hi Robert,
Definately a good idea  Will try this..but it may become a little messy because they use a standard subroutine to force abend the pgm..and that routine takes the value directly from SQLCA..
I will try the following logic.
1. Run update query
2. Check if SQLCODE NOT = 0
a) move SQLCODE to WS-SQLCODE
b) peform explicit ROLLBACK + Final UPDATE
c) Move WS-SQLCODE to SQLCODE
d) call the standard ABORT subroutine which uses the values of SQLCA.
Does this look good?
Thanks, |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| It may sound beaureucratic but they are very particular to use that subroutine to abend he program..The called subroutine has the SQLCA passed to it as one of the linkage parameters.. |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
Wait a minute, won't the "final update" be rolled back by the abend issued by the ABORT subroutine?
For this to work, the sequence would have to be:
ROLLBACK
Perform final update
COMMIT
Call ABORT routine
abend
Or does the ABORT subroutine not issue an abend? |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| don.leahy wrote: |
Wait a minute, won't the "final update" be rolled back by the abend issued by the ABORT subroutine?
For this to work, the sequence would have to be:
ROLLBACK
Perform final update
COMMIT
Call ABORT routine
abend
Or does the ABORT subroutine not issue an abend? |
Hmm..valid point buddy..I need to put my thinking cap on |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
| Rijit wrote: |
Problem
==========
But the problem is when they display the SQLCODE then it shows 0 in the spool, because the rollback statement's SQLCODE overiddes the original SQLCODE returned from the update query. This is meisleading and wrong..
|
I will suggest that you declare a copy of SQLCA in your working storage. No need to declare the individual fields, just an area big enough to hold the SQLCA area.
Just before issuing the rollback, move the contents of the original SQLCA to the copy area, and use that as call parameter when you call the force abend routine, or move the copy SQLCA information back to the original area before calling the abend code.
That way the intervening DB2 calls does not interfere with your original abend situation.
Remember, you should still be able to issue a forced abend documenting DB2 calls that goes wrong in the period between the rollback and the last commit. |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| Superb idea!! Thanks Kjeld |
|
| Back to top |
|
|
|
|
