Specifying access paths in a PLAN_TABLE instance

You try to enforce a particular access path for a SQL statement that is issued by a specific single authorization ID by creating PLAN_TABLE access paths.

Before you begin

The following prerequisites are met.

• Prepare to manage access paths.
• An instance of the PLAN_TABLE table is created under the authorization ID that issues the SQL statement.
• 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.

To specify access paths in a PLAN_TABLE instance:

1. Optional: Include a QUERYNO clause in your SQL statements.
   The following query contains an example of the QUERYNO clause:

   SELECT * FROM T1
     WHERE C1 = 10 AND
           C2 BETWEEN 10 AND 20 AND
           C3 NOT LIKE 'A%'
     QUERYNO 100;

   This step is not required for specifying access paths in PLAN_TABLE instances. However, by specifying a query number to identify each SQL statement you can eliminate ambiguity in the relationships between rows in the PLAN_TABLE and the SQL corresponding statements.

   For example, the statement number for dynamic applications is the number of the statement that prepares the statements in the application. For some applications, such as DSNTEP2, the same statement in the application prepares each dynamic statement, meaning that every dynamic statement has the same statement number.

   Similarly, when you modify an application that contains static statements, the statement numbers might change, causing rows in the PLAN_TABLE to be out of sync with the modified application. Statements that use the QUERYNO clause are not dependent on the statement numbers. You can move those statements around without affecting the relationship between rows in the PLAN_TABLE and the corresponding statements in the application.

   Such ambiguity might prevent Db2 from enforcing the specified access paths.

2. Insert a name for the specified access path in the OPTHINT column of the PLAN_TABLE rows for the SQL statement.
   This step enables Db2 to identify the PLAN_TABLE rows that specified the access path.

   UPDATE PLAN_TABLE
     SET OPTHINT = 'NOHYB'
     WHERE QUERYNO = 200 AND
           APPLNAME = ' ' AND
           PROGNAME = 'DSNTEP2' AND
           VERSION = ' ' AND
           COLLID = 'DSNTEP2';

3. Optional: Modify the PLAN_TABLE rows to instruct to Db2 try to enforce a different access path.
   You might also use PLAN_TABLE access paths only to try to enforce the same access path after a rebind or prepare. In that case, you can omit this step. However, remember that PLAN_TABLE access paths are not the recommended method for enforcing existing access paths after rebind or prepare. Use the APREUSE option at rebind, or specify a plan management policy instead.

   For example, suppose that Db2 chooses a hybrid join (METHOD = 4) when you know that a sort merge join (METHOD = 2) might perform better. You might issue the following statement.

   UPDATE PLAN_TABLE
     SET METHOD = 2 
     WHERE QUERYNO = 200 AND
           APPLNAME = ' ' AND
           PROGNAME = 'DSNTEP2' AND
           VERSION = '' AND
           COLLID = 'DSNTEP2' AND
           OPTHINT = 'NOHYB' AND
           METHOD = 4;
   

4. Instruct Db2 to begin enforcing the specified access path:

   Option	Description
   For dynamic statements...	Issue a SET CURRENT OPTIMIZATION HINT = 'hint-name' statement. If the SET CURRENT OPTIMIZATION HINT statement is a static SQL statement, rebind the plan or package. Issue an EXPLAIN statement for statements that uses the access path. Db2 adds rows to the plan table for the statement and inserts the 'hint-name' value into the HINT_USED column. If the dynamic statement cache is enabled, Db2 tries to use the hint only when no match is found for the statement in the dynamic statement cache. Otherwise, Db2 uses the cached plan, and does not prepare the statement or consider the specified access path.
   For static statements...	Rebind the plan or package that contains the statements and specify the EXPLAIN(YES) and OPTHINT('hint-name') options.

   Db2 uses the following PLAN_TABLE columns when matching rows that specify access paths to SQL statements:

   • QUERYNO
   • APPLNAME
   • PROGNAME
   • VERSION
   • COLLID
   • OPTHINT

   It is best to create an index on these columns for the PLAN_TABLE when you specify access paths in a PLAN_TABLE instance.

   If Db2 uses all of the access paths that you specified, it returns SQLCODE +394 from the PREPARE of the EXPLAIN statement and from the PREPARE of SQL statements that use the specified access paths. If any of your the specified access paths are invalid, or if any duplicates were found, Db2 issues SQLCODE +395. You can suppress SQLCODES +394 and +395 for dynamic SQL statements by setting the value of the SUPPRESS_HINT_SQLCODE_DYN subsystem parameter.

   If Db2 does not find that an access path is specified, it returns another SQLCODE. Usually, this SQLCODE is 0.Db2 also returns a message at the completion of the bind operation to identify the numbers of statements for which hints were fully applied, not applied or partially applied, and not found.

5. Select from the PLAN_TABLE to check whether Db2 used the specified access path.
   For example, you might issue the following statement:

   SELECT *
     FROM PLAN_TABLE
      WHERE QUERYNO = 200
     ORDER BY TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ; 

   The following table shows the example PLAN_TABLE data. The OPTHINT column contains the value NOHYB where the specified access path was used. You can also see that Db2 used that access path, as indicated by the NOHYB value in the HINT_USED column.

   Table 1. PLAN_TABLE that shows that the NOHYB access path is used.

   QUERYNO	METHOD	TNAME	OPTHINTS	HINT_USED
   200	0	EMP	NOHYB
   200	2	EMPPROJACT	NOHYB
   200	3		NOHYB
   200	0	EMP		NOHYB
   200	2	EMPPROJECT		NOHYB
   200	3			NOHYB