Page 1 of 1

SQL Code -913

Posted: Fri Aug 14, 2009 1:17 am
by Mandar999
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.

Posted: Fri Aug 14, 2009 12:38 pm
by dbzTHEdinosauer
-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.

Posted: Fri Aug 14, 2009 10:00 pm
by Mandar999
Thanks a lot.

Posted: Mon Aug 17, 2009 1:45 pm
by Mandar999
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

Posted: Tue Aug 18, 2009 1:56 pm
by Anuj Dhawan
Did you look into the manuals about -913 and -911?

Posted: Tue Aug 18, 2009 9:54 pm
by Mandar999
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