EXPLAIN tables

You can create and maintain a set of EXPLAIN tables to capture and analyze information about the performance of SQL statements and functions that run on DB2 for z/OS. Each row in an EXPLAIN table describes some aspect of a step in the execution of a query or subquery in an explainable statement. The column values for the row identify, among other things, the query or subquery, the tables and other objects involved, the methods used to carry out each step, and cost information about those methods. DB2 creates EXPLAIN output and populates EXPLAIN tables in the following situations:

• When an EXPLAIN statement is executed.
• At BIND or REBIND with the EXPLAIN(YES) or (ONLY) bind options. Rows are added for every explainable statement in the plan or package being bound. For a plan, these do not include statements in the packages that can be used with the plan. For either a package or plan, they do not include explainable statements within EXPLAIN statements nor do they include explainable statements that refer to declared temporary tables, which are incrementally bound at run time.
• When an explainable dynamic statement is executed and the value of the CURRENT EXPLAIN MODE special register is set to YES or EXPLAIN.
• When the DSNAEXP stored procedure executes successfully. The DSNAEXP stored procedure is deprecated.

Important: It is best to convert EXPLAIN tables to DB2 10 format during migration, or soon after migration. In DB2 10, the EXPLAIN function supports tables that have only the DB2 10, DB2 9, or Version 8 formats. However, DB2 9 format and Version 8 format EXPLAIN tables are deprecated. If you invoke EXPLAIN and DB2 9 or Version 8 tables are used, DB2 issues SQL code +20520. If tables of an unsupported format are found, DB2 issues SQL code -20008 and the EXPLAIN operation fails.

Important: If the EXPLAIN tables have any format older than the DB2 Version 8 format, or are encoded in EBCDIC, DB2 returns an error for any operation that tries inserts rows in the EXPLAIN tables.