BIND QUERY (DSN)

Start of change The DSN subcommand BIND QUERY reads the statement text, default schema, and a set of bind options from every row of DSN_USERQUERY_TABLE, and information from correlated EXPLAIN table rows. When LOOKUP(NO) is in effect, DB2® inserts the pertinent data into certain catalog tables. End of change

The data inserted in the catalog tables creates one or more of the following methods for influencing access path selection for matching SQL statements:

Specifying statement-level optimization parameters.
Specifying statement level access paths.

Environment

You can use BIND QUERY from DB2I, or from a DSN session under TSO that runs in either the foreground or background. You can also use the SYSPROC.ADMIN_COMMAND_DSN to submit this subcommand from a remote requester.

Data sharing scope: Group

Authorization

To issue this command, you must use a privilege set of the process that includes one of the following authorities:

SQLADM authority
System DBADM authority
SYSOPR authority
SYSCTRL authority
SYSADM authority

Syntax

>>-BIND QUERY--+-------------------+---------------------------->
               |         .-NO--.   |   
               '-LOOKUP(-+-YES-+-)-'   

>--+-----------------------------------+-----------------------><
   '-EXPLAININPUTSCHEMA('schema-name')-'

Option description

LOOKUP

Specifies whether to check catalog tables for existing rows that match rows in DSN_USERQUERY_TABLE. LOOKUP(NO) is the default value. When LOOKUP(YES) is in effect, rows are not inserted or modified in the catalog tables. When matching rows are found in the SYSIBM.SYSQUERY and SYSIBM.SYSQUERYPLAN catalog tables, DB2 inserts the value of the SYSQUERYPLAN.QUERYID column into the DSN_USERQUERY_TABLE.QUERYID column of the matching row.

(NO)

DB2 reads the information from the DSN_USERQUERY_TABLE and certain EXPLAIN tables and inserts the data into certain catalog tables to influence access path selection for matching statements. NO is the default value.

Depending on the values that are specified in DSN_USERQUERY_TABLE, rows might be read from the following additional input tables:

schema.PLAN_TABLE

Start of change Where schema is the authorization ID of the issuer of the BIND QUERY command. End of change

Depending on the values that are specified in the input tables, data might be inserted in the following catalog tables:

SYSIBM.SYSQUERY
SYSIBM.SYSQUERYPLAN
SYSIBM.SYSQUERYOPTS

The catalog table rows influence access path selection for the following methods:

Specifying statement-level optimization parameters.
Specifying statement level access paths.

DB2 issues the following messages to indicate the results of BIND QUERY operation:

A DSNT280I message for each DSN_USERQUERY_TABLE row that is inserted successfully into the catalog tables.
A DSNT281I message for each DSN_USERQUERY_TABLE row that is not successfully inserted into the catalog tables.
A single DSNT290I message if some rows were inserted into catalog tables successfully or a DSNT291I message if no rows were inserted successfully.

(YES)

DB2 reads information from the DSN_USERQUERY_TABLE and looks for the matching rows in the following catalog tables:

SYSIBM.SYSQUERY
SYSIBM.SYSQUERYPLAN
SYSIBM.SYSQUERYOPTS

The catalog tables are not modified or populated with new values. When matching rows exist in the catalog tables, DB2 inserts the value of the SYSQUERY.QUERYID column into the DSN_USERQUERY_TABLE.QUERYID column of the matching row.

Start of change New rows are not inserted into the catalog tables when LOOKUP(YES) is specified. Instead, DB2 issues messages to indicate whether existing rows where identified: End of change

A DSNT280I message for each row in the DSN_USERQUERY_TABLE that has a valid matching row in the SYSIBM.SYSQUERY table.
A DSNT281I message for each row in DSN_USERQUERY_TABLE that does not have valid matching rows in the SYSIBM.SYSQUERY.
A single DSNT290I message if some matching rows were found or a DSNT291I message if no matches were found.

EXPLAININPUTSCHEMA

Specifies the schema name of the EXPLAIN tables that are to be used for input during BIND QUERY processing. The schema name must be enclosed in single quotation marks (').

EXPLAININPUTSCHEMA enables you to create separate EXPLAIN tables to be used only as input to the BIND QUERY command. By creating separate input tables, you can eliminate the need to remove unneeded rows that might interfere with BIND QUERY process from the EXPLAIN output tables. When the EXPLAININPUTSCHEMA option is not specified, DB2 uses the tables that are qualified by the authorization ID of the user that issues the BIND QUERY command.

Usage notes

Eligible SQL statements: BIND QUERY only processes the following types of the SQL statements.

SELECT
INSERT with fullselect
Searched UPDATE
Searched DELETE
MERGE
TRUNCATE

If you enter any SQL statement types other than those on the list above, DB2 issues a message.

DECP options: The following options must be the same when the BIND QUERY command is issued as when packages were bound, for static SQL statements, or when the statements were prepared, for dynamic SQL statements:

CCSID
Decimal point
String delimiter

Cached dynamic SQL statements: During BIND QUERY time, after DB2 processes a query successfully, the query will be removed from the dynamic statement cache when the following conditions are met.

The query is a dynamic SQL statement
The access path to be specified exists in the PLAN_TABLE
The query was prepared and saved in the dynamic statement cache before the BIND QUERY time and value of the OPTHINTS subsystem parameter is set to 'YES'

Examples

Example: Specifying statement-level optimization parameters

Suppose that you created an instance of DSN_USERQUERY_TABLE under your schema, and populated it with rows that specify optimization parameters. Use the following subcommand to create corresponding rows in the SYSIBM.SYSQUERY and SYSIBM.SYSQUERYOPTS catalog tables.

BIND QUERY