You can use READS requests from an IFI application to monitor
static SQL statements.
Procedure
To create an IFI program that monitors static SQL statements uses the following
steps:
- Acquire and initialize storage areas for common IFI communication
areas.
- Issue an IFI COMMAND call to start performance trace class monitor
class 29 for IFCID 400. This step enables statistics collection for
static SQL statements.
- Put the IFI program into a wait state. During this time, SQL applications
in the subsystem execute static SQL statements.
- Resume the IFI program after enough time has elapsed for a reasonable
amount of static SQL statement activity to occur.
- Set up the qualification area for a READS call for IFCID 401.
- Set up the IFCID area to request data for IFCID 401.
- Examine the contents of the return area.
- For a statement with unexpected statistics values:
-
Obtain the statement ID from the IFCID 401 return area.
-
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.
-
Use the statement text to execute an SQL EXPLAIN statement.
-
Fetch the EXPLAIN results from the PLAN_TABLE.
- Issue an IFI COMMAND call to stop performance trace class 29 for
IFCID 0400.