Enabling DB2 EXPLAIN

Application Performance Analyzer supports both static and dynamic EXPLAIN options. These need to be configured separately, but both options require that the DB2® Intercept be enabled.

Static EXPLAIN

Static EXPLAIN is enabled in Application Performance Analyzer by specifying DB2X=Yes in the CONFIG SAMPLE statement during customization. This makes the DB2X data extractor available for DB2 measurements. The DB2EXPLAIN keyword in the CONFIG SAMPLE statement is required to indicate which DB2 subsystems are to be accessed for explain information.

While sampling a DB2 application when the DB2X data extractor is active for the measurement, Application Performance Analyzer can collect EXPLAIN data created as a result of a DB2 BIND with the EXPLAIN(YES) option. This applies to static SQL statements only. The EXPLAIN data is retrieved from the PLAN_TABLE used by DB2 at the time of the bind.

Dynamic EXPLAIN

Dynamic EXPLAIN is enabled in Application Performance Analyzer by specifying the DB2EXPLAIN keyword in the CONFIG SAMPLE statement during customization.

To use dynamic EXPLAIN with the Application Performance Analyzer DB2 component, Application Performance Analyzer started tasks must have access to a PLAN_TABLE. Bypass this section if you do not use the dynamic EXPLAIN feature.

For DB2 Version 9, edit and submit job CAZPLANT in hlq.SCAZSAMP to create a PLAN_TABLE. For DB2 Version 10, edit and submit job CAZPLANX in hlq.SCAZSAMP to create a PLAN_TABLE.
For DB2 Version 11 or higher, edit and submit job CAZPLANY in hlq.SCAZSAMP to create a PLAN_TABLE.
The qualifier for this PLAN_TABLE defaults to the authorization ID of the started task. You can override this default by changing the DB2Qualifier setting in the configuration file. See DB2Qualifier under CONFIG SAMPLE statement.

The CREATE statement in job CAZPLANT is in the 49-column format compatible with DB2 Version 6. The 51-column format (for DB2 Version 7) and the 58-column format (for DB2 Version 8) can be used instead. For DB2 Version 10, the PLAN_TABLE must be created using job CAZPLANX. Whichever PLAN_TABLE is used, the dynamic EXPLAIN feature will only make use of the first 49 columns when reporting the results of an EXPLAIN.

You can specify the database and tablespace name in which the PLAN_TABLE will be created by adding the appropriate lines to the CREATE statement.

The CREATE statement assumes that the escape character for the DB2 subsystem is the quote character ("). If the DB2 subsystem has been installed with the quote character as the SQL string delimiter, then the escape character is the apostrophe ('). In this case, the quotes in the CREATE statement must be changed to apostrophes.

Considerations in using dynamic DB2 EXPLAIN

The DB2+ data extractor is required for Application Performance Analyzer to capture the required qualification information. If DB2+ is not selected, then dynamic EXPLAIN will not be available.

There is a limit of 15000 bytes on the target SQL statement size. The sample file LRECL dictates this limit.

A DB2 restriction prevents some SQL statements from being the subject of dynamic EXPLAIN. UPDATE or DELETE statements with a WHERE CURRENT OF clause are among the prevented SQL statements.

Application Performance Analyzer needs to access catalog information for the target SQL statement. If any of the information changed since the sample file was created, then the resulting dynamic EXPLAIN data might not be correct. In the case of a static SQL statement of 4000 bytes or greater, the statement itself might have changed and might no longer match the statement in the sample file.