Specifying Explain options

Use the following procedure to specify processing options for your Explain.

1. Access the Explain Options panel.
   BMC.DB2.SPE2507

   PSSPA020 ----------------------- Explain Options ------------------------------
   Command ===>                                                                  
   
   Specify options and press ENTER to continue.                                   
   Press F8 to scroll down and F7 to scroll up.                                   
                                                                       More:     +
   Rule Set . . . . . . . . . DEFAULT  (DEFAULT, APPLDEV, other)                  
   Dynamic Explain:                                                              
     Plan Table Owner . . . . USERID   (USERID, OWNER, QUAL, authid)                           
     Qualifier Name . . . . . QUAL     (QUAL, authid)  For unqualified SQL        
     Degree . . . . . . . . .          (blank/1/Any)                              
     Query Acceleration . . .          (blank/NONE/ENABLE)
     Include GLOB DDL   . . . Y (Y/N)
     Declare Global Temp DDL . . .(PS name or PDS name with member name.)
     DSN . .                 
                                                             
   Static Explain or Explain Package:                                             
     Plan Table Rows  . . . . L        (L=Last bind time, A=ALL rows)             
   
   Read-Only Application  . . N        (Y/N)                                      
   Online Application . . . . Y        (Y/N)                                      
   
   Acceleration Detail    . . Y        (Y/N)                                      
   Currency Code . . . . . . USD       (USD, EUR, etc.)                           
   Cost Translation Rate . . 1.0        Cost per timeron                           
                             1.0        Cost per service unit                      
                                                                  
   Rule Display Option                                                            
    1 1 BMC message ID, rule severity, and the message text                      
      2 Message text only                                                       
      3 No rule messages                                                         
   
   Column Break . . . . . . . N   (Y/N)  Format 1 column per line in SQL text    
   Recall DBRMLIB Data Set    Y   (Y/N)  Restore PDS for DBRMLIB
                   
   Debug Parameter  . . . . .            BMC Support Supplied Value
                   
   Include Stats Feedback   . Y   (Y/N)
2. In the Rule Set field, type the name of the rule set to use:
   • DEFAULT contains rules concerning performance issues, primarily geared for DBAs.
   • APPLDEV contains rules designed especially for application developers.

   • other is the name of a customized rule set. This value corresponds to the member name of the rule set that you created and should match the RULESET declaration.

     You might have several modified versions of the rule set for your installation.

     Example

     You might have a rule set for the accounting application and another for human resources, or you might have one rule set for the test environment and another for the production environment.

3. Select options for a Dynamic Explain, a Static Explain, or an Explain Package. For a Dynamic Explain, specify the following options:

   Field	Values
   Plan Table Owner	Specify one of the following qualifiers for the PLAN_TABLE Explain operation uses: USERID uses the job submitter's user ID for the plan table. Explain reverts to this value if you don't have the authority to run the SET CURRENT SQLID command. OWNER uses the owner name of the object provided at bind time for the PLAN_TABLE. If the object is not bound to Db2 (such as DBRMLIB or ad hoc SQL text), Explain uses the exact value that you specify. QUAL uses the qualifier name of the object provided at bind time for the PLAN_TABLE. If the object is not bound to DB2 (such as DBRMLIB or ad hoc SQL text), Explain uses the exact value that you specify. authID uses authID.Plan_Table, where authID is any valid Db2 qualifier. Tip We recommend that you point to BMC or empty plan tables. If your PLAN_TABLE has many rows, you can avoid performance problems by adding the recommended indexes in the DAADB2IX member in the SAMP library.
   Qualifier Name	Specify a qualifier for objects and tables in the Explain operation, as follows: QUAL uses the Qualifier Name provided at bind time. This value is not valid for DBRMLIB or ad hoc SQL text explanation. authID is any valid Db2 qualifier used to qualify any unqualified objects. Important For online Explains, the product updates this field with the qualifier of a bound object. For batch Explains, the product uses the value you specify here.
   Degree	Specify whether the Explain operation should run using parallel processing to maximize performance, as follows: blank issues a SET CURRENT DEGREE command to the value that the object was bound to before running the dynamic Explain. This value is not valid for DBRMLIB or ad hoc SQL text explanation (default). 1 issues a SET CURRENT DEGREE = 1 command before running the dynamic Explain. Any issues a SET CURRENT DEGREE = ANY command before running the dynamic Explain. This option enables parallel processing.

4. For a Static Explain or Explain Package, specify which rows from the  owner.Plan_Table to use in the Explain. This option is valid only for static Explains (XS) and Explain Package (XP). Specify one of the following values:

   Value	Description
   L—Last bind time	XS uses the most recent rows in the owner.PLAN_TABLE as follows: From the last time that you ran a bind with EXPLAIN(YES) For the object that you want to Explain
   	XP displays the current copy of the package that you want to Explain.
   A—All rows	XS uses all of the rows in the  owner.PLAN_TABLE for the object that you want to Explain.
   	XP displays the following copies of the package that you want to explain: Current Previous Original Important If the last bind on the object did not specify EXPLAIN(YES), no static Explain information is retrieved from the PLAN_TABLE.

5. In the Read-Only Application field, specify whether the access intent is read only and no data manipulation is involved:
   • Type Y to trigger a set of specific Db2 consideration rules.
   • Type N if your application inserts, updates, or deletes data.
6. In the Online Application field, specify whether to trigger specific rules for table space scan, list and sequential prefetch, and multiple index access paths (MIAP) considerations:
   • Type Y if the application usage is online.
   • Type N if the application usage is batch.

7. In the Acceleration Detail field, specify whether to report the SQL's eligibility to run on an IBM Db2 Analytics Accelerator (IDAA):

   • Type Y (the default) to show the SQL's eligibility to run on an IDAA.
   • Type N to omit the SQL's eligibility to run on an IDAA.

   A BMC message rule indicates the name of the accelerator, if applicable.

   Important

   If you have enabled Accelerator Modeling (via the ACCELMODEL system parameter), this option supports Accelerator Modeling and actual accelerators.

   Depending on the parameters that you set, the product displays the applicable BMC message rules that are listed here:

   • If you enabled the IDAA, SQL Explorer displays this message rule:

     BMC184401I-The query will be sent to the accelerator.

   • If, in addition, you set the Acceleration Detail parameter to Y, SQL Explorer displays one of the following message rules:

     BMC184397I-The query will be sent to Accelerator <acceleratorServerName acceleratorServerLocation>.

     BMC184398I-This query is not eligible for Acceleration. Reason:<reasonCode-queryText>

     Values for the variables correspond to the REASON_CODE and QI_DATA columns of the DSN_QUERYINFO_TABLE.

   • If Accelerator Modeling is turned on, SQL Explorer displays one of the following message rules:

     BMC184399I-ACCELMODEL {ELIGIBLE | INELIGIBLE}

     BMC184398I-This query is not eligible for Acceleration. Reason: <reasonCode-queryText>

   The product's BMC Explain component retrieves this information from the following tables:

   • DSN_STATEMNT_TABLE
   • DSN_QUERYINFO_TABLE
   • PLAN_TABLE

   If these tables do not exist at the time of the Explain, BMC Explain creates them.

8. Specify a three-digit Currency Code for the monetary unit used to calculate the cost translation rate.

   Best Practice

   You can use any characters except spaces in this field, though we recommend that you use ISO 4217 standard codes for the representation of currencies. The default is USD.

9. Specify the Cost Translation Rate used to translate the estimated processor cost of executing an SQL statement into monetary units, as follows:
   • Specify the rate used to translate the timeron cost into a monetary unit, in the form of currency/timerons, where currency is the value you specified for Currency Code.
   • Specify the rate used to translate the service unit cost into a monetary unit, in the form of currency/service units, where currency is the value you specified for Currency Code.
10. Specify the Rule Display Option, as follows:

    • BMC message ID, rule severity, and the message text causes the Explain output to contain only the BMC message ID, the message severity level, and the descriptive text of the message.

      Example

      BMC184028I-The tables referenced in the FROM clause are fully qualified. This will limit the flexibility for the qualifier to be resolved at bind time.

    • Message text only causes the Explain output to contain only the descriptive text of the message.

      Example

      The tables referenced in the FROM clause are fully qualified. This will limit the flexibility for the qualifier to be resolved at bind time.

    • No rule messages generally causes a batch or online Explain to bypass rule message processing. Because this option bypasses the rule message processing, no rules are listed in the batch or online output.

      However, if you specify this option for an online Explain in conjunction with one of the rule-related action codes shown in Actions, the product overrides this option and displays the rules associated with the specified action.

11. Specify whether to insert a Column Break between columns listed as part of the SELECT, INSERT, DELETE, or UPDATE clause in the SQL text:
    • Y formats the SQL text with a single column per line.
    • N formats the SQL text with multiple columns per line.
12. In the Recall DBRMLIB Data Set field, specify whether to restore the partitioned data set (PDS) when performing an Explain on a DBRMLIB.

13. Depending on whether you want to select objects to Explain or use the specified object, take one of the following actions:

    Explain type	Action
    Online	Press Enter. The Explain Object Selection List is displayed. You can now Explain a package online.
    Batch	Press F8 to scroll down to the batch Explain options. Specify options for a batch Explain.

14. BMC.DB2.SPE2507 In the Include Stats Feedback field, select one of the following options:
    • Y Includes the DSN_STAT_FEEDBACK table for Explain. (default)
    • N Skips the DSN_STAT_FEEDBACK table for Explain.