Options for TEST

There are several options on the Process TEST of packages panel. These allow you to specify filters and to customize the information returned by a test.

Options

  • Select the report type to display: Enter D to display the Delta report or S to display the Summary report. The Summary report contains one line per SQL statement to meet the filter requirements. The Delta report contains more detailed information about the SQL statement.
  • From catalog: Indicates where the statements used for comparing to the package will be obtained. Valid values: C, N, Y.
  • EXPLAIN table SQLID: the SQLID to run EXPLAIN on the specified packages.
  • Current degree: The degree of parallelism for a dynamic SQL or QMF statement.
  • EXPLAIN type: This field has two values:
    • Dynamic (D): The EXPLAIN will use Db2 dynamic path selection.
    • EXPLAIN only (X): The EXPLAIN will use a bind with the EXPLAIN(ONLY) option, which uses more system resources but produces a more accurate access path prediction.
  • Package cost filter: Processes packages with a different total cost (all statements in the package). Valid values are
    • N: no filtering
    • Y: TOTAL_COST has changed
    • - (minus sign): TOTAL_COST has decreased
    • + (plus sign): TOTAL_COST has increased
    The default is N.
  • Statement cost filter: Processes statements with a different total cost. Valid values are:
    • N: no filtering
    • Y: TOTAL_COST has changed
    • - (minus sign): TOTAL_COST has decreased
    • + (TOTAL_COST has increased)
    The default is N.
  • Always show access path changes: This option lets you see records that have changes in their access path, even if they do exceed cost limits. Select Y to see all changes, or N to filter out changes that exceed cost limits.

    The default is N.

  • Minimum percent cost change to display: Minimum package/statement total cost the compare will process. If both Package and Statement cost filters are specified, the threshold value will first be used for Package total cost and then for statements inside the package. If both cost filters are set to N, this option has no effect.
  • Show invalid packages: Determines whether a warning message will be shown for packages flagged as INVALID in SYSIBM.SYSPACKAGE. An invalid package will be skipped, and if this field is set to Y, a warning message will be issued in the report for all invalid packages. For example: ANL0213W Package collid.pkgname is flagged as INVALID by Db2. Rebind is recommended.

Advanced options for Test

For advanced options enter command ADVOPT.

SQLPA520 ------------------ Advanced Options ------------------- 11:32                                                          
                                                                    
  CSV Options:                                                          
    Create CSV report . . N  (N/Y)                                      
    Delimiter character . ;  (;/,)                                      
                                                                        
  Match options:                                                        
    Statement matching . . . . . . . . . . .. . 1    (1, 2, Q, S)       
                                                                        
  Display options:                                                      
    Show percent change . . . . . . . . . . . . Y           (N/Y)       
    Show only packages bound before . . . . . .

Advanced options

  • Create CSV report: The value of this field indicates whether to create a CSV file for the COMPARE report. Valid values are Y, and N. The default value is N.
  • Delimiter character: The value of this field indicates the column delimiter for the CSV file. The valid values are semicolon (;) or a comma (,). The default value is semicolon (;).
  • Statement matching: Indicates the method used to match the statements between package versions. The valid values are 1, 2, Q, and S.
    • A value of 1 matches by table and query type.
    • A value of 2 matches by table, query type, and statement text.
    • A value of Q matches by query number.
    • A value of S matches by the sequence of the statements.

    This command is optional and defaults to 1 if not specified. This is the same as the STMTMATCH command in batch.

  • Show invalid packages: Determines whether a warning message will be shown for packages flagged as INVALID in SYSIBM.SYSPACKAGE. An invalid package will be skipped, and if this field is set to Y, a warning message will be issued in the report for all invalid packages. For example: ANL0213W Package collid.pkgname is flagged as INVALID by Db2. Rebind is recommended.
  • Show percent change: This option shows all numeric fields
    (ACCESS_DEGREE, JOIN_DEGREE, MATCHCOLS,
        PROCMS, PROCSU and TOTAL_COST)
    to be shown with an additional percent change display. The default is N (no display).
  • Show only packages bound before (timestamp): Specify a partial Db2 timestamp in the format yyyymmddHHMMSS to show packages bound packages that were bound before that time. Leave the field blank to select all packages bound before the current time.

Advanced Explain and Bind options for Test.

The APREUSE and APREUSESOURCE options are used for BIND/REBIND process. To set the values of the APREUSE and APREUSESOURCE options, use the EXPLOPT command. This command is displayed on the command line of the 'Process TEST of packages' panel. To open this panel from the command line, right click on the mouse or enter the EXP command.

If the value of the Explain type option is set to 'x', SQL PA passes the value of the APREUSE and APREUSESOURCE options to the BIND/REBIND operators. The REBIND operator is used only when a value is provided for the DBRM Library field, else the BIND operator is used. 

SQLPA520 ---------- Advanced Explain and Bind options ---------- 15:48  
                                                                         
  Enter parameters:                                                      
                                                                         
  For explain types D and X:                                             
    Current degree . . . : ANY      (1/Any)                              
                                                                         
  For explain type X only:                                               
    APREUSE option . . . : ERROR    (NONE,NO,ERROR,WARN)                 
    APREUSESOURCE option : ORIGINAL (CURRENT,PREVIOUS,ORIGINAL)

Explain and Bind options:

  • Current degree: The DEGREE option determines whether to attempt to run a query using parallel processing to maximize performance.

  • APREUSE option: The APREUSE option specifies whether Db2 tries to reuse previous access paths for SQL statements in a package. Db2 uses information about the previous access paths from the directory to create a hint.

    Valid values are NONE, NO, ERROR, WARN.

  • APREUSESOURCE option:The APREUSESOURCE bind option, when used with the APREUSE bind option, specifies which package copy to use for EXPLAIN information for access path reuse processing. You can use the APREUSESOURCE bind option to specify which package copy is used for access path reuse when the APREUSE option is specified.

    Valid values are CURRENT, PREVIOUS, ORIGINAL.

If APREUSE is set to NO or NONE, APREUSESOURCE is not used for the BIND/REBIND processes.

For more information on these options, see Testing current access paths for packages in batch, which explains the batch commands corresponding to the settings on this ISPF panel.