Monitoring static SQL statements with READS calls

You can use READS requests from an IFI application to monitor static SQL statements.

To create an IFI program that monitors static SQL statements uses the following steps:

1. Acquire and initialize storage areas for common IFI communication areas.
2. Issue an IFI COMMAND call to start performance trace class monitor class 29 for IFCID 0400. This step enables statistics collection for static SQL statements.
3. Put the IFI program into a wait state. During this time, SQL applications in the subsystem execute static SQL statements.
4. Resume the IFI program after enough time has elapsed for a reasonable amount of static SQL statement activity to occur.
5. Set up the qualification area for a READS call for IFCID 0401.
6. Set up the IFCID area to request data for IFCID 0401.
7. Examine the contents of the return area.
8. For a statement with unexpected statistics values:
   1. Obtain the statement ID from the IFCID 0401 return area.
   2. Query the STMT_ID column of the SYSIBM.SYSPACKSTMT catalog table, by using the statement ID, and obtain the statement text from the STATEMENT column.
   3. Use the statement text to execute an SQL EXPLAIN statement.
   4. Fetch the EXPLAIN results from the PLAN_TABLE.
9. Issue an IFI COMMAND call to stop performance trace class 29 for IFCID 0400.