Capturing Dynamic SQL with EXPLAIN

I’ve already written extensively about EXPLAIN, but since IBM continues to develop the feature and enhance the process, there's plenty more to cover.

For instance, I recently started experimenting with the SET CURRENT EXPLAIN MODE statement, which is used to capture EXPLAIN data for dynamically executed statements. The challenge with dynamic SQL is that -- because it's built dynamically based on input during execution -- you may not know how the statement will appear until it's executed. This makes it challenging to prebuild and add an EXPLAIN command to determine the access path DB2 will use. However, starting with DB2 10, applications can be modified to take advantage of the SET CURRENT EXPLAIN MODE statement. The options are:

• NO specifies that no EXPLAIN information is captured. NO is the initial value of the EXPLAIN MODE special register.

• YES enables the EXPLAIN facility and causes EXPLAIN information to be inserted into the EXPLAIN tables for eligible dynamic SQL statements after the statement is prepared and executed. All dynamic SQL statements are compiled and executed normally.

• EXPLAIN enables the EXPLAIN facility and causes EXPLAIN information to be captured for any eligible dynamic SQL statement after the statement is prepared. This setting behaves similarly to YES; however, dynamic statements (except for SET statements) aren't executed.

To enable a program to execute a hard-coded statement, pass in a parameter to drive the program logic or simply pass in a value to be used in a host variable on the SET statement.

This is hard-coded logic:

    IF pass-value = ‘NO’ then

       SET CURRENT EXPLAIN MODE = NO

    ELSE IF pass-value = ‘YES’ then

       SET CURRENT EXPLAIN MODE = YES

    ELSE IF pass-value = ‘EXPLAIN’ then

       SET CURENT EXPLAIN MODE = EXPLAIN

               END-IF

           END-IF

    END-IF.

 

Here's how to pass in a value for a host variable:

   SET CURRENT EXPLAIN MODE = :pass-value

 

The host variable must be defined as CHAR or VARCHAR, and it must be set to a valid option (NO, YES or EXPLAIN). Leading blanks aren't allowed. All input values must be uppercase. In addition, they must be left-justified within the host variable and padded on the right with blanks if the length of the value is less than the length of the host variable.

The host variable is a simple option for turning on/off EXPLAIN processing during program execution. I used this script in SPUFI to test it out:

 

1 - DELETE PLAN_TABLE;
2 - COMMIT;
3 - SET CURRENT EXPLAIN MODE = YES;
4 - SELECT * FROM SYSIBM.SYSTABLES WHERE NAME = 'SYSTABLES';
5 - SET CURRENT EXPLAIN = NO;
6 - SELECT * FROM PLAN_TABLE;

 

Notice I turned on the auto EXPLAIN feature with statement 3, and then shut it off with statement 5. I didn't want statement 6 to appear in the EXPLAIN tables. I did find an interesting contradiction to the documentation during my testing in SPUFI. When I set mode to EXPLAIN at statement 3, I received the message below warning me that the statement wasn't executed. I also found that nothing after statement 4 was executed.  So obviously, statement 6 wasn't executed, either.

Note: When you use mode EXPLAIN you will receive warning SQLCODE +217:

DSNT404I SQLCODE = 217, WARNING:  THE STATEMENT WAS NOT EXECUTED AS ONLY EXPLAIN INFORMATION REQUESTS ARE BEING PROCESSED

Have you had an opportunity to use this feature to help you EXPLAIN dynamic SQL statements? Please share your experiences in comments.

Using EXPLAININPUTSCHEMA

At the beginning of this year, IBM introduced a new bind query parameter, EXPLAININPUTSCHEMA.

Available for DB2 for z/OS 10.0, this parameter is designed to simplify the task of populating EXPLAIN tables that are required for command input. The parameter allows DBAs to create these tables under a separate schema.

Prior to this enhancement, customers had to delete most existing rows in the plan_table while maintaining only those rows needed to be used as input into the BIND QUERY command. Plan_tables normally contain the access path for many different programs, so having to delete this history was a real problem for many who rely on this information.

To take advantage of this enhancement, you need to create a new plan_table using a new schema name. For example, to create a plan_table under schema BINDQ and then copy rows from your existing plan_table to be used as input into BIND QUERY, you'd issue this statement:

BIND QUERY EXPLAININPUTSCHEMA(’BINDQ’)

With this change, the BIND QUERY LOOKUP(YES) must change to recognize the new table. The format to use BINDQ schema is:

BIND QUERY LOOKUP(YES) EXPLAININPUTSCHEMA(‘BINDQ’)

Read more about this parameter in the DB2 10 for z/OS Command Reference.

EXPLAIN Tables Need to be Converted Prior to DB2 10 Migration

Traditionally with DB2 upgrades, DBAs haven't had to worry about converting the EXPLAIN tables (including, for example, the user PLAN_TABLE). Of course new columns are added to these tables with each new DB2 release, and DBAs could choose to modify their current tables to accommodate these new columns, but it hasn't been necessary. The EXPLAIN tables could function through the upgrade without any changes. And even without the columns and other enhancements delivered with the latest DB2 release, the tables could still be populated with valid data post-upgrade.

Convenient as this has been, it's changed with DB2 10. It's now necessary to convert EXPLAIN tables prior to migration. While this doesn't require a significant amount of extra work, customers do need to plan for it.

The DB2 install provides instructions on converting existing EXPLAIN tables to the current release format.  If you're migrating to DB2 10 from DB2 version 8, you should convert all the existing EXPLAIN tables to version 8.0 format before beginning the migration. Likewise, if you're migrating from DB2 9, convert the EXPLAIN tables to DB2 9 format before starting the migration. If the EXPLAIN tables aren't converted before migrating to DB2 10, issuing an EXPLAIN will result in an error. For instance, DB2 will return SQLCODE -20008 reason code 2 when performing an EXPLAIN and the EXPLAIN tables are in a pre-Version 8 format.

Once you migrate to DB2 10, you must also ensure the tables are in DB2 10 format and encoded in UNICODE. If your tables are still encoded in EBCDIC, an EXPLAIN will generate an SQLCODE -878 error.

This process is covered in the “Converting EXPLAIN Tables (before migration)" section of the DB2 for z/OS Installation and Migration Guide. Note that there are different sets of instructions when migrating from V8 versus V9.1

The install guide lists some jobs that you can run to help with the conversion. One thing I noticed when reviewing this section: Check any applications you have that may join with the EXPLAIN tables. You may receive different results because the EXPLAIN tables being unicode and your user tables are in EBCDIC.

Have you converted EXPLAIN tables as part of a migration to DB2 10? If so, please share your words of wisdom about this process.