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.
Connect With Us: