The collection of statement-level statistics for SQL statements
might increase the processing costs for those statements.
Before you begin
Monitor trace class 29 must be active to enable the collection
of statement-level statistics.
Procedure
To minimize the costs of collection statistics for SQL
statements:
- Use IFCID 318 to enable and disable the collection of
dynamic statement cache statistics in IFCID 316.
When
IFCID 318 is inactive, Db2 does
not collect those statistics. Db2 tracks
the statements in the dynamic statement cache, but does not accumulate
the statistics as those statements are used. When you stop or start
the trace for IFCID 318, Db2 resets
the IFCID 316 statistics counters for all statements in the cache
to 0.
If you issue a READS call for IFCID 316 while IFCID 318
is inactive, Db2 returns
identifying information for all statements in the cache, but returns
0 in all the IFCID 316 statistics counters. When you are not actively
monitoring the cache, you should turn off the trace for IFCID 318.
- Use IFCID 400 to enable
and disable the collection of statistics for static SQL statements
through IFCID 401.
When IFCID 400 is inactive, Db2 does
not collect those statistics. Db2 tracks
the statements in the EDM pool, but does not accumulate the statistics
as those statements are used. When you stop or start the trace for
IFCID 400, Db2 resets the IFCID
0401 statistics counters for all static SQL statements to 0.
If
you issue a READS call for IFCID 401 while IFCID 400 is inactive, Db2 returns identifying information
for all statements in the EDM pool, but returns 0 in all the IFCID 316 statistics counters. When you are not actively monitoring statistics
SQL statements, you should turn off the trace for IFCID 400.