You can increase the likelihood that DB2® can identify matching SQL statements when
you use the BIND QUERY command to influence access path selection.
About this task
DB2 modifies the
statement text that is used for matching at BIND or PREPARE time.
For example, white space, SQL comments, and certain clauses such as
EXPLAIN, are removed from the query text. These changes enable DB2 to match the parsed SQL statements
during BIND and PREPARE processing.
The
following application default values must be the same at BIND QUERY
time as they are when static SQL statements are bound or dynamic SQL
statements are prepared:
- CCSID
- DECIMAL POINT
- STRING DELIMITER
Procedure
To enable successful matching of the statement text,
use the following approaches:
- For static SQL statements, and for dynamic statements that
are prepared with the DYNAMICRULES(BIND) option, specify the following
columns that specify package information for the statement in DSN_USERQUERY_TABLE:
- PACKAGE
- COLLECTION
- VERSION
These values are not strictly required. However, when these
values are specified, DB2 uses
parsing information from the SYSIBM.SYSPACKSTMT catalog table to modify
the statement text. If the values are unspecified, or the matching
package is not found during the BIND QUERY processing, DB2 uses the values that are specified in the
application defaults module.
As part of the BIND QUERY process, DB2 validates that the package if
specified, contains matching statement text. If the statement text
does not match, DB2 issues message DSNT281I and
the BIND QUERY command fails.
When multiple versions of the
package exist, and you specify * for the value of the VERSION column. DB2 uses package information from
the SYSIBM.SYSPACKSTMT catalog table that has the smallest value in
the VERSION column to modify the statement text. If other versions
of the package use different options, it is possible that for matching
to fail for statements from the other versions.
When the package
context is not specified in DSN_USERQUERY_TABLE, DB2 uses the applications default module to modify
the statement text. However, the statement text is not validated against
statements in a particular package.
- When you populate the QUERY_TEXT column in DSN_USERQUERY_TABLE,
select the parsed query text from the following locations:
- For static SQL statements, select the statement text from the
DBRM or from the SYSIBM.SYSPACKSTMT catalog table.
- For dynamic SQL statements, select the statement text from the
dynamic statement cache. For statements that are eligible for replacement
of literal values by the ampersand symbol (&), extract the statement
text after DB2 replaces literal
values.
It is possible to specify the text directly in an INSERT statement
(such as by copying from the source code for your application). However,
that approach reduces the likelihood of successful matching of statements
to the hint.
- Ensure that object names and SQL keywords in the statement
text are specified by uppercase characters, especially for dynamic
SQL statements.
