BIND QUERY subcommand (DSN)

Environment for BIND QUERY

You can use BIND QUERY 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.

The BIND QUERY command succeeds only when the value of the OPTHINTS subsystem parameter is set to YES.

Authorization for BIND QUERY

Option descriptions for BIND QUERY

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
• schema.DSN_PREDICAT_TABLE
• schema.DSN_PREDICATE_SELECTIVITY

Where schema is value specified in the EXPLAININPUTSCHEMA option, or the authorization ID of the issuer of the BIND QUERY command.

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
• SYSIBM.SYSQUERYPREDICATE
• SYSIBM.SYSQUERYSEL

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

• Overriding the selectivities of predicates.
• 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.

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:

• 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 for BIND QUERY

Eligible SQL statements

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

• SELECT
• INSERT
• Searched UPDATE
• UPDATE WHERE CURRENT OF
• Searched DELETE
• DELETE WHERE CURRENT OF
• 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'

Trace information for data sharing members

When this command with group scope is issued in a Db2 data sharing member, it also runs on all other active members. IFICID 090 trace records for other group members can show that the same command was issued by the SYSOPR authorization ID from the 016.TLPKN5F correlation ID, in addition to the trace records from the member where the original command was issued. See Command scope in Db2 data sharing.