Creating input EXPLAIN tables under a separate schema

You can simplify the task of populating EXPLAIN tables that are required for command input by creating the tables under a separate schema.

About this task

When you issue commands such as BIND QUERY, you often must populate rows in certain related tables that contain input values. By default DB2® uses data from the EXPLAIN tables that are created under the schema of the issuer of the BIND QUERY command. Therefore, you can use the same tables as both output for EXPLAIN data and as input for the BIND QUERY command.

However, the EXPLAIN output tables are likely to contain many more rows than are needed by the BIND QUERY command. The presence of such additional rows might interfere with the hint or override that you want to create. To avoid that problem, you can specify that DB2 uses input tables under a different schema.

To create separate tables that provide values for BIND QUERY command input:

1. Create EXPLAIN tables under your schema.
2. Create instances of the following objects under a new schema.
   • DSN_USERQUERY_TABLE table
   • PLAN_TABLE table
   • PLAN_TABLE_HINT_IX index
   • DSN_PREDICAT_TABLE table
   • DSN_PREDICAT_TABLE_IX index

   You can find sample statements for creating these objects in member DSNTESC member of the SDSNSAMP library.

3. Capture EXPLAIN information for the statements to populate the EXPLAIN tables under your schema.
4. Select only the needed rows from the EXPLAIN tables under your schema, and insert the rows into the EXPLAIN tables under the new schema. Not every table is used for every method of influencing access path selection. However, it is best to create all of the objects in the preceding list and populate only the objects that are needed.
5. Modify the rows as necessary with information that DB2 uses for access path selection. For detailed information about the rows and values that are needed, see the information about the type of hints that you want to create:
   • Statement-level access paths
   • Statement-level optimization parameters
6. Issue the BIND QUERY command and specify the EXPLAININPUTSCHEMA option. For example, the following command specifies use of the input tables under the BINDQ schema:

   BIND QUERY EXPLAININPUTSCHEMA('BINDQ')

   DB2 uses the values from the tables under the specified schema to populate the related catalog tables.