Hey,
Does anyone know the difference between SQL Code -911 and -913 ?
As per my knowledge, -911 or -913 is encountered during deadlock or timeout
-911 is when the ROLLBACK of SQL statement is (successful) done to previous commit
but i don,t know when does -913 occur. Please let me know if anyone
has encountered -913 and the scenario for same.
SQL Code -913
Moderators: Kalicharan, Moderator Group
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
-911 simply states that due to deadlock or a timeout (indicated by the reason code) your UnitOfWork (UOW) has been rolled back. So to start over, you begin at the state of your last commit.
-913 indicates that this particular SQL, that you just tried to execute, has failed because (indicated by reason code) of either a deadlock or a timeout has occured.
To start over, re-execute the last sql.
based on experience an application receiving a -913 should should immediately issue a ROLLBACK and then proceed as a -911.
Idea of doing a ROLLBACK is we know that 2 or more tasks (ours being one) are trying to issue exclusives on a particular area of a table(s). So if we do a ROLLBACK, and process based on last commit, theoretically (proven in practice - banking, insurance) our task will deal with the (eventually) same transaction and the other tasks will be finished.
-913 indicates that this particular SQL, that you just tried to execute, has failed because (indicated by reason code) of either a deadlock or a timeout has occured.
To start over, re-execute the last sql.
based on experience an application receiving a -913 should should immediately issue a ROLLBACK and then proceed as a -911.
Idea of doing a ROLLBACK is we know that 2 or more tasks (ours being one) are trying to issue exclusives on a particular area of a table(s). So if we do a ROLLBACK, and process based on last commit, theoretically (proven in practice - banking, insurance) our task will deal with the (eventually) same transaction and the other tasks will be finished.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
Hi,
As per your reply, i assume that it is not possible to encounter a -913 since it
proceeds as a -911. Is it possible anyway to figure out in any way whether my application has/had encountered a -913 before issuing a -911 abend.
Is it possible to simulate to generate a -913 abend at DB2.
Thanks in advance,
Mandar
As per your reply, i assume that it is not possible to encounter a -913 since it
proceeds as a -911. Is it possible anyway to figure out in any way whether my application has/had encountered a -913 before issuing a -911 abend.
Is it possible to simulate to generate a -913 abend at DB2.
Thanks in advance,
Mandar
-
- Moderator
- Posts: 1625
- Joined: Sat Aug 09, 2008 9:02 am
- Location: Mumbai, India
This is what IBM Db2 manual (for V8) says,
For TSO, Batch, and CAF
When a deadlock or timeout occurs in these environments, DB2 attempts
to roll back the SQL for one of the application processes. If the
ROLLBACK is successful, that application receives SQLCODE -911. If the
ROLLBACK fails, and the application does not abend, the application
receives SQLCODE -913.
For CICS , it says
If you are using CICS and a deadlock occurs, the CICS attachment facility decides whether or not to roll back one of the application processes, based on the value of the ROLBE or ROLBI parameter. If your application process is chosen for rollback, it receives one of two SQLCODEs in the SQLCA:
-911 A SYNCPOINT command with the ROLLBACK option was issued on
behalf of your application process. All updates (CICS
commands and DL/I calls, as well as SQL statements) that
occurred during the current unit of work have been undone.
(SQLSTATE '40001')
-913 A SYNCPOINT command with the ROLLBACK option was not issued.
DB2 rolls back only the incomplete SQL statement that
encountered the deadlock or timed out. CICS does not roll
back any resources. Your application process should either
issue a SYNCPOINT command with the ROLLBACK option itself or
terminate. (SQLSTATE '57033')
Consider using the DSNTIAC subroutine to check the SQLCODE and display
the SQLCA. Your application must take appropriate actions before
resuming.
What i don,t understand is what do we mean by 'ROLLBACK fails' in case of -913.
e.g.
Consider a Cobol-DB2 program in which a total of 1000 recrods are updated and a commit is taken after every 100 updates.
What happens if my program/transaction encounters a deadlock (with another program) while updating 410th record. Since commit has been taken after 400th update,
if a -911 occurs during deadlock, the UOW is rolled back to last commit, i.e. last 10 updates (after 400th update) are lost.
What happens in this case if instead of a -911, my app receives a -913
during deadlock while updating 410th record ?
Will the behaviour/result be same in this case ?
Thanks,
Mandar
For TSO, Batch, and CAF
When a deadlock or timeout occurs in these environments, DB2 attempts
to roll back the SQL for one of the application processes. If the
ROLLBACK is successful, that application receives SQLCODE -911. If the
ROLLBACK fails, and the application does not abend, the application
receives SQLCODE -913.
For CICS , it says
If you are using CICS and a deadlock occurs, the CICS attachment facility decides whether or not to roll back one of the application processes, based on the value of the ROLBE or ROLBI parameter. If your application process is chosen for rollback, it receives one of two SQLCODEs in the SQLCA:
-911 A SYNCPOINT command with the ROLLBACK option was issued on
behalf of your application process. All updates (CICS
commands and DL/I calls, as well as SQL statements) that
occurred during the current unit of work have been undone.
(SQLSTATE '40001')
-913 A SYNCPOINT command with the ROLLBACK option was not issued.
DB2 rolls back only the incomplete SQL statement that
encountered the deadlock or timed out. CICS does not roll
back any resources. Your application process should either
issue a SYNCPOINT command with the ROLLBACK option itself or
terminate. (SQLSTATE '57033')
Consider using the DSNTIAC subroutine to check the SQLCODE and display
the SQLCA. Your application must take appropriate actions before
resuming.
What i don,t understand is what do we mean by 'ROLLBACK fails' in case of -913.
e.g.
Consider a Cobol-DB2 program in which a total of 1000 recrods are updated and a commit is taken after every 100 updates.
What happens if my program/transaction encounters a deadlock (with another program) while updating 410th record. Since commit has been taken after 400th update,
if a -911 occurs during deadlock, the UOW is rolled back to last commit, i.e. last 10 updates (after 400th update) are lost.
What happens in this case if instead of a -911, my app receives a -913
during deadlock while updating 410th record ?
Will the behaviour/result be same in this case ?
Thanks,
Mandar
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page