SQL Code -913

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
Mandar999
Member
Posts: 5
Joined: Fri Aug 14, 2009 1:07 am
Location: MUMBAI, INDIA

SQL Code -913

Post by Mandar999 » Fri Aug 14, 2009 1:17 am

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.

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Fri Aug 14, 2009 12:38 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.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Mandar999
Member
Posts: 5
Joined: Fri Aug 14, 2009 1:07 am
Location: MUMBAI, INDIA

Post by Mandar999 » Fri Aug 14, 2009 10:00 pm

Thanks a lot.

Mandar999
Member
Posts: 5
Joined: Fri Aug 14, 2009 1:07 am
Location: MUMBAI, INDIA

Post by Mandar999 » Mon Aug 17, 2009 1:45 pm

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

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Tue Aug 18, 2009 1:56 pm

Did you look into the manuals about -913 and -911?
Regards,
Anuj

Mandar999
Member
Posts: 5
Joined: Fri Aug 14, 2009 1:07 am
Location: MUMBAI, INDIA

Post by Mandar999 » Tue Aug 18, 2009 9:54 pm

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

Post Reply

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



Other References
Mainframe Tools and others