Specifying access paths at the statement level

You can suggest that Db2 uses a certain access path for all instances of a particular SQL statement within a specified scope by creating statement-level access paths.

Before you begin

The following prerequisites are met:

• Prepare to manage access paths.
• You have one of the following authorities:
  • SQLADM
  • SYSOPR
  • SYSCTRL
  • SYSADM
• Instances of the following user tables exist under your schema, or under a separate schema for input tables:
  • DSN_USERQUERY_TABLE
  • PLAN_TABLE
  For more information about using tables under a separate schema see Creating input EXPLAIN tables under a separate schema. You can find sample CREATE statements for the tables and associated indexes in members DSNTESC and DSNTESH of the prefix.SDSNSAMP library.
• 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.

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

To create statement-level access paths:

1. INSERT rows into the DSN_USERQUERY_TABLE table.
   1. Insert values in the following columns to specify the SQL statement and context in which to apply the access path:

      QUERYNO

      Insert the value that correlates to the value of the QUERYNO column of existing PLAN_TABLE rows that describe the access path that you want to enforce.

      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 of 'N' to indicate that selectivity overrides are not specified. Statement-level access paths and selectivity overrides cannot coexist for the same statements.

      ACCESSPATH_HINT

      Specify a value of 'Y' to indicate that an access path is specified.

      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.
      For example, you might execute either of the following statements to populate DSN_USERQUERY_TABLE.

      • For static SQL statements, you might retrieve values from the SYSIBM.SYSPACKSTMT catalog table and insert the values by executing a statement like the following INSERT statement:

        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 
        )   
        SELECT 1111111, 'MYSCHEMA_1', 1, 
        STATEMENT, 
        '','',
        COLLID, NAME, VERSION, 
        '', '', 
        -1, '', -1, '',
        'N', 'Y', 'N' 
        FROM SYSIBM.SYSPACKSTMT
        WHERE COLLID = 'MYCOLLID_1'
        AND NAME = 'MYPACKAGE_1'
        AND VERSION = 'MYVERSION_1'
        AND STMTNO = 12;

        When validated, the result specifies that Db2 uses the access path that is described by the PLAN_TABLE row that contains the QUERYNO value of 1111111 for instances of the specified statement that are issued under the specified package version. The 1 value for HINT_SCOPE indicates that the hint applies only to instances of the statement that are issued by the specified package.

      • If the statement text and other information are not available from the SYSIBM.SYSPACKSTMT catalog table, you might issue an INSERT statement that specifies the values explicitly.
2. Populate PLAN_TABLE for the SQL statement. You can populate this table by either manually inserting one or more rows or issuing an EXPLAIN statement.
3. 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.SYSQUERYPLAN

   The QUERYID column correlates rows in these tables.