Monitoring deadlocks and timeouts from a monitor program

Monitor programs can call IFI functions to monitor deadlocks and timeouts.

About this task

An IFI program that monitors deadlocks and timeouts of cached statements might include the following steps:

End program-specific programming interface information.

Procedure

To monitor deadlocks and timeouts through the instrumentation facility interface:

Create a monitor program that uses the following actions:
  1. Acquire and initialize storage areas for common IFI communication areas.
  2. Start statistics trace class 3, or performance trace class 6, for IFCID 0172 to monitor deadlocks, or for IFCID 196 to monitor timeouts.
  3. Put the IFI program into a wait state. During this time, SQL applications in the subsystem execute dynamic SQL statements by using the dynamic statement cache.
  4. Resume the IFI program when a deadlock or timeout occurs.
  5. Issue a READA request to obtain IFCID 172 or IFCID 196 trace data.
  6. Obtain the cached statement ID of the statement that was involved in the deadlock or timeout from the IFCID 172 or IFCID 196 trace data. Using the statement ID, set up the qualification area for a READS call for IFCID 316 or IFCID 317.
  7. Set up the IFCID area to request data for IFCID 316 or IFCID 0317.
  8. Issue an IFI READS call to retrieve the qualifying cached SQL statement.
  9. Examine the contents of the return area.
  10. Issue an IFI COMMAND call to stop statistics trace class 3 for IFCID 172 or IFCID 196.