Collecting statement-level statistics for SQL statements
You can enable the collection of statement-level statistics for both static and dynamic SQL statements at the Db2 subsystem level.
About this task
Monitor trace class 29 enables you to create READS programs to monitor SQL statements at the Db2 subsystem level through the instrumentation facility interface.
Procedure
To enable the collection of statement level statistics:
Create a program that issues IFI command to enable monitor
trace class 29.
Your program can monitor static SQL statements,
dynamic SQL statements or both:
- For static SQL statements, set up READS calls to monitor IFCID 401. IFCID 400 controls the collection of statistics for static SQL statements.
- For dynamic SQL statements, set up READS calls to monitor IFCIDs 316 and 317. IFCID 318 controls the collection of statistics for dynamic SQL statements.
Your program can examine the return areas for statement-level
statistics, and can gather EXPLAIN information for statements that
have unexpected statistics values.