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:
About this task
Statement-level access paths use matching of the statement
text to apply the specified access path 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 create statement-level access paths:
- INSERT rows into the DSN_USERQUERY_TABLE
table.
- 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 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.
- 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.
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
)
SELECT 1111111, 'MYSCHEMA_1', 1,
STATEMENT,
'','',
COLLID, NAME, VERSION,
'', '',
-1, '', -1, ''
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.
- 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.
- Issue a BIND QUERY
command. You must
omit the LOOKUP option or specify LOOKUP(NO). 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.
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.
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.
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:
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.
- 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.