DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
Starting with Version 9.7 Fix Pack 1, DB2® database manager has the ability to perform an explain directly from the contents of a runtime section. This functionality is known as a section explain. The advantage of a section explain over a traditional explain using an EXPLAIN statement is that the EXPLAIN statement will recompile the statement that is being explained. If the compilation environment or table statistics have changed when the EXPLAIN statement is issued, the compiler may generate a different access plan.
A section explain will always provide the exact access plan that was executed, since the access plan is reconstructed directly from the executable section. A section explain is similar to the functionality provided by the db2expln command, but provides a level of detail approaching that which is provided by the EXPLAIN statement.
The section explain functionality is accessible through a set of stored procedures. The stored procedures take input parameters that are used to locate a section (either in memory, catalogs, captured by an event monitor, or provided directly as input) and perform the explain, populating the explain tables similar to the EXPLAIN statement. The stored procedures output the key fields for the explain instance that was populated in the explain tables. These key fields can be used as input to existing explain formatting tools, for example db2exfmt, which extract the information from the explain tables and present it in a formatted output.