Specifying optimization parameters at the statement level

You can customize the values of certain optimization-related subsystem parameters and bind options for all instances of particular SQL statements within particular scopes by creating statement-level optimization parameters.

Before you begin

Begin program-specific programming interface information.The following prerequisites are met:
  • Prepare to manage access paths.
  • You have one of the following authorities:
    • SQLADM
    • SYSOPR
    • SYSCTRL
    • SYSADM
  • An instance of the DSN_USERQUERY_TABLE user table is created under your schema, or under a separate schema for input tables. For more information about using tables under a separate schema see Creating input EXPLAIN tables under a separate schemaYou can find sample CREATE statements for the tables and associated indexes in members DSNTESC and DSNTESH of the prefix.SDSNSAMP library.
  • The package that contains the statement was created by a BIND PACKAGE statement. Statement-level methods for influencing access paths are not supported for statements in packages that are created by other statements, such as CREATE FUNCTION, CREATE TRIGGER, and CREATE PROCEDURE statements.
  • An index is created on the following PLAN_TABLE columns:
    • QUERYNO
    • APPLNAME
    • PROGNAME
    • VERSION
    • COLLID
    • OPTHINT

    A sample statement that creates the index is included in member DSNTESC of the SDSNSAMP library.

About this task

Statement-level optimization parameters use matching of the statement text to apply the specified optimization parameter value to all instances of a statement within one of the following scopes:

  • System-wide
  • From any version of particular collection and package
  • From a particular version of a collection and package

Procedure

To specify statement-level optimization parameters:

  1. INSERT rows into the DSN_USERQUERY_TABLE table.
    1. Insert values in the following columns to specify the SQL statement and context for the optimization parameter:
      QUERYNO
      Specify any value that does not correlate to PLAN_TABLE rows and does not already exist in another DSN_USERQUERY_TABLE row. The QUERYNO value is used only for the primary key of DSN_USERQUERY_TABLE.
      SCHEMA
      If the SQL statement contains unqualified object names that might resolve to different default schemas, insert the schema name that identifies the unqualified database objects. If the statement contains unqualified objects names because it might apply to different schemas at different times, you must create separate hints or overrides for each possible SCHEMA value. If the statement contains only fully qualified object names, the SCHEMA value is not required. However, you can still insert a SCHEMA value to help you identify that the hint relates to a certain schema.
      QUERY_TEXT
      Insert the text of the statement whose access path you want to influence.

      The text that you provide must match the statement text that Db2 uses when binding static SQL statements and preparing dynamic SQL statements. For more information about how to enable successful text matching, see Populating query text for statement-level matching.

      HINT_SCOPE
      Insert a value to specify that context in which to match the statement.
      0
      System wide. Db2 uses only the text of the SQL statement and the value of the SCHEMA column, when it contains a value, to determine whether the statement matches.
      1
      Package-level. Db2 uses the values of the COLLECTION, PACKAGE, and VERSION columns to determine whether the statement matches.
      COLLECTION
      Insert the collection ID of the package. This value is required only when the value of HINT_SCOPE is 1.

      When the value of HINT_SCOPE is 0, the value is optional, and when a value is specified Db2 issues an error message when you bind the query if the matching value is not found in the SYSIBM.SYSPACKAGE catalog table. When HINT_SCOPE is 0, either specify both COLLECTION and PACKAGE or leave both fields blank.

      For static SQL statements and dynamic SQL statements that use the DYNAMICRULES(BIND) option, you might need to specify the value of this column so that Db2 can retrieve the correct application default values from the SYSIBM.SYSPACKSTMT catalog table.

      PACKAGE
      Insert the name of the package. This value is required only when the value of HINT_SCOPE is 1.

      When the value of HINT_SCOPE is 0, the value is optional, and when a value is specified Db2 issues an error message when you bind the query if the matching value is not found in the SYSIBM.SYSPACKAGE catalog table. When HINT_SCOPE is 0, either specify both COLLECTION and PACKAGE or leave both fields blank.

      For static SQL statements and dynamic SQL statements that use the DYNAMICRULES(BIND) option, you might need to specify the value of this column so that Db2 can retrieve the correct application default values from the SYSIBM.SYSPACKSTMT catalog table.

      The package-specific scope is intended primarily to support the staging, validation, and testing of statement-level hints, before they are deployed with a system-wide scope.

      VERSION
      Insert the version identifier of the package or '*'. A value in this column is required only when the value of HINT_SCOPE is 1. When you specify '*' for the VERSION column, Db2 does not require matching of the VERSION column for statement matching.

      When the value of HINT_SCOPE is 0, this value is optional. When a value is specified Db2 issues an error message when you bind the query if the matching value is not found in the SYSIBM.SYSPACKAGE catalog table.

      For static SQL statements and dynamic SQL statements that use the DYNAMICRULES(BIND) option, you might need to specify the value of this column so that Db2 can retrieve the correct application default values from the SYSIBM.SYSPACKSTMT catalog table.

      SELECTVTY_OVERRIDE
      Specify a value to indicate whether selectivity overrides are specified. Unless you specifically want to enable both option overrides and selectivity overrides for the same statement, specify 'N' in this column. If you want to specify both types of overrides, specify 'Y'.
      ACCESSPATH_HINT
      Specify a value of 'N' to indicate that no access path is specified. You cannot specify both access paths and selectivity overrides for the same statements.
      OPTION_OVERRIDE
      Specify a value of 'Y' to indicate that statement-level optimization parameters are specified.
      Important: A previous format of DSN_USERQUERY_TABLE that does not contain the SELECTVTY_OVERRIDE, ACCESSPATH_HINT, and OPTION_OVERRIDE columns is also supported for compatibility. However, the recommendation is to use the most recent format of DSN_USERQUERY_TABLE and specify values in each of these columns. By specifying these values, you simplify the process of using only intended methods to influence access paths.
    2. Insert values that define the optimization parameters.
      Statement-level optimization parameters are created only when the QUERYNO value that you specify in DSN_USERQUERY_TABLE does not correlate to existing PLAN_TABLE rows. You can specify settings for the following optimization parameters and options:
      • REOPT bind option
      • STARJOIN subsystem parameter
      • PARAMDEG subsystem parameter (MAX_PAR_DEGREE column)
      • CDSSRDEF subsystem parameter (DEF_CURR_DEGREE column)
      • SJTABLES subsystem parameter
      For example, you might execute the following SQL statement to insert a row into DSN_USERQUERY_TABLE that creates an optimization parameter hint:
      INSERT INTO DSN_USERQUERY_TABLE 
      ( QUERYNO, SCHEMA, HINT_SCOPE, QUERY_TEXT,
        USERFILTER, OTHER_OPTIONS,
        COLLECTION, PACKAGE, VERSION, 
        REOPT, STARJOIN, MAX_PAR_DEGREE, 
        DEF_CURR_DEGREE, SJTABLES, OTHER_PARMS,
      SELECTVTY_OVERRIDE, ACCESSPATH_HINT, OPTION_OVERRIDE
      )   
        VALUES  
        (100, 'MYSCHEMA_1', 0,
         'DECLARE C06 CURSOR FOR
           SELECT N_NAME, COUNT(*)
             FROM ORDER, CUSTOMER, NATION_NP
             WHERE C_NATIONKEY = N_NATIONKEY
               AND C_CUSTKEY = O_CUSTKEY
               AND N_REGIONKEY = :H
               AND O_ORDERDATE BETWEEN ''1998-01-01'' AND ''1998-03-31''
             GROUP BY N_NAME',                               
         'USER001', '',        
         '', '', '',            
         'Y', '', -1, 
         '', -1, '',
      'N', 'N', 'Y'
      );     

      The result is that Db2 uses the REOPT(ALWAYS) optimization parameter for instances of the specified statement.

      End program-specific programming interface information.
  2. Issue a BIND QUERY command.
    You must omit the LOOKUP option or specify LOOKUP(NO).
    If you created input tables under a dedicated schema, specify the EXPLAININPUTSCHEMA bind option and the name of the schema.
    Db2 takes the input from every DSN_USERQUERY_TABLE row, and from related input tables, and inserts data into the following catalog tables:
    • SYSIBM.SYSQUERY
    • SYSIBM.SYSQUERYOPTS

    The QUERYID column correlates rows in these tables.

Results

The catalog table rows for static SQL statements are validated and applied when you rebind the package that contains the statements. Catalog table rows for dynamic SQL statements are validated and enforced when the statements are prepared.

What to do next

Consider taking the following actions:

  1. Validate that the appropriate catalog table rows have been created:
    1. Insert row into the DSN_USERQUERY_TABLE table that contain values in the QUERY_TEXT and SCHEMA columns.
    2. Issue the following command:
      BIND QUERY LOOKUP(YES)
      Db2 issues the following messages to indicate whether the catalog tables contain valid rows that correspond to the DSN_USERQUERY_TABLE rows.
      • A DSNT280I message for each DSN_USERQUERY_TABLE row that has matching rows in the catalog tables.
      • A DSNT281I message for each DSN_USERQUERY_TABLE row that does not have matching rows in the catalog table.
      • A single DSNT290I message if some matching rows were found in the catalog tables or a DSNT291I message if no matching rows were found.
      Db2 also updates the value of QUERYID column in the DSN_USERQUERY_TABLE table to match the value from the matching rows in the SYSIBM.SYSQUERY catalog table.
  2. Delete the DSN_USERQUERY_TABLE rows to prevent the replacement of existing catalog table rows when you issue subsequent BIND QUERY commands. When you issue a BIND_QUERY command, catalog tables rows are created or replaced for every row in DSN_USERQUERY_TABLE row. Changes to data in other input tables might have unintended consequences if old rows remain in the DSN_USERQUERY_TABLE and you issue the BIND_QUERY command again.