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 0401. IFCID 0400 controls the collection of statistics for static SQL statements.
For dynamic SQL statements, set up READS calls to monitor IFCIDs 0316 and 0317. IFCID 0318 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.