Application Performance Analyzer for z/OS, Version 11.1, Customization Guide

Considerations in using DB2 EXPLAIN

  1. You must select DB2+ in order for Application Performance Analyzer to capture the required qualification information. It must be done after the SQL call has completed, when the DB2® control blocks are stable. If DB2+ is not selected, then EXPLAIN will not be available.
  2. There is a limit of 15000 bytes on the target SQL statement size. The sample file LRECL dictates this limit.
  3. 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 some of the prevented SQL statements.
  4. 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 EXPLAIN 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.
  5. When you enable the automatic PLAN_TABLE creation option, the started task will automatically create a PLAN_TABLE if none exists for the qualifier of a particular target SQL statement. Depending on the usage of the EXPLAIN feature, the result could be that many tables are created.
    Note:
    This option only applies to DB2 versions 6 and 7.

    To avoid creating a large number of tables, you can explicitly create the plan tables for those qualifiers for which you want EXPLAIN support enabled, and specify NO, in the automatic PLAN_TABLE creation option of the DB2EXPLAIN entry for the DB2 subsystem.




Rate this page

[ Top of Page | Previous Page | Next Page | Contents | Index ]