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
- 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:
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:
- Validate that the appropriate catalog table rows have been created:
- Insert row into the DSN_USERQUERY_TABLE table that contain values in the QUERY_TEXT and SCHEMA columns.
- Issue the following command:
Db2 issues the following messages to indicate whether the catalog tables contain valid rows that correspond to the DSN_USERQUERY_TABLE rows.BIND QUERY LOOKUP(YES)
- 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.
- 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.