Executing Detector for Db2 for z/OS (PDT) and encountered a deadlock issue with remote threads.
The application should have received -913 SQLCODEs in order to make a decision on whether to rollback or commit.
From Detector for that time period noted no -913 SQLCODEs were reported on the SQL Error Interval Display but several -911 SQLCODEs were located. Within the Detector SQLCA Data Area Display for the SQL-911 the following was noted:
NOTE: -913 SQLCODES MAY DISPLAY AS A - 911 SQLCODE IN DETECTOR. WHEN VIEWING -911 SQLCODE PLEASE CHECK THE APPLICATION TO VERIFY THE ROLLBACK OCCURRED (-911). -913 IMPLIES THE ROLLBACK WAS NOT REQUESTED OR FAILED.
Is there a reason why Detector cannot report on the SQL-913 error?
At the end of the execution of a failed SQL statement due to DEADLOCK or TIMEOUT the SQL code is set to -913, as the ROLLBACK of the unit of work has not yet been attempted. Detector at this point assumes that the ROLLBACK
will be performed and records a -911 SQL code. However the ROLLBACK may fail and the SQL code will remain a -913. Detector does no further processing of the SQL statement and therefore is not able to reset the SQL code to -913.
Detector collects statistics by gaining control at the start and end of SQL statement execution. If an SQL error occurs this is reflected back to Detector when control is gained at SQL statement end. At the point of control available to Detector, Db2 has not made the decision to rollback. The decision to rollback work and the determination of successful rollback do not occur within the Detector data collection scope of control. Detector cannot reliably distinguish between a -911 and a -913 and so all occurrences are reported as -911 with the caveat.