Explaining_an_ad_hoc_SQL_statement (generic)

1. Access the Explain Object Specification panel. The method varies depending on the product.

   • On the SQL Explorer main menu, select Explain. Review the SSID and DDF Location, revise if necessary, and press Enter.
   • On the APPTUNE Main Menu, select Explain Interface and press Enter.
   • On the MainView for DB2 - DC Main Menu, select Explain Interface and press Enter.

   The Explain Object Specification panel is displayed.

   Note

   Explain recognizes only the information that applies to the object Type that you specify. For example, if you specify to Explain a package, any information that exists in the plan, DBRM, or DBRMLIB fields is ignored. You do not need to remove any of the unrelated values before performing the Explain.

   PSSPA110 ---------------- Explain Object Specification ------------------------
   Command ===>                                                                   
                                                                                 
   Type  . . 5  (1=Plan, 2=Package, 3=DBRM, 4=DBRMLIB, 5=Ad Hoc SQL)              
                                                                                 
   Plan:                                                                          
     Name                                                                         
                                                                                 
   Package:                                                                       
     COLLID  DEMO%                Name . . . %                                    
     Version %                                                                    
                                                                                 
   DBRM:                                                                          
     Plan                         Name . . .                                      
                                                                                 
   DBRMLIB:   (Specify PDS with member name or wildcard member.)                  
     DSN . .                                                                      
                                                                                 
   Processing Mode:  L         (L=List object(s),                                 
                                B=Batch Explain with specified objects)           
   _ Explain Options

2. In the Type field, select Ad Hoc SQL and then press Enter.An ad hoc SQL Explain edit session is displayed.

    

   ISREDDE2   EXPLAIN                                         Columns 00001 00072
   Command ===>                                                  Scroll ===> PAGE
   ****** ***************************** Top of Data ******************************
   ==MSG> -Warning- The UNDO command is not available until you change            
   ==MSG>           your edit profile using the command RECOVERY ON.              
   000001 SELECT * FROM SYSTABLES                                                 
   ****** **************************** Bottom of Data ****************************

3. In the edit window, type an SQL statement and then press F3.The Explain or Execute Parameters panel is displayed.

    

   PSSPA117 ----------------- Explain or Execute Parameters ----------------------
   Command ===>                                                                   
                                                                                 
   Specify the options below and press ENTER to continue.                         
                                                                                 
   Option . . . . . . . . . 1    1. Explain                                       
                                 2. Execute                                       
                                 3. Edit                                          
                                                                                 
   Qualifier Name . . . . . SYSIBM
4. In the Option field, select Explain.

5. Specify a Qualifier Name, and press Enter.The Explain Results panel is displayed.

     FILE   COMMANDS   OPTIONS   HELP
   -------------------------------------------------------------------------------
   PSSPE200             Explain Results for SQLTEXT                               
   Command ===>                                                  Scroll ===> CSR
                                                                    More:       >
      Actions: S H R RS RW RI XD XS W P T
      LBL   STMTNO     COST*RATE SQL-STATEMENT                                    
      XD01          13489.976562 SELECT * FROM SYSTABLES ;                        
            COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TCREATOR TNAME            
      XD01  13489.976  1  1   0 SELECT    0 I      0 N  SYSIBM   SYSTABLES        
   ******************************** Bottom of Data *******************************

6. Specify options on the Explain Results panel.

   Note

   For a SQL Performance installation, the identifier of this panel is PSSPW200. In this case, additional action codes are available only as part of the SQL Performance for DB2 solution.

7. Review the results of your Explain.

Explain_types (generic)

 An Explain takes one of the following forms:

• A dynamic Explain—asks the DB2 optimizer for an explanation of the access path. This process evaluates access paths, SQL text, and key catalog statistics in real time.

  Note

   If you upgrade to DB2 Version 10 or later, you must ensure that your existing plan tables are in Unicode format before running the dynamic Explain process. See your IBM product documentation for information about migrating your plan tables.The dynamic Explain process in DB2 Version 10 or later alters the Unicode back-leveled PLAN_TABLE that you specify to include all of the columns that are required by your version of DB2. This alteration ensures that your PLAN_TABLE remains compatible with the DB2 version that you are using. If the PLAN_TABLE has a padded index, automatic ALTERs for the back-level PLAN_TABLE are disabled.

• A static Explain from a bind with EXPLAIN(YES)—reports the access path information that was derived when you performed a bind with EXPLAIN(YES).
• An Explain Compare on a static SQL statement from the SQL workload—performs a dynamic Explain and a static Explain, matching the STMTNO from the catalog to QUERYNO in the PLAN_TABLE.
• A What-If Edit SQL Explain—in which the product edits the SQL and performs a dynamic Explain. You can edit and Explain the SQL as many times as needed. You can also compare the resulting access paths to determine the performance impact of an SQL change.
• An Explain Statement Cache on a dynamic SQL statement—performs an EXPLAIN STMTCACHE STMTID on a dynamic SQL statement that was executed and is still in the statement cache. The access path in use when the statement was executed is retrieved from the statement cache and reported.
• An Explain Package—in which the current access path for a static SQL statement is retrieved and reported even if a BIND with EXPLAIN(YES) was not previously performed.

When you perform multiple Explains on a statement or object, the differences are shown in white. An asterisk (*) next to the Explain label indicates that there was a change on that line.

The Explains are listed in the order in which they were performed, with the most recent Explain at the top of the list. The Explains are compared from the bottom up, such that a difference between Timestamp 1 and Timestamp 2 is indicated by an asterisk on the label for Timestamp 2. The columns where the differences occurred are shown in white

Explain_entry_points (generic)

 Several BMC Software products and solutions share the Explain function. The following table describes how you can access the Explain function from these products.

Main_panels (generic)

 The main panels for the Explain process are as follows:

• Explain Object Specification allows you to specify criteria to retrieve an object or statement to Explain. Use this panel to:
  • Specify whether to list objects that you can select to Explain
  • Specify to Explain the specified objects in batch

  • Specify whether to provide additional Explain options

    Note

     The appearance of this panel varies, depending on the route by which you enter the Explain function. For example, you might be asked to specify a subsystem ID for your Explain.

• 
  
  • Explain Options is displayed when you select Explain Options on the Explain Object Specification panel or from the OPTIONS task bar. Use the Explain Options panel to specify or change Explain processing options.

    • Explain Object Selection List is displayed when you specify Processing Mode L, List object(s), on the Explain Object Specification panel, or when you execute BMCEX on objects from CATALOG MANAGER. Use the Explain Object Selection List to specify objects for an Explain operation. A task bar and action codes provide more room on this panel for displaying Explain information. You can also use the FIND, RFIND, and ZOOM commands on this panel.

      Because a plan can consist of DBRMs, a package list of collection IDs, or a mixture of both DBRMs and packages, the Object Selection List for plans shows both the DBRMs and package list.

      The appearance of the Explain Object Selection List is controlled by a member in a layout data set.

  • Explain Results displays the results of online Explain operations, including the following information:

    • The estimated cost (in timerons) of executing the SQL statement
    • The predicates used in the SQL statement
    • Whether a mismatch in data type or length might be causing the DB2 optimizer to choose a bad access path
    • Access path information
    • Expert rules regarding the syntax of the statement, the access path, and current statistics on the objects
    • Statistics from the DB2 catalog for tables referenced in the access path steps

    In addition, indexes referenced in the access path steps are displayed and highlighted with the table statistics, followed by indexes that are available but not referenced in the step. You can drill down further to display table and index columns, and their related statistics.

    The information on the Explain Results panel is divided into the following sections:

    • The Statement area is located at the top of the panel. This section shows information such as the type of Explain that was performed, the statement number, the cost in timerons, the statement text, and the cost in service units.

    • The Access path area is located at the bottom of the panel. This section displays information from the DB2 PLAN_TABLE. You can drill down to table and index columns and statistics for each query block.

      A task bar and action codes provide more room on this panel for displaying Explain information. From this panel, you can drill down to get more information, display reports on demand, edit SQL, and perform additional Explains. You can also use the FIND, RFIND, and ZOOM commands on this panel.

      The appearance of the Explain Results panel is controlled by the same layout data set that controls the display of the Explain Object Selection List.

Task_bar (generic)

 The Explain Object Selection List and Explain Results panels use a task bar containing the following options.To display these options, put your cursor on a task bar item, and press Enter.

• FILE task bar enables you to manage the data sets used by the Explain operation. Specify one of the following options, and press Enter:
  • OPEN LAYOUT allows you to browse, edit, or change the layout data set, which controls the display of this panel.
  • PRINT writes the Explain results to the ISPF data set. This option is available only on the Explain Results panel.
  • EXIT returns you to the previous panel.
• COMMANDS task bar enables you to specify processing options for all objects on the panel.
• OPTIONS task bar enables you to specify processing options for the Explain operation or for the JCL used in a batch Explain. Specify one of the following options, and press Enter:
  • EXPLAIN displays the Explain Options panel.
  • JCL displays the JCL Options panel.
• HELP task bar enables you to get more information about the Explain function. Specify one of the following options, and press Enter:
  • ACTIONS displays information about the Explain action codes.
  • COMMANDS displays information about the Explain commands.

Actions_ (generic)

 You can use the following action codes in your Explain in one of the following ways:

• Type the action code in the space beside an object, and press Enter.
• Put the cursor in the space beside an object, and press Enter. A list of action codes is displayed. Specify an action code, and press Enter.

The action codes appear on the Explain Object Selection List and the Explain Results panels. To specify an action code for multiple objects, type the action code in the space beside an object, type an equal sign (=) in the space beside any subsequent objects on which you want to perform the same action, and press Enter.

The following table lists the action codes.

Commands_ (generic)

 The following table lists commands that apply to all objects in the Explain Object Selection List or the Explain Results panel.You can specify a command in one of the following ways:

• Type a command on the COMMAND line and press Enter.
• Put your cursor on the COMMANDS task bar and press Enter. Specify a command, and press Enter.

Layout_data_set (generic)

A layout data set member controls the appearance of the Explain Object Selection List and the Explain Results panel.
The Explain function is shipped with a default layout member (PSSREPA) in the hlq.SAMP data set. The DOMCLIST allocates the product hlq.llqSAMP data set to the PSSCNTL DD. The product does not allow customization of PSSREPA, but you can copy the default layout to a new data set name and edit that member. You can save one or more layout members.

To customize a default layout member:

1. Copy the PSSREPA member to one of the following locations:
   • The userlib data set (hlq.UBMCSAMP)

   • A different data set

     If you copy the member to a different data set, first allocate the target data set (for example, new.dsn) in the DOMCLIST on the PSSCNTL DD:

     Example

     ALLOC F(PSSCNTL) SHR REU DA('<new.dsn>' '<hlq>.UBMCSAMP' '<hlq>.BMCSAMP')
2. Edit the new data set.
3. Change the name of the member and then reference the new name as the layout data set.

The Layout Data Set panel contains the following options:

• Edit layout data set opens the layout data set for editing.
• Browse layout data set opens the layout data set for viewing. In this mode, you cannot edit the layout data set.
• Use default data set instead of layout data set reverts to using the default data set to display the Explain Object Specification List and the Explain Results panel online.
• Overwrite layout data set with default data set and edit replaces the contents of the layout data set with the contents of the default data set and opens the revised layout data set for editing.

The default layout data set contains comments to help you edit your layout data set.

Explain_parameters (generic)

 You can use the parameters described in the following table in your Explain job.The equal sign (=) is required for these parameters where shown. You can include more than one object in a batch job by using a wildcard character or by repeating the --NEWOBJ/--ENDOBJ set of parameters in the SYSIN.

Explain_return_codes (generic)

 The following table lists the return codes for the Explain function and gives information about the events that cause Explain to issue a particular return code.

Specifying_JCL_options_in_batch_mode (generic)

 When you run a job in batch mode, the Batch Job panel is displayed. Use the following panel to specify JCL options before executing a job in batch mode.

Setting_data_set_options

You can enclose the data set and member name with single quotation marks. If you use quotation marks, the product uses the exact data set and name that you specify. Otherwise, the product attempts to append the data set and member name with the TSO prefix (if you have a prefix set) as the first node.

To review or change data set options

1. Access the JCL Options panel using the procedure described in Specifying-JCL-options-in-batch-mode.

2. In the Options field, select 2 (for Data Sets) and press Enter.The Data Set Options panel is displayed.

   PSSPODS2 ----------------------- Data Set Options -----------------------------
   Command ===>                                                                   
                                                                                  
    Specify options and press ENTER to continue.                                   
                                                                                  
    Use #### in member name to have counter variable generated into member name.   
    Counter . . . . . . . . 1    (Replaces #### in member name, then incremented)  
                                                                                  
      Note: JCL, SYSPRINT, Summary, and SYSIN data sets can be partitioned or      
            sequential.                                                            
                                                                                  
    JCL Data Set  . . . . . 'RDAJLG4.SQLXPLR.CNTL(SQLX####)'                       
                                                                                  
    SYSPRINT Data Set . . . 'RDAJLG4.SQLXPLR.SYSPRINT(SQLX####)'                   
    SYSPRINT SYSOUT . . . . Y  (Y/N)      SYSOUT Class . . . *                     
                                                                                  
    Summary Data Set  . . . 'RDAJLG4.SQLXPLR.SUMMARY(SQLX####)'                    
    Summary SYSOUT  . . . . Y  (Y/N)      SYSOUT Class . . . *                     
                                                                                  
    SYSIN Data Set  . . . . 'RDAJLG4.SQLXPLR.SYSIN'                                
                                                                                  
      Additional Data Set (Update)
3. In the Counter field, type any value from 1 through 9999 to replace the '####' suffix in the PDS member name.If needed, the product adds leading zeros to fill the four-digit suffix.
4. In the JCL Data Set field, type the name of the partitioned or sequential data set to which the product writes the JCL and selected options for the batch job.The product creates partitioned data sets if you specify a member name or sequential data sets if you omit the member name. You can enclose the data set and member name with single quotation marks. If you use quotation marks, the product uses the exact data set and name that you specify. Otherwise, the product attempts to append the data set and member name with the TSO prefix (if you have a prefix set) as the first node.
5. Specify options for SYSPRINT data, as follows:
   1. In the SYSPRINT Data Set field, type the name of the partitioned or sequential data set to which the product writes SYSPRINT data for the job.
   2. In the SYSPRINT SYSOUT field, type Y to use a SYSOUT class or N to use a SYSPRINT data set.
   3. In the SYSOUT Class field, specify the JES message class for the SYSPRINT data set.You can use a wildcard in this field.
6. Specify options for summary data:
   1. In the Summary Data Set field, specify the name of the partitioned or sequential data set to which the product writes summary data for the job.
   2. In the Summary SYSOUT field, type Y to use a SYSOUT class or N to use a summary data set.
   3. In the SYSOUT Class field, specify the JES message class for the summary data set.You can use a wildcard in this field.
   4. In the SYSIN Data Set field, type the name of the partitioned or sequential data set to which the product writes SYSIN data for an online job.
7. Specify options for additional data sets:

   1. Type any character in the space beside Additional Data Sets, and press Enter.The Additional Data Set Options panel is displayed.

       

      PSSPODS3 ----------------- Additional Data Set Options ------------------------
      Command ===>                                                                   
                                                                                     
       Specify options and press ENTER to continue.                                   
                                                                                     
         NOTE: The Update data set can only be sequential.                            
               The Update data set is needed only for Migrate Access Path Statistics.
                                                                                     
        Update Data Set . . . . 'RDAJLG4.SQLXPLR.UPDATE'
   2. In the Update Data Set field, type the name of the sequential data set to which the access path statistics for the Migrate Access Path Statistics job are written.
8. Press Enter or END to store changes and leave the panel:
   • Press Enter to continue to the next set of options in the menu sequence.
   • Press END once to return to the previous panel, or twice to return to the main Options panel.

Generating_a_list_of_packages_to_Explain

 Use the following procedure to generate a list of packages to Explain.

1. Access the Explain Object Specification panel.The method varies depending on the product:

   • On the SQL Explorer Main Menu, type 1 (for Explain), change the SSID and DDF locations if necessary, and press Enter.
   • On the APPTUNE Main Menu, select Explain Interface and press Enter.
   • On the MainView for DB2 - DC Main Menu, select Explain Interface and press Enter.

   The  Explain Object Specification panel is displayed.

   Note

   The product recognizes only the information that applies to the object type that you specify. For example, if you specify to Explain a package, the product ignores information in the Plan, DBRM, or DBRMLIB fields. You do not need to remove any unrelated values before proceeding.

   PSSPA110 ---------------- Explain Object Specification ------------------------
    Command ===>                                                                   
                                                                                  
    Type  . . 2  (1=Plan, 2=Package, 3=DBRM, 4=DBRMLIB, 5=Ad Hoc SQL)              
                                                                                  
    Plan:                                                                          
      Name    DYNSTAPL                                                             
                                                                                  
    Package:                                                                       
      COLLID  C9AN31C              Name . . . *                                    
      Version *                                                                    
                                                                                  
    DBRM:                                                                          
      Plan    V10LGUPL              Name . . . *                                    
                                                                                  
    DBRMLIB:   (Specify PDS with member name or wildcard member.)                  
      DSN . . 'PSS.TESTING.V10.DBRM(V10TEST1)'                                       
                                                                                  
    Processing Mode:  L         (L=List object(s),                                 
                                 B=Batch Explain with specified objects)
2. In the Type field, select Package.

3. In the Package: COLLID field, type the name of the package collection.You can use wildcards in this field.

   Tip

   If the information is truncated, place your cursor on the field, and press F4 or F6 (ZOOM). The contents of the entire field are displayed in a pop-up window.

4. In the Package: Name field, type the name of the package that you want to Explain.This name is the one used when the package was bound. You can enter a specific name, or use wildcards to display a list and select the package from the list. If the Name information is truncated, place your cursor on the field, and press F4 or F6 (ZOOM). The contents of the entire field are displayed in a pop-up window.

5. In the Package: Version field, type the version identifier for the package.You can type a specific version, use wildcards, or leave the field blank (for packages that do not use a version). If the package that you want to Explain has a version but you leave this field blank, the package does not appear in your results.

   Tip

   BMC recommends using a wildcard because package versions can contain mixed-case characters or timestamps and can be long and difficult to remember.

6. In the Processing Mode field, select List object(s) to view a list of object specification criteria.
7. In the Explain Options field, type any character in the space beside the field, and press Enter.The Explain Options panel is displayed.

8. Specify options for the Explain.The Explain Object Selection List panel is displayed .

    

     FILE   COMMANDS   OPTIONS   HELP
   -------------------------------------------------------------------------------
   PSSPE100                    Explain Object Selection List            7 Objects
    Command ===>                                                  Scroll ===> CSR  
                                                                     More:       >
       Actions: S H R RS RW RI XD XS XC XP XB                                         
       COLLID           NAME     VERS     OWNER    VALID OPER REOPT VALIDATE ISOLAT
       C9AN31C          TR003A            C9AN31C  Y     Y    N     B        S     
       C9AN31C          TR004A            C9AN31C  Y     Y    N     B        S     
       C9AN31C          TR00BA            C9AN31C  Y     Y    N     B        S

Specifying_Explain_options 

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

1. Access the Explain Options panel.

    

   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, authid)                          
     Qualifier Name . . . . . QUAL     (QUAL, authid)  For unqualified SQL       
     Degree . . . . . . . . .          (blank/1/Any)                             
                                                                                
   Static Explain or Explain Package:
     Plan Table Rows  . . . . L        (L=Last bind time, A=ALL rows)            
                                                                                
   Read-Only Application  . . N        (Y/N)                                     
   Online Application . . . . N        (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 DBRM and DBRMLIB

    

    

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. For 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 shown in the following table.

      

     Field	Values
     Plan Table Owner	Type a qualifier for the plan table used by the Explain operation, as follows: USERID uses the job submitter’s user ID for the plan table. Explain reverts to this value if you do not have the authority to perform the SET CURRENT SQLID command. authID uses authID.Plan_Table, where authID can be any valid DB2 qualifier. OWNER specifies the owner of the package or DBRM Tip BMC recommends that you point to BMC or empty plan tables. If your plan table has a lot of 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. Note 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. Note In a plan, the value of Degree applies only to the DBRMs bound directly to the plan, and has no effect on PKLIST names. The value has no effect on dynamic SQL statements, which use the value of the special register CURRENT DEGREE.

   • For a Static Explain or Explain Package, specifies 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 Note If the last bind on the object did not specify EXPLAIN(YES), no static Explain information is retrieved from the PLAN_TABLE.

4. 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.
5. 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.

6. 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.

   Note

   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 Common 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, Common Explain creates them.

7. 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 BMC Software recommends that you use ISO 4217 standard codes for the representation of currencies. The default is USD.

8. 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.
9. 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.

10. 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.
11. In the Recall DBRMLIB Data Set field, specify whether to restore the partitioned data set (PDS) when performing an Explain on a DBRM or DBRMLIB.

12. 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.

Specifying_batch_Explain_options

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

1. Access the Explain Options panel.

   ------------------------- Batch Explain Options ------------------------------
   
   Explain INSERT Statements  Y   (Y/N)                                          
   Rule Message Level . . . . A   (A=All, S=Severe, W=Warning, I=Informational)  
   Suppress Statement Detail When No Rules Fire  N  (Y/N)
   
   Maintain History . . . . . N   (Y/N)                                          
     Histories Retained . . . 3   (0-99)                                         
                                                                                
   Summary Report Sort Order  1   1. Object, Statement                           
                                  2. Cost (desc)                                 
                                  3. Object, Cost (desc)                         
                                                                                
   Generate Report Title  . . Y   (Y/N)                                          
     Title   EXPLAIN REPORT

    

    

2. In the Explain INSERT Statements field, select one of the following options:
   • Y Explains the INSERT statements.
   • N skips the INSERT statements.

3. Specify the Rule Message Level to display in the report:

   • All includes Informational, Warning, and Severe messages in the report. (default)
   • Severe flags only the statements and situations that might cause severe performance problems, such as a SELECT * statement.

   • Warning flags statements that can be improved to enhance performance.

     Arithmetic operations become Non-indexable, Stage 2 predicates.
     Consider comparing to a computed value instead of the expression.

   • Informational includes messages that provide information that might help you make changes to improve performance.

     The Column Functions (MIN/MAX) on Composite index can take advantage
     of One-Fetch access when there exists a matching predicated on the
     high order column other than a LIKE predicate

4. In the Suppress Statement Detail When No Rules Fire field, specify whether to suppress detail in batch Explain reports. This option is used in conjunction with Rule Message Level. It does not affect the analysis object summary information or the summary report.

   • Y suppresses the detail in a batch Explain report when no rules are issued, based on the Rule Message Level that you specify.
   • N displays the detail in a batch Explain report, based on the Rule Message Level that you specify. (default)

   The following table lists the results of various combinations of these options.

   Rule Message Level is	and	and Suppress Statement Detail is either
   		Y	N (default)
   Severe	Severe rules are issued	All batch report sections and severe rules are displayed.
   	No severe rules are issued	No batch report sections nor rules are displayed.	All batch report sections are displayed, but no rules are shown.
   Warning	Both warning and severe rules are issued	All batch report sections and warning and severe rules are displayed.
   	Neither warning nor severe rules are issued	No batch report sections nor rules are displayed.	All batch report sections are displayed, but no rules are shown.
   	Only severe rules are issued	All batch report sections and only severe rules are displayed.
   	Only warning rules are issued	All batch report sections and only warning rules are displayed.
   Informational	Informational, warning, and severe rules are issued	All batch report sections and severe, warning, and informational rules are displayed.
   	Only informational and warning rules are issued	All batch report sections and only warning and informational rules are displayed.
   	Only informational rules are issued	All batch report sections and only informational rules are displayed.
   	No informational, warning, or severe rules are issued	All batch report sections are displayed, but no rules are shown.
   	Only warning rules are issued	All batch report sections and only warning rules are displayed.
   	Only severe rules are issued	All batch report sections and only severe rules are displayed.
   	Only warning and severe rules are issued	All batch report sections and only warning and severe rules are displayed.

   
   

5. In the Maintain History field, specify whether to create and save a history of the Explain operation on the object for later use in the Compare process.

   Note

   If you are explaining a DBRMLIB, an ad hoc SQL statement, or by statement only, Explain ignores this option, regardless of what you specify.

6. In the Histories Retained field, specify the number of history records to retain for the object, for later use in the Compare process.When the specified number of histories is reached, the oldest entries are dropped and the newest entries are retained. The range for this option is from 0 to 99.

   Note

   You must specify Y for the Maintain History option to enable this option.

   A value of 0 deletes all existing history rows for the object, or saves none.

7. Select a Summary Report Sort Order:

   • Object, Statement shows the report sequenced by object and statement.
   • Cost (desc) shows the report sequenced by cost in descending order.
   • Object, Cost (desc) shows the report sequenced by object and cost in descending order.

   This report appears at the top of the output, and contains one line for each object and statement, and information such as counts for scans, joins, and so on.

8. In the Generate Report Title field, specify whether to include a report title in the output.

9. Type a title for the report, and press Enter.

   Note

   You must specify Y for the Generate Report Title field to enable this option.

Allocating_a_data_set_for_a_specific_job

 If an analysis job requires data sets that are not already allocated, the product displays the Allocate Data Set panel. Use this panel to enter or change information about the data set allocation.

To allocate a data set

1. In the Allocate Data Set with the Following Values field, accept the default (Y) to allocate the data set.Specifying N cancels the allocation.

   Note

   The DD Name and Data Set Name fields show the DD name and data set name of the data set to be allocated. You cannot modify this information.

2. In the Volume Serial field, specify the volume serial (VOLSER) of the direct access volume where you want the data set to reside.Leave this field blank if you want Storage Management to select an eligible volume. (The volume must already be defined for your environment.)
3. In the Generic Unit field, specify the generic group name or unit address for the direct access volume where you want the data set to reside (for example, SYSDA, 3380, 3390).Leave this field blank if you want Storage Management to select the unit.
4. In the Space Units field, specify the unit in which the data set size is expressed:
   • BLKS for blocks
   • TRKS for tracks
   • CYLS for cylinders (default)
5. In the Primary Quantity and Secondary Quantity fields, specify the primary and secondary allocation quantities.For both fields, the default is 1, and the maximum value that you can specify is 16777215. The unit is the unit type that you specified in Step 4.
6. In the Directory Blocks field, specify the number of 256-byte directory blocks to be provided for a partitioned data set.If you leave this field blank, the number defaults to 64. To allocate a sequential data set, specify 0. The maximum value you can specify for a partitioned data set is 99999.
7. In the Record Format field, specify one of the following formats:
   • FB (fixed length, blocked records)
   • FBA (fixed length, blocked records with ASA printer control characters for SYSPRINT data sets)
8. In the Record Length field, specify the logical record length, in bytes, of the records to be stored in the data set.
9. In the Block Size field, specify the block size (physical record length), in bytes, of blocks to be stored in the data set:
   • If the specified record length is 80, the record format defaults to FB and the block size defaults to 3120.
   • If the specified record length is 100, the record format defaults to FB and the block size defaults to 3200.
   • If the specified record length is 132, the record format defaults to FBA and the block size defaults to 3300.
10. Specify the management, data, and storage classes:
    • In the Management Class field, specify the name of the SMS management class that contains the migration and backup information for allocating the data set.
    • In the Data Class field, specify the name of the SMS data class that contains the SPACE and LRECL information for allocating the data set.
    • In the Storage Class field, specify the name of the SMS storage class for allocating the data set.

Expert_rules (asq)

 The 

APPTUNE for DB2

 Explain feature is driven by a set of expert rules.These rules trigger messages that tell you where performance problems exist and what design changes are needed. Each expert rule has an associated severity value and threshold.

Customizing_expert_rules

 You can customize or create your own custom rule sets. Use this procedure to copy one of the default rule sets and modify its rules to meet your needs.Consider the following when defining your own rules set:

• The facts and variables within the predefined rules are set internally. You can use the same facts within rules you define. 
• You can modify the severity codes on messages to adjust them to the severity level that you require.
• If you do not want a rule to trigger, use one of the following methods to disable it:
  • Enclose rules that you do not want to fire within a comment statement. The syntax for a comment is /* commentText */.

  • Set the predicate comparison to A=0. For example, you could modify a rule as follows to ensure that it is never triggered:

    Example

    RULE1008: IF A = 0
        THEN OUT=OUT||'BMC184008X-A Correlated Subquery will be e'||
        'valuated for each row in the composite table. It performs like a N'||
        'ested Loop Join./n'
  • Set the severity code of the message to X.

• If you want a rule to always trigger, ensure that the predicate always evaluates to true.

  Example

  RULE1008: IF 0 = 0
      THEN OUT=OUT||'BMC184008I-A Correlated Subquery will be e'||
      'valuated for each row in the composite table. It performs like a N'||
      'ested Loop Join./n'

• If your message text is longer than a single line, ensure that you use the || characters to append the next line. Enclose each line in the message within quotation marks and end the message with /n.

  Example

  RULE5056: IF FULLOUTER = 'Y'
            THEN OUT=OUT||'BMC184620I-The clause FULL OUTER JOIN incl'||
           'udes unmatched rows from both tables. Missing values in the result'||
           ' table are filled with nulls./n'
• You can use variable values within the message text:

  • If you want to display a variable value in the message, insert the variable name within || characters. Do not include the variable name within quotation marks. For example, the following message includes the variable value for LOCKSIZE on the final line:

    Example

    RULE2054P: IF LOCKRULE = 'P' THEN LOCKSIZE = 'PAGE'                   
    RULE2054R: IF LOCKRULE = 'R' THEN LOCKSIZE = 'ROW'                    
    RULE2054T: IF LOCKRULE = 'T' THEN LOCKSIZE = 'TABLE'                  
    RULE2054S: IF LOCKRULE = 'S' THEN LOCKSIZE = 'TABLESPACE'             
    RULE2054L: IF LOCKRULE = 'L' THEN LOCKSIZE = 'LOB'                    
    RULE2054X: IF LOCKRULE = 'X' THEN LOCKSIZE = 'XML'                    
                                                                         
    RULE2054: IF LOCKRULE <> 'A'                                          
              THEN OUT=OUT||'BMC184224W-The tablespace has a LOCKSIZE t'||
    'hat does not match your installation standard. The current LOCKSIZ'||
    'E is '||LOCKSIZE||'./n'

  • If the value that you want to display is a numerical value, check to see if an equivalent string variable exists. The equivalent variable name has a C appended. For example, the following message includes the variable value for SEGSIZE on the final line:

    Example

    RULE2056: IF TBTYPE = 'T' AND SEGSIZE > 2
              THEN OUT=OUT||'BMC184226W-The tablespace has a SEGSIZE th'||
              'at does not match your installation standard.The current SEGSIZE '||
              'is '||SEGSIZEC||'./n'

To customize expert rules

1. Copy one of the following members in the BMCSAMP data set to create your own version of the rule set:

   • PSSDFLT (for the DEFAULT rule set)
   • PSSAPPL (for the APPLDEV rule set)
   • PSSJAPAN (for the JAPAN rule set)

   Note

   Do not modify the sample members directly. These files will be overwritten and any changes lost the next time that you install the product. Always create a copy of the file and save it in the hlq.UBMCSAMP library to modify.

2. Modify the copy of the rule set in the hlq.UBMCSAMP data set as needed.Ensure that the rules conform to the structure. Ensure also that the RULESET name matches the name of the new rules member, as does NEWRULE1 in the following example:

   Example

   BROWSE HLQ.LLQSAMP(NEWRULE1) -
   Command ===>
   ********************************* Top of Data ****
     
   RULESET NEWRULE1

3. Update the Explain options to identify this rule set.

   1. At the SQL Explorer main menu, select 0 (Options).
   2. At the Options panel, select 1 (General).
   3. At the General Default Options panel, press Enter.
   4. At the Explain Options panel, specify the member name of the rule set that you want to use in the Rule set field and press Enter.

   Note

   For information on the other options that you can enter on the Explain Options panel, see Specifying Explain options.

   You can also access the Explain Options panel from the Explain Object Specification panel by selecting the Explain Options field.