There is currently no way to get the text of a dynamic SQL statement when it leaves the cache. If one has IFCID 316 turned on, one can collect the SQL usage metrics in SMF, but not the text. One can get the statement text if one uses the EXPLAIN statement to periodically take a snapshot of the cache, but this will not pick up those SQL statements that only reside in the cache for a very short period of time.
One should be able to optionally turn on IFICD 317 so that the SQL statement text is sent to SMF, along with the related IFCID 316 data, when the dynamic SQL statement is flushed from the cache. However, in itself, this request is not very useful. Assuming that the data being collected is going to eventually reside in some sort of data warehouse, we also need to have the following:
#1 A dynamic SQL statement identifier that is stable over time. A value that matches the QUERY_HASH column in SYSIBM.SYSQUERY would probably be ideal. The value should be sent to both the IFCID 316 and 317 record types, plus also to IFCID 63.
At present, if a dynamic SQL statement cycles in and out of the cache, or if it resides in the cache of separate DB2 systems, there is no way correlate the various IFCID 316 records. This makes it impossible to do historical analysis.
This is arguably outside the scope of this request, but the QUERY_HASH column should also reside in SYSIBM.SYSPACKSTMT. This would enable us to easily check newly bound static SQL statements to see if they already exist in some other program. If they do, and if we already have performance metrics about their usage, we will avoid having to do the access path analysis that we normally do for “new†SQL statements.
#2 Some way to send the contents of the dynamic SQL statement cache to SMF when a DB2 system is stopped. If we can't do this, then we won't get any performance information about the most frequently invoked dynamic SQL statements, which are arguably the ones that we care about the most.
Even if we have this, we will still lose data if the DB2 system crashes are hard. But a system crash is very infrequent, so we can ignore this issue.
#3 Some way to periodically dump the usage metrics in the cache (i.e. IFICD 316 data only, or IFCID 316 & 317 data) and send them to SMF – similar to how the EXPLAIN statement works today. This will enable us to track changes over time in the activity, or performance, of those dynamic SQL statements that reside in the cache for a very long time. For example, we might dump the IFCID 316 data hourly.
This feature will enable us to get some usage metrics about those dynamic SQL statements that reside in the cache for a long time – even if the DB2 system does subsequently crash.
#4 Some way to know if the access path for a given dynamic SQL statement has changed over time. For example, the cache could also contain a value that was the HASH of the access path. If we saw a SQL statement (identified using the QUERY_HASH value) that had a changed access path, we would investigate closely.
The SYSIBM.SYSPACKSTMT table should also have the access path hash value – to simplify the searching for changed access paths (e.g. after a rebind). It can also be used to efficiently search for syntactically different SQL statements that have the same access path.
#5 Of course, none of the above is useful unless OMEGAMON, or some other product, is updated to enable the enable the data to be extracted from SMF and loaded into a data warehouse.
