Monitoring the dynamic statement cache with READS calls

This strategy can help you detect and diagnose performance problems for those cached dynamic SQL statements.

An IFI program that monitors the dynamic statement cache should include these steps:

1. Acquire and initialize storage areas for common IFI communication areas.
2. Issue an IFI COMMAND call to start monitor class 29. This step enables statistics collection for statements in the dynamic statement cache.
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 after enough time has elapsed for a reasonable amount of activity to occur in the dynamic statement cache.
5. Set up the qualification area for a READS call for IFCID 316
6. Set up the IFCID area to request data for IFCID 316.
7. Issue an IFI READS call to retrieve the qualifying cached SQL statements.
8. Examine the contents of the return area.
   For a statement with unexpected statistics values:

   1. Obtain the statement name and statement ID from the IFCID 316 data.
   2. Set up the qualification area for a READS call for IFCID 317.
   3. Set up the IFCID area to request data for IFCID 317.
   4. Issue a READS call for IFCID 317 to get the entire text of the statement.
   5. Obtain the statement text from the return area.
   6. Use the statement text to execute an SQL EXPLAIN statement.
   7. Fetch the EXPLAIN results from the PLAN_TABLE.
9. Issue an IFI COMMAND call to stop performance trace class 29 for IFCID 318.