SET_PLAN_HINT stored procedure
The SET_PLAN_HINT stored procedure validates, deploys, modifies, or deletes catalog tables rows that influence access path selection for SQL statements.
Environment
The SET_PLAN_HINT stored procedure runs in a WLM-established stored procedures address space.
The following staging tables, and related objects, which are created by job DSNTIJRT, must exist for use by the SET_PLAN_HINT stored procedure:
- DSN8BQRY.PLAN_TABLE
- DSN8BQRY.DSN_USER_QUERY_TABLE
- DSN8BQRY.DSN_PREDICAT_TABLE
- DSN8BQRY.DSN_PREDICATE_SELECTIVITY
Authorization
To execute the CALL statement, the owner of the package or plan that contains the CALL statement must have EXECUTE privilege on the SET_PLAN_HINT stored procedure.Syntax
The schema is SYSPROC.
Option descriptions
- major_version
- An input and output parameter of type INTEGER that indicates the major document version. On
input, this parameter indicates the major document version that you support for the XML documents
that are passed as parameters in the stored procedure (xml_input,
xml_output, and xml_message). The stored procedure processes
all XML documents in the specified version, or returns an error (-20457) if the version is invalid.
On output, this parameter specifies the highest major document version that is supported by the procedure. To determine the highest supported document version, specify NULL for this input parameter and all other required parameters. The only supported value is 1.
If the XML document in the xml_input parameter specifies the Document Type Major Version key, the value for that key must be equal to the value provided in the major_version parameter, or an error (+20458) is raised.
This parameter is used in conjunction with the minor_version parameter. Therefore, you must specify both parameters together. For example, you must specify both as either NULL, or non-NULL.
- minor_version
- An input and output parameter of type INTEGER that indicates the minor document version. On
input, this parameter specifies the minor document version that you support for the XML documents
that are passed as parameters for this stored procedure (xml_input,
xml_output, and xml_message). The stored procedure processes
all XML documents in the specified version, or returns an error (-20457) if the version is invalid.
On output, this parameter indicates the highest minor document version that is supported for the highest supported major version. To determine the highest supported document version, specify NULL for this input parameter and all other required parameters. The only minor document version that is supported is 0 (zero).
If the XML document in the xml_input parameter specifies the Document Type Minor Version key, the value for that key must be equal to the value provided in the minor_version parameter, or an error (+20458) is raised.
This parameter is used in conjunction with the major_version parameter. Therefore, you must specify both parameters together. For example, you must specify both as either NULL, or non-NULL.
- requested_locale
- An input parameter of type VARCHAR(33) that specifies a locale. If the specified language is
supported on the server, translated content is returned in the xml_output and
xml_message parameters. Otherwise, content is returned in the default language.
Only the language and possibly the territory information is used from the locale. The locale is not
used to format numbers or influence the document encoding. For example, key names are not
translated. The only translated portion of XML output and XML message documents are
Display Name, Display Unit, and Hint.
The value might be globalized where applicable. You should always compare the requested language to
the language that is used in the XML output document (see the Document Locale
entry in the XML output document).
Currently, the supported values for requested_locale are en_US and NULL. If you specify a null value, the result is the same as specifying en_US.
- xml_input
- An input parameter of type BLOB(2G) that specifies an XML input document of type Data Server
Hint Management Input in UTF-8 that represents the hint customization. The SET_PLAN_HINT stored
procedure does not support Complete mode.
The input document has the following format.
<?xml version="1.0" encoding="UTF-8"?> <plist version="1.0"> <dict> <key>Document Type Name</key><string>Data Server Hint Management Input</string> <key>Document Type Major Version</key><integer>1</integer> <key>Document Type Minor Version</key><integer>0</integer> <key>HINT_CUSTOMIZATION</key> <dict> hint-customization </dict> </dict> </plist>
In the preceding example, hint-customization is an XML fragment that describes the customized hint to be generated, validated, or deleted. The following example shows the overview of the format of the hint customization dictionary:
<key>HINT_CUSTOMIZATION</key> <dict> <key>DeploymentParameters</key> <dict> </dict> <key>StatementList</key> <array> <dict> <key>DeploymentParameters</key> <dict> </dict> <key>SQLStatement</key> <dict> </dict> <key>ExistingAccessPlanIdentifier</key> <dict> </dict> <key>StatementLevelRules</key> <dict> </dict> <key>PlanLevelRules</key> <dict> <key>TableAccessRules</key> <array></array> <key>JoinSequenceRules</key> <array></array> </dict> <key>PredicateSelectivityInstances</key> <array> </array> <key>ExistingPredicateSelectivityIdentifier</key> <dict> </dict> </dict> </array> </dict>
hint-customization is a dictionary that contains the following keys:- DeploymentParameters
- The type of hint, the action for the stored procedure, and the deployment parameters for the
hint.
Deployment parameters can also be specified at the statement level, within the StatementList array. Statement-level deployment parameters override the global deployment parameters for a particular statement.
The DeploymentParameters dictionary can contain the following keys:
- MODE
The MODE value is required. It specifies the hint processing mode and controls the actions taken by the SET_PLAN_HINT stored procedure:
CREATE
- Generates a hint in the deployment table. The following table describes the specific actions and
required input parameters for different types of
hints:
Table 1. Actions by hint type for the SET_PLAN_HINT stored procedure in CREATE mode Hint type Action Required Input Parameters PLAN_TABLE access path hints - A check is completed to ensure that the deployment PLAN_TABLE does not contain an existing hint
that has matching values for the following parameters that are specified in the DeploymentParameters dictionary:
- QUERYNO
- APPLNAME
- PROGNAME
- COLLID
- VERSION
- OPTHINT
- BIND_TIME
- An existing access path is copied from the source PLAN_TABLE to the deployment PLAN_TABLE, and
the information specified in the DeploymentParameters, TableAccessRules, and JoinSequenceRules
dictionaries are applied to the copied plan.
- The existing access path is identified by the parameters specified in the ExistingAccessPathIdentifier dictionary.
- The source PLAN_TABLE is schema-name.PLAN_TABLE, where schema-name is the PLAN_SCHEMA value that is specified in the ExistingAccessPlanIdentifier dictionary.
- The deployment PLAN_TABLE is the schema-name.PLAN_TABLE, where schema-name is the PLAN_SCHEMA value that is specified in the DeploymentParameters dictionary.
- DeploymentParameters
- QUERYNO
- APPLNAME
- PROGNAME
- COLLID
- VERSION
- OPTHINT
- PLAN_SCHEMA (schema of the deployment PLAN_TABLE )
- ExistingAccessPlanIdentifier
- QUERYNO
- APPLNAME
- PROGNAME
- COLLID
- VERSION
- OPTHINT
- PLAN_SCHEMA (schema of the source PLAN_TABLE)
Statement-level access paths - The staging table DSN8BQRY.DSN_USERQUERY_TABLE is populated with the information that is specified in the DeploymentParameters and SQLStatement dictionaries.
- The following command is issued to check that the new hint does not already
exist:
BIND QUERY EXPLAININPUTSCHEMA('DSN8BQRY') LOOKUP(YES)
- If a
hint of the same scope does not already exist, an existing access path is copied from the source
PLAN_TABLE to the staging table DSN8BQRY.PLAN_TABLE, the information specified in the
DeploymentParameters, TableAccessRules, and JoinSequenceRules dictionaries are applied to the copied
plan, and the following command is issued:
BIND QUERY EXPLAININPUTSCHEMA('DSN8BQRY')
- The existing access path is identified by the information that is specified in the ExistingAccessPlanIdentifier dictionary
- The source PLAN_TABLE is schema-name.PLAN_TABLE, where schema-name is the PLAN_SCHEMA value that is specified in the ExistingAccessPlanIdentifier.
- DeploymentParameters
- If HINT_SCOPE=1 (package-level access paths):
- PROGNAME
- COLLID
- VERSION
- HINT_TYPE ('INSTANCE-LEVEL')
- If HINT_SCOPE=1 (package-level access paths):
- ExistingAccessPlanIdentifier
- QUERYNO
- APPLNAME
- PROGNAME
- COLLID
- VERSION
- PLAN_SCHEMA (schema of the source PLAN_TABLE)
- SQLStatement
- SQLText
Statement-level optimization parameters 1 - The staging table DSN8BQRY.DSN_USERQUERY_TABLE is populated with the information specified in the DeploymentParameters, SQLStatement, and StatementLevelRules dictionaries.
The following command is issued to check that the new hint does not already exist:
BIND QUERY EXPLAININPUTSCHEMA ('DSN8BQRY') LOOKUP(YES)
If a hint of the same scope does not already exist, the following command is issued:
BIND QUERY EXPLAININPUTSCHEMA('DSN8BQRY')
- DeploymentParameters
- If HINT_SCOPE=1 (package-level access paths):
- PROGNAME
- COLLID
- VERSION
- HINT_TYPE ('INSTANCE-LEVEL')
- INSTANCE_LEVEL_HINT_TYPE ('OPTIMIZATION-PARAMETERS')
- If HINT_SCOPE=1 (package-level access paths):
- StatementLevelRules (any one of the following values)
- REOPT
- STARJOIN
- MAX_PAR_DEGREE
- DEGREE
- SJTABLES
- SQLStatement
- SQLText
Statement-level predicate selectivity overrides - The staging table DSN8BQRY.DSN_USERQUERY_TABLE is populated with the information that is specified in the DeploymentParameters and SQLStatement dictionaries.
-
The following command is issued to check whether the hint already exists:
BIND QUERY EXPLAININPUTSCHEMA('DSN8BQRY') LOOKUP(YES)
- If an existing hint of the same scope is found and predicate selectivity override for the query is not in effect, or an existing hint of the same scope is not found:
- If optimization parameters are in effect for the query, the staging table DSN8BQRY.DSN_USERQUERY_TABLE is further updated with the optimization parameters to ensure that the existing optimization parameters are not lost when the BIND QUERY is performed subsequently.
- The staging table DSN8BQRY.DSN_PREDICAT_TABLE is populated with the original predicate information from the source DSN_PREDICAT_TABLE.
- The source DSN_PREDICAT_TABLE is schema-name.DSN_PREDICAT_TABLE, where schema-name is the PLAN_SCHEMA value that is specified in the ExistingPredicateSelectivityIdentifier dictionary
- The original predicates of the query are identified by the parameters specified in the ExistingPredicateSelectivityIdentifier dictionary.
If the existing hint is an optimization parameter override, the values that define it are inserted in the staging table DSN8BQRY.DSN_USERQUERY_TABLE.
If the existing hint is not compatible with the new selectivity override, processing ends.
Steps continue in the next row.
- DeploymentParameters
- HINT_TYPE ('INSTANCE-LEVEL')
- INSTANCE_LEVEL_HINT_TYPE ('SELECTIVITY-OVERRIDE')
- If HINT_SCOPE=1 (package-level):
- PROGNAME
- COLLID
- VERSION
- SQLStatement
- SQLText
- ExistingPredicateSelectivityIdentifier
- QUERYNO
- PLAN_SCHEMA
- EXPLAIN_TIME
- PredicateSelectivityInstances
- QBLOCKNO
- Predicates:
- PREDNO
- SelectivityInstances
- INSTANCE
- SELECTIVITY
- WEIGHT
Statement-level predicate selectivity overrides (continues) - 4. The staging table DSN8BQRY.DSN_PREDICATE_SELECTIVITY is populated with predicate selectivity overrides that are specified in the DeploymentParameters dictionary and PredicateSelectivityInstances array. The value of the ASSUMPTION column is set to 'OVERRIDE'.
- 5. The following command is issued:
BIND QUERY EXPLAININPUTSCHEMA('DSN8BQRY')
- A check is completed to ensure that the deployment PLAN_TABLE does not contain an existing hint
that has matching values for the following parameters that are specified in the DeploymentParameters dictionary:
DELETE
- Deletes an existing hint from the deployment table. The following table describes the specific
actions and required input parameters for different types of
hints:
Table 2. Actions by hint type for the SET_PLAN_HINT stored procedure in DELETE mode Hint type Action Required Input Parameters PLAN_TABLE access path hints The specified access path hint is deleted from the deployment PLAN_TABLE. - The access path hint to be deleted is identified by the parameters specified in the DeploymentParameters dictionary.
- The deployment PLAN_TABLE is schema-name.PLAN_TABLE, where schema-name is the PLAN_SCHEMA value that is specified in the DeploymentParameters dictionary.
- DeploymentParameters
- QUERYNO
- APPLNAME
- PROGNAME
- COLLID
- VERSION
- OPTHINT
- PLAN_SCHEMA (schema of the deployment PLAN_TABLE)
Statement-level access paths The FREE QUERY command is issued for the QUERYID value that is specified in the DeploymentParameters dictionary. - DeploymentParameters
- QUERYID
- HINT_TYPE ('INSTANCE-LEVEL')
Statement-level optimization parameters 1 The FREE QUERY command is issued for the QUERYID value that is specified in the DeploymentParameters dictionary. - DeploymentParameters
- QUERYID
- HINT_TYPE ('INSTANCE-LEVEL')
Statement-level predicate selectivity overrides If optimization parameters are not in effect for the hint to be deleted, a FREE QUERY command is issued for the QUERYID value that is specified in the DeploymentParameters dictionary. Otherwise:
- The staging table DSN8BQRY.DSN_USERQUERY_TABLE is populated with the optimization parameters to ensure that we do not lose the existing optimization parameters when FREE QUERY is performed subsequently.
- The FREE QUERY command is issued for the QUERYID value that is specified in the DeploymentParameters dictionary.
- The following command is issued to restore the optimization
parameters:
BIND QUERY EXPLAININPUTSCHEMA('DSN8BQRY')
- DeploymentParameters
- HINT_TYPE ('INSTANCE-LEVEL')
- INSTANT_LEVEL_HINT_TYPE ('SELECTIVITY-OVERRIDE')
- QUERYID
MODIFY
- Modifies an existing hint in the deployment table. The following table describes the specific
actions and required input parameters for different types of
hints:
Table 3. Actions by hint type for the SET_PLAN_HINT stored procedure in MODIFY mode Hint type Action Required Input Parameters PLAN_TABLE access path hints The information specified in the TableAccessRules and JoinSequenceRules dictionaries are applied to an existing access path in the deployment PLAN_TABLE. - The existing access path is identified by the parameters specified in the DeploymentParameters dictionary
- The deployment PLAN_TABLE is schema-name.PLAN_TABLE, where schema-name is the PLAN_SCHEMA value that is specified in the DeploymentParameters dictionary
- DeploymentParameters
- QUERYNO
- APPLNAME
- PROGNAME
- COLLID
- VERSION
- OPTHINT
- PLAN_SCHEMA (schema of the deployment PLAN_TABLE)
Statement-level access paths - The following staging tables are populated based on an existing hint in the query catalog
tables, and on the information that is specified in the DeploymentParameters, SQLStatement,
TableAccessRules, and JoinSequenceRules dictionaries:
- DSN8BQRY.PLAN_TABLE
- DSN8BQRY.DSN_USERQUERY_TABLE
The existing hint in the query catalog tables is identified by the QUERYID value that is specified in the DeploymentParameters dictionary.
- The following command is
issued:
BIND QUERY EXPLAININPUTSCHEMA('DSN8BQRY')
- DeploymentParameters
- QUERYID
- If HINT_SCOPE=1 (package-level access path):
- PROGNAME
- COLLID
- VERSION
- HINT_TYPE ('INSTANCE-LEVEL')
- SQLStatement
- SQLText
Statement-level optimization parameters 1 - The staging table DSN8BQRY.DSN_USERQUERY_TABLE is populated based on an existing hint in the
query catalog tables and on the information specified in the DeploymentParameters, SQLStatement, and
StatementLevelRules dictionaries.
The existing hint in the query catalog tables is identified by the QUERYID value that is specified in the DeploymentParameters dictionary.
- The following command is
issued:
BIND QUERY EXPLAININPUTSCHEMA('DSN8BQRY')
- DeploymentParameters
- QUERYID
- If HINT_SCOPE=1 (package-level access path hints):
- PROGNAME
- COLLID
- VERSION
- HINT_TYPE ('INSTANCE-LEVEL')
- INSTANCE_LEVEL_HINT_TYPE ('OPTIMIZATION-PARAMETERS')
- StatementLevelRules (any one of the following values)
- REOPT
- STARJOIN
- MAX_PAR_DEGREE
- DEGREE
- SJTABLES
- SQLStatement
- SQLText
Statement-level predicate selectivity overrides - The following staging tables are populated based on an existing hint in the query catalog tables:
- DSN8QRY.DSN_USERQUERY_TABLE
- DSN8BQRY.DSN_PREDICAT_TABLE
- DSN8QRY.DSN_PREDICATE_SELECTIVITY
The existing hint in the catalog tables is identified by the QUERYID value that is specified in the DeploymentParameters dictionary.
- If optimization parameters are in effect for the query, the staging table DSN8BQRY.DSN_USERQUERY_TABLE is further updated with the optimization parameters.
- The input tables are modified again based on the values that are is specified in the
PredicateSelectivityInstances array:
- If SELECTIVITY_ACTION is 'UPDATE' rows are added or modified as necessary.
- If SELECTIVITY_ACTION is 'DELETE' rows are deleted as necessary.
- The following command is
issued:
BIND QUERY EXPLAININPUTSCHEMA('DSN8BQRY')
- DeploymentParameters
- HINT_TYPE ('INSTANCE -LEVEL')
- INSTANCE_LEVEL_HINT_TYPE ('SELECTIVITY-OVERRIDE')
- QUERYID
- PredicateSelectivityInstances
- QBLOCKNO
- Predicates:
- PREDNO
- SelectivityInstances:
- INSTANCE
- SELECTIVITY (if SELECTIVITY_ACTION='UPDATE')
- WEIGHT (if SELECTIVITY_ACTION='UPDATE')
- SELECTIVITY_ACTION (optional, must be 'UPDATE' or 'DELETE' if specified.)
VALIDATE
- Generates a hint and captures EXPLAIN information to validate the hint. The following table
shows the actions taken and the required input parameters when VALIDATE mode is
used.
Table 4. Actions by hint type for the SET_PLAN_HINT stored procedure in VALIDATE mode Hint type Action Required Input Parameters - PLAN_TABLE access path hints
- Statement-level access paths
- Statement-level optimization parameters
- A check is completed to ensure that no existing rows the deployment PLAN_TABLE table match the
following values that are specified in the DeploymentParameters and ExistingAccessPlanIdentifier dictionaries:
- QUERYNO
- APPLNAME
- PROGNAME
- COLLID
- VERSION
- OPTHINT
- BIND_TIME
- If the matching rows do not already exist, the access path identified by the information
specified in the ExistingAccessPlanIdentifier dictionary is copied from the source PLAN_TABLE to the
deployment PLAN_TABLE. The information specified in the DeploymentParameters, TableAccessRules and
JoinSequenceRules dictionaries are applied to the copied plan.
- The source PLAN_TABLE is schema-name.PLAN_TABLE, where schema-name is the PLAN_SCHEMA value that is specified in the ExistingAccessPlanIdentifier dictionary.
- The deployment PLAN_TABLE is the schema-name.PLAN_TABLE, where schema-name is the value of the CURRENT SQLID special register for the process.
- EXPLAIN information is captured for the resulting access path. The copied access path and the
EXPLAIN output are kept or deleted based on the SQLCODE value that is returned for the EXPLAIN operation:
- SQLCODE +000
- The new PLAN_TABLE rows are deleted.
- SQLCODE +394
- The new PLAN_TABLE rows are kept.
- SQLCODE +395
- If the value of the HINT_USED column is blank for all rows, the new PLAN_TABLE rows are deleted.
- ExistingAccessPlanIdentifier
- QUERYNO
- APPLNAME
- PROGNAME
- COLLID
- VERSION
- PLAN_SCHEMA (the schema of the source PLAN_TABLE)
- DeploymentParameters
- OPTHINT
Statement-level predicate selectivity overrides - DSN8QRY.DSN_USER_QUERY_TABLE is populated with the information that is specified in the DeploymentParameters dictionary, and a check is completed to determine whether incompatible catalog table rows already exist.
- If incompatible catalog table rows are not found, the following input tables are populated with
values from the DeploymentParameters and PredicateSelectivityInstances dictionaries, and the
SYSIBM.SYSQUERYPREDICATE catalog table:
- DSN8QRY.DSN_USER_QUERY_TABLE
- DSN8QRY.DSN_PREDICATE_SELECTIVITY
- DSN8QRY.DSN_PREDICAT_TABLE
- The following BIND QUERY command is issued to create temporary rows in the catalog
tables.1
BIND QUERY EXPLAININPUTSCHEMA('DSN8BQRY')
- EXPLAIN information is captured for the resulting access path. The copied access path and the
EXPLAIN output are kept or deleted based on the SQLCODE value that is returned for the EXPLAIN
operation.
If the SQLCODE +000 is returned by the explain operation, the value of the PLAN_TABLE.HINT_USED column is checked. If the value is 'SYSQUERYSEL n', the EXPLAIN records are kept.
The generated EXPLAIN records are identified by the QUERYNO and EXPLAIN_TIME values that are specified by in the ExistingPredicateSelectivityIdentifier dictionary.
- A FREE QUERY command is issued to remove the selectivity overrides from the catalog tables.
- DeploymentParameters:
- HINT_TYPE ('INSTANCE_LEVEL')
- INSTANCE_LEVEL_HINT_TYPE ('SELECTIVITY-OVERRIDE')
- If HINT_SCOPE=1 (package-level):
- PROGNAME
- COLLID
- VERSION
- SQLStatement:
- SQLText
- PredicateSelectivityInstances:
- QBLOCKNO
- Predicates:
- PREDNO
- SelectivityInstances:
- INSTANCE
- SELECTIVITY (required only if SELECTIVITY_ACTION is 'UPDATE')
- WEIGHT (required only if SELECTIVITY_ACTION is 'UPDATE')
- ExistingPredicateSelectivityIdentifier
- QUERYNO
- EXPLAIN_TIME
- PLAN_SCHEMA
Notes:- In VALIDATE processing for static statements and dynamic statements that are bound with the DYNAMICRULES(BIND) option, the COLLID, PROGNAME, and VERSION values for the package that contains the SET_PLAN_HINT stored procedure are used for the BIND QUERY operation. Therefore, the SQL processing options of DSNADM.DSNADMHS are used. Differences between the SQL processing options of the specified package and DSNADMHS might cause the BIND QUERY command to fail. When that happens, the validate operation fails. Option differences that might cause such failures include decimal point representation and others. For more information, see:
Notes:- If the SET_PLAN_HINT stored procedure is called to create, modify, or delete statement-level optimization parameters (HINT_TYPE=INSTANCE-LEVEL and INSTANCE_LEVEL_HINT_TYPE=OPTIMIZATION-PARAMETERS), any existing predicate selectivity overrides for the same statement are removed.
- HINT_TYPE
- The type of access path hint:
- TRADITIONAL
- PLAN_TABLE access path hints. This value is used by default.
- INSTANCE-LEVEL
- Statement-level hints, including:
- Access paths.
- Optimization parameters.
- Predicate selectivity overrides.
- HINT_SCOPE
- The scope of the statement-level hint:
- SYSTEM-LEVEL
- The hint applies at the system level. This value is used by default.
- PACKAGE-LEVEL
- The hint applies at the package level.
- INSTANCE_LEVEL_HINT_TYPE
- The action specified by statement-level rows:
- ACCESS-PATH
- Statement-level access paths. This value is used by default.
- OPTIMIZATION-PARAMETERS
- Statement-level optimization parameters.
- SELECTIVITY-OVERRIDE
- Statement-level predicate selectivity overrides.
- Keys that correspond to the following PLAN_TABLE columns:
-
For the meanings and accepted values for these keys, see: PLAN_TABLE.
- PLAN_SCHEMA
- The schema of the deployment PLAN_TABLE.
The following example shows the format of the DeploymentParameters dictionary for a PLAN_TABLE access path hint:
<key>DeploymentParameters</key> <dict> <key>MODE</key> <string>CREATE</string> <key>HINT_TYPE</key> <string>TRADITIONAL</string> <key>HINT_SCOPE</key> <string>PACKAGE-LEVEL</string> <key>APPLNAME</key> <string></string> <key>PROGNAME</key> <string>DSNTIAD</string> <key>COLLID</key> <string>DSNTIAB1</string> <key>VERSION</key> <string>*</string> <key>QUERYNO</key> <string>200</string> <key>PLAN_SCHEMA</key> <string>ADMF002</string> <key>OPTHINT</key> <string>HINT001</string> </dict>
The following example shows the format of the DeploymentParameters dictionary for a statement-level predicate selectivity override:
<key>DeploymentParameters</key> <dict> <key>MODE</key> <string>CREATE</string> <key>HINT_TYPE</key> <string>INSTANCE-LEVEL</string> <key>INSTANCE_LEVEL_HINT_TYPE</key> <string>SELECTIVITY-OVERRIDE</string> <key>HINT_SCOPE</key> <string>PACKAGE-LEVEL</string> <key>PROGNAME</key> <string>DSNTIAD</string> <key>COLLID</key> <string>DSNTIAB1</string> <key>VERSION</key> <string></string> </dict>
- StatementList
- A list of SQL statements and the hint definition that is associated with each. Only one SQL
statement is supported. Additional SQL statements are ignored. The following example shows the
format of the StatementList array:
<key>StatementList</key> <array> <dict> <key>DeploymentParameters</key> <dict> </dict> <key>SQLStatement</key> <dict> </dict> <key>ExistingAccessPlanIdentifier</key> <dict> </dict> <key>StatementLevelRules</key> <dict> </dict> <key>PlanLevelRules</key> <dict> <key>TableAccessRules</key> <array></array> <key>JoinSequenceRules</key> <array></array> </dict> <key>PredicateSelectivityInstances</key> <array> </array> <key>ExistingPredicateSelectivityIdentifier</key> <dict> </dict> </dict> </array>
- DeploymentParameters
- Deployment parameters can be specified globally, or specified at the statement level. Statement-level deployment parameters override the global parameters for a particular statement. For a description of the DeploymentParameter dictionary, see DeploymentParameters.
- SQLStatement
- The statement text and the default schema for resolving unqualified table names in the
statement.
The SQLStatement dictionary can contain the following keys:
- SQLText
- The text of the SQL statement.
- SCHEMA
- The default schema for resolving unqualified table names in the statement.
<key>SQLStatement</key> <dict> <key>SCHEMA</key> <string>USER001</string> <key>SQLText</key> <string> SELECT s_name, count(*) as numwait FROM supplier, lineitem l1, order, nation WHERE s_suppkey = l1.l_suppkey AND o_orderkey = l1.l_orderkey AND o_orderstatus = 'F' AND l1.l_receiptdate > l1.l_commitdate AND EXISTS( SELECT * FROM lineitem l2 WHERE l2.l_orderkey = l1.l_orderkey AND l2.l_suppkey <> l1.l_suppkey ) AND NOT EXISTS ( SELECT * FROM lineitem l3 WHERE l3.l_orderkey = l1.l_orderkey AND l3.l_suppkey <> l1.l_suppkey AND l3.l_receiptdate > l3.l_commitdate ) AND s_nationkey = n_nationkey AND n_name = 'USA' GROUP BY s_name ORDER BY numwait desc,s_name </string> </dict>
- ExistingAccessPlanIdentifier
- A list of parameters that identify an access path in a PLAN_TABLE.
ExistingAccessPlanIdentifier applies
only for hints that specify access
paths.
The ExistingAccessPlanIdentifier dictionary can contain the following keys:
- Keys that correspond to the following PLAN_TABLE columns
- OPTHINT
- PROGNAME
- APPLNAME
- VERSION
- COLLID
- QUERYNO
- BIND_TIME
For the meanings and accepted values for these keys, see: PLAN_TABLE.
- PLAN_SCHEMA
- The schema of the PLAN_TABLE that contains the original access path.
<key>ExistingAccessPlanIdentifier</key> <dict> <key>APPLNAME</key> <string></string> <key>PROGNAME</key> <string>DSNADMXX</string> <key>COLLID</key> <string>DSNADM</string> <key>VERSION</key> <string>*</string> <key>QUERYNO</key> <string>200</string> <key>PLAN_SCHEMA</key> <string>ADMF002</string> <key>OPTHINT</key> <string>HINT001</string> <key>BIND_TIME</key> <string>2012-11-05-07.10.41.700000</string> </dict>
- StatementLevelRules
- A list of parameters that identify optimization parameter hint properties.
StatementLevelRules applies only to
hints for optimization parameters.
The StatementLevelRules dictionary can contain the following keys:
- Keys that correspond to the following DSN_USERQUERY_TABLE columns:
- REOPT
- STARJOIN
- MAX_PAR_DEGREE
- DEGREE (for the DEF_CURR_DEGREE column)
- SJTABLES
For meanings and accepted values for these keys, see the column descriptions in DSN_USERQUERY_TABLE.
<key>StatementLevelRules</key> <dict> <key>REOPT</key> <dict> <key>VALUE</key> <string>1</string> </dict> <key>STARJOIN</key> <dict> <key>VALUE</key> <string>N</string> </dict> <key>MAX_PAR_DEGREE</key> <dict> <key>VALUE</key> <string>2</string> </dict> <key>DEGREE</key> <dict> <key>VALUE</key> <string>ONE</string> </dict> <key>SJTABLES</key> <dict> <key>VALUE</key> <string>2</string> </dict> </dict>
- PlanLevelRules
- A list of parameters that describe the customized access path that is specified by the hint,
including separate arrays for table access and join sequence information.
PlanLevelRules applies only to hints
that specify access paths. The following example shows the format of this PlanLevelRules dictionary:
<key>PlanLevelRules</key> <dict> <key>TableAccessRules</key> <array></array> <key>JoinSequenceRules</key> <array></array> </dict>
- TableAccessRules
- A list of rules that are related to table access and that describe data access methods, such as
table space scans and index scans, for example.
Each table access rule is represented by a TableReferenceIdentifier and its corresponding Settings.
- TableReferenceIdentifier
- A list of properties that identify the table reference. The properties correspond to PLAN_TABLE columns:
- QBLOCKNO
- TABNO
- TABLE_CREATOR (for the CREATOR column)
- TABLE_NAME (for the TNAME column)
- CORRELATION_NAME
For the meanings and accepted values for these properties, see: PLAN_TABLE.
- Settings
- A list of access properties. The properties correspond to PLAN_TABLE columns:
- ACCESS_TYPE (for the ACCESSTYPE column)
- ACCESS_CREATOR (for the ACCESCREATOR column)
- ACCESS_NAME (for the ACCESSNAME column)
- PREFETCH
- PAGE_RANGE
- SORTN_JOIN
- SORTC_JOIN
- PARALLELISM_MODE
- ACCESS_DEGREE
- JOIN_DEGREE
- ACCESS_PGROUP_ID
- JOIN_PGROUP_ID
- PRIMARY_ACCESSTYPE
- METHOD
For the meanings and accepted values for these properties, see: PLAN_TABLE.
<key>TableAccessRules</key> <array> <dict> <key>TableReferenceIdentifier</key> <array> <dict> <key>NAME</key> <string>QBLOCKNO</string> <key>VALUE</key> <string>1</string> </dict> ... </array> <key>Settings</key> <array> <dict> <key>NAME</key> <string>ACCESS_TYPE</string> <key>VALUE</key> <string>IXSCAN</string> </dict> </array> </dict> </array>
- JoinSequenceRules
- A list of join sequence customization rules. Each join sequence rule is represented by the
Settings and Roots (root nodes) for the join sequence. The join sequence rules correspond to the
join sequence and join methods, such as merge join and hybrid join, for example.
- Settings
- A property for the join sequence rule that correspond to the PLAN_TABLE column of the same name: QBLOCKNO
- Roots
- A list of root nodes in the join sequence, where each root node is identified by the node type.
Depending on the node type, a TABLE_REFERENCE_NODE holds the properties to identify the table
reference, while an OPERATOR_NODE holds the properties of the operator.
- TYPE
- The node type:
- TABLE_REFERENCE_NODE
- OPERATOR NODE
- TableReferenceIdentifier
- A list of properties to identify the table reference if node type is TABLE_REFERENCE_NODE. These
properties correspond to PLAN_TABLE columns:
- QBLOCKNO
- TABNO
- TABLE_CREATOR (for the CREATOR column)
- TABLE_NAME (for the TNAME column)
- CORRELATION_NAME
For the meanings and accepted values for these properties, see: PLAN_TABLE.
- Settings
- A list of properties of this operator if node type is OPERATOR_NODE. These properties correspond
to PLAN_TABLE columns:
- JOIN_METHOD (for the METHOD column)
- SORTN_JOIN
- SORTC_JOIN
- JOIN_DEGREE
- JOIN_PGROUP_ID
For the meanings and accepted values for these properties, see: PLAN_TABLE.
- Left
- A description of a left-side child node. The data structure is similar to the root node.
- Right
- A description of a right-side child node. The data structure is similar to the root node.
<key>JoinSequenceRules</key> <array> <dict> <key>Settings</key> <array> <dict> <key>NAME</key> <string>QBLOCKNO</string> <key>VALUE</key> <string>1</string> </dict> </array> <key>Roots</key> <array> <dict> <key>TYPE</key> <string>OPERATOR_NODE</string> <key>Settings</key> <array> <dict> <key>NAME</key> <string>JOIN_METHOD</string> <key>VALUE</key> <string>NLJOIN</string> </dict> </array> <key>Left</key> <dict> <key>TYPE</key> <string>OPERATOR_NODE</string> <key>Settings</key> <array> <dict> <key>NAME</key> <string>JOIN_METHOD</string> <key>VALUE</key> <string>SMJOIN</string> </dict> </array> <key>Left</key> <dict> <key>TYPE</key> <string>OPERATOR_NODE</string> <key>Settings</key> <array> <dict> <key>NAME</key> <string>JOIN_METHOD</string> <key>VALUE</key> <string>NLJOIN</string> </dict> </array> <key>Left</key> <dict> <key>TYPE</key> <string>TABLE_REFERENCE_NODE</string> <key>TableReferenceIdentifier</key> <array> <dict> <key>NAME</key> <string>TABLE_NAME</string> <key>VALUE</key> <string>NATION</string> </dict> </array> </dict> <key>Right</key> <dict> <key>TYPE</key> <string>TABLE_REFERENCE_NODE</string> <key>TableReferenceIdentifier</key> <array> <dict> <key>NAME</key> <string>TABLE_NAME</string> <key>VALUE</key> <string>SUPPLIER</string> </dict> </array> </dict> </dict> <key>Right</key> <dict> <key>TYPE</key> <string>TABLE_REFERENCE_NODE</string> <key>TableReferenceIdentifier</key> <array> <dict> <key>NAME</key> <string>TABLE_NAME</string> <key>VALUE</key> <string>LINEITEM</string> </dict> </array> </dict> </dict> <key>Right</key> <dict> <key>TYPE</key> <string>TABLE_REFERENCE_NODE</string> <key>TableReferenceIdentifier</key> <array> <dict> <key>NAME</key> <string>TABLE_NAME</string> <key>VALUE</key> <string>ORDER</string> </dict> </array> </dict> </dict> </array> </dict> </array>
- PredicateSelectivityInstances
- A list of predicates and their selectivity instances.
PredicateSelectivityInstances applies
only to hints that specify selectivity overrides.
- QBLOCKNO
- The identifier of the query block that contains the predicate.
- Predicates
- SELECTIVITY_ACTION
- The processing mode for the selectivity instance. One of the following values:
- UPDATE (valid when MODE is CREATE, MODIFY, or VALIDATE.) This is the default value.
- DELETE (valid when MODE is MODIFY, or VALIDATE for an existing hint.)
- INSTANCE
- The identifier of the selectivity instance. A positive integer greater than or equal to 1.
- SELECTIVITY
- Percentage of rows in the table that satisfy the predicate as a value 0–1. This value is mandatory when SELECTIVITY_ACTION='UPDATE'.
- WEIGHT
- Percentage of executions in which the selectivity instance applies, as a value 0–1. This value is mandatory when SELECTIVITY_ACTION='UPDATE'.
The following example shows the format of the PredicateSelectivityInstances array:
<key>PredicateSelectivityInstances</key> <array> <dict> <key>QBLOCKNO</key><string>1</string> <key>Predicates</key> <array> <dict> <key>PREDNO</key><string>2</string> <key>SelectivityInstances</key> <array> <dict> <key>SELECTIVITY_ACTION</key><string>UPDATE</string> <key>INSTANCE</key><string>1</string> <key>SELECTIVITY</key><string>0.1111</string> <key>WEIGHT</key><string>0.11111</string> </dict> <dict> <key>INSTANCE</key><string>2</string> <key>SELECTIVITY</key><string>0.1222</string> <key>WEIGHT</key><string>0.12222</string> </dict> </array> </dict> <dict> <key>PREDNO</key><string>3</string> <key>SelectivityInstances</key> <array> <dict> <key>INSTANCE</key><string>3</string> <key>SELECTIVITY</key><string>0.1333</string> <key>WEIGHT</key><string>0.13333</string> </dict> <dict> <key>INSTANCE</key><string>4</string> <key>SELECTIVITY</key><string>0.1444</string> <key>WEIGHT</key><string>0.14444</string> </dict> </array> </dict> </array> </dict> </array>
- ExistingPredicateSelectivityIdentifier
- Identifies the EXPLAIN tables rows that are copied to input tables for the BIND QUERY operation.
ExistingPredicateSelectivityIdentifier
applies only to hints that override selectivities.
- PLAN_SCHEMA
- The schema of the following EXPLAIN tables: PLAN_TABLE, DSN_PREDICAT_TABLE, and DSN_PREDICATE_SELECTIVITY tables.
- EXPLAIN_TIME
- The time that the row was inserted by EXPLAIN.
- QUERYNO
- The identifier of the statement.
The following example shows the format of the ExistingPredicateSelectivityIdentifier dictionary:
<key>ExistingPredicateSelectivityIdentifier</key> <dict> <key>QUERYNO</key><string>100</string> <key>EXPLAIN_TIME</key><string>2013-03-20-14.29.55.660000</string> <key>PLAN_SCHEMA</key><string>ADMF001</string> </dict>
- xml_filter
- An input parameter of type BLOB(4K). Specifies a valid XPath query string to retrieve a single value from an XML output document.
- xml_output
- An output parameter of type BLOB(2G) When the mode is VALIDATE for hints other than selectivity overrides,xml_output has the following format:
<?xml version="1.0" encoding="UTF-8"?> <plist version="1.0"> <dict> <key>Document Type Name</key><string>Data Server Hint Management Output</string> <key>Document Type Major Version</key><integer>1</integer> <key>Document Type Minor Version</key><integer>0</integer> <key>Data Server Product Name</key><string>DSN</string> <key>Data Server Product Version</key><string>10.1.5</string> <key>Data Server Major Version</key><integer>10</integer> <key>Data Server Minor Version</key><integer>1</integer> <key>Data Server Platform</key><string>z/OS</string> <key>Document Locale</key><string>en_US</string> <key>Hint Validation</key> <dict> <key>Display Name</key><string>Hint Validation</string> <key>EXPLAIN SQLCODE</key> <dict> <key>Display Name</key><string>EXPLAIN SQLCODE</string> <key>Value</key><string>394</string> <key>Hint</key><string/> </dict> <key>Additional EXPLAIN Information</key> <dict> <key>Display Name</key><string>Additional EXPLAIN Information</string> <key>Value</key> <string>DSNT404I SQLCODE = 394, WARNING: USER SPECIFIED OPTIMIZATION HINTS USED DURING ACCESS PATH SELECTION DSNT418I SQLSTATE = 01629 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXOPCO SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = 20 0 25 1264473616 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'00000014' X'00000000' X'00000019' X'4B5E5610' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION</string> <key>Hint</key><string/> </dict> <key>Hint</key><string></string> </dict> </dict> </plist>
When the mode is CREATE, MODIFY, or DELETE for PLAN_TABLE access path hints, xml_output has the following format:<?xml version="1.0" encoding="UTF-8"?> <plist version="1.0"> <dict> <key>Document Type Name</key><string>Data Server Hint Management Output</string> <key>Document Type Major Version</key><integer>1</integer> <key>Document Type Minor Version</key><integer>0</integer> <key>Data Server Product Name</key><string>DSN</string> <key>Data Server Product Version</key><string>10.1.5</string> <key>Data Server Major Version</key><integer>10</integer> <key>Data Server Minor Version</key><integer>1</integer> <key>Data Server Platform</key><string>z/OS</string> <key>Document Locale</key><string>en_US</string> <key>Hint Deployment</key> <dict> <key>Display Name</key><string>Hint Deployment</string> <key>SQLCODE</key> <dict> <key>Display Name</key><string>SQLCODE</string> <key>Value</key><string>0</string> <key>Hint</key><string/> </dict> <key>Hint</key><string></string> </dict> </dict> </plist>
When the mode is CREATE or MODIFY for statement-level hints, xml_output has the following format:<?xml version="1.0" encoding="UTF-8"?> <plist version="1.0"> <dict> <key>Document Type Name</key><string>Data Server Hint Management Output</string> <key>Document Type Major Version</key><integer>1</integer> <key>Document Type Minor Version</key><integer>0</integer> <key>Data Server Product Name</key><string>DSN</string> <key>Data Server Product Version</key><string>10.1.5</string> <key>Data Server Major Version</key><integer>10</integer> <key>Data Server Minor Version</key><integer>1</integer> <key>Data Server Platform</key><string>z/OS</string> <key>Document Locale</key><string>en_US</string> <key>Hint Deployment</key> <dict> <key>Display Name</key><string>Hint Deployment</string> <key>BIND QUERY Message</key> <dict> <key>Display Name</key><string>BIND QUERY Message</string> <key>Value</key> <string>DSNT280I @ BIND QUERY FOR QUERYNO = 8888 SUCCESSFUL DSNT289I @ BIND QUERY COMMAND INVOKED BY THE DSNE PROCESSOR. UNDER THIS ENVIRONMENT, THE COMMAND CAN ONLY PROCESS THE FIRST APPLICABLE QUERY. ALL OTHER QUERIES ARE NOT PROCESSED. </string> <key>Hint</key><string/> </dict> <key>QUERYID</key> <dict> <key>Display Name</key><string>QUERYID</string> <key>Value</key> <string>999</string> <key>Hint</key><string/> </dict> <key>Hint</key><string></string> </dict> </dict> </plist>
When the mode is DELETE for statement-level hints, xml_output has the following format. The format is different for statement-level selectivity overrides when preexisting optimization parameters are specified.<?xml version="1.0" encoding="UTF-8"?> <plist version="1.0"> <dict> <key>Document Type Name</key><string>Data Server Hint Management Output</string> <key>Document Type Major Version</key><integer>1</integer> <key>Document Type Minor Version</key><integer>0</integer> <key>Data Server Product Name</key><string>DSN</string> <key>Data Server Product Version</key><string>10.1.5</string> <key>Data Server Major Version</key><integer>10</integer> <key>Data Server Minor Version</key><integer>1</integer> <key>Data Server Platform</key><string>z/OS</string> <key>Document Locale</key><string>en_US</string> <key>Hint Deployment</key> <dict> <key>Display Name</key><string>Hint Deployment</string> <key>FREE QUERY Message</key> <dict> <key>Display Name</key><string>FREE QUERY Message</string> <key>Value</key> <string>DSNT280I @ FREE QUERY FOR QUERYID = 6 SUCCESSFUL DSNT290I @ FREE QUERY COMMAND COMPLETED</string> <key>Hint</key><string/> </dict> <key>Hint</key><string/> </dict> </dict> </plist>
When the mode is DELETE for statement-level predicate selectivity overrides and preexisting optimization parameters are specified, xml_output has the following format:<?xml version="1.0" encoding="UTF-8"?> <plist version="1.0"> <dict> <key>Document Type Name</key><string>Data Server Hint Management Output</string> <key>Document Type Major Version</key><integer>1</integer> <key>Document Type Minor Version</key><integer>0</integer> <key>Data Server Product Name</key><string>DSN</string> <key>Data Server Product Version</key><string>11.1.5</string> <key>Data Server Major Version</key><integer>11</integer> <key>Data Server Minor Version</key><integer>1</integer> <key>Data Server Platform</key><string>z/OS</string> <key>Document Locale</key><string>en_US</string> <key>Hint Deployment</key> <dict> <key>Display Name</key><string>Hint Deployment</string> <key>FREE QUERY Message</key> <dict> <key>Display Name</key><string>FREE QUERY Message</string> <key>Value</key> <string>DSNT280I @ FREE QUERY FOR QUERYID = n SUCCESSFUL DSNT290I @ FREE QUERY COMMAND COMPLETED</string> <key>Hint</key><string/> </dict> <key>BIND QUERY</key> <dict> <key>Display Name</key><string>BIND QUERY</string> <key>Message</key> <dict> <key>Display Name</key><string>Message</string> <key>Value</key> <string>DSNT280I @ BIND QUERY FOR QUERYNO = n SUCCESSFUL DSNT289I @ BIND QUERY COMMAND INVOKED BY THE DSNE PROCESSOR. UNDER THIS ENVIRONMENT, THE COMMAND CAN ONLY PROCESS THE FIRST APPLICABLE QUERY. ALL OTHER QUERIES ARE NOT PROCESSED. </string> <key>Hint</key><string/> </dict> <key>QUERYID</key> <dict> <key>Display Name</key><string>QUERYID</string> <key>Value</key><string>n</string> <key>Hint</key><string/> </dict> <key>Hint</key><string/> </dict> <key>Hint</key><string/> </dict> </dict> </plist>
- xml_message
- An output parameter of type BLOB(2G). The output is an XML document with the following
format:
<?xml version="1.0" encoding="UTF-8"?> <plist version="1.0"> <dict> <key>Document Type Name</key> <string>Data Server Message</string> <key>Document Type Major Version</key><integer>1</integer> <key>Document Type Minor Version</key><integer>0</integer> <key>Data Server Product Name</key><string>DSN</string> <key>Data Server Product Version</key><string>10.1.5</string> <key>Data Server Major Version</key><integer>10</integer> <key>Data Server Minor Version</key><integer>1</integer> <key>Data Server Platform</key><string>z/OS</string> <key>Document Locale</key><string>en_US</string> <key>Short Message Text</key> <dict> <key>Display Name</key><string>Short Message Text</string> <key>Value</key> <string>DSNA618I DSNADMHS SQL ERROR DURING SQL STATEMENT CALL, PROCEDURE=SYSPROC.ADMIN_COMMAND_DSN DSNT408I SQLCODE = -471, ERROR: INVOCATION OF FUNCTION OR PROCEDURE SYSPROC.ADMIN_COMMAND_DSN FAILED DUE TO REASON 00E79001 DSNT418I SQLSTATE = 55023 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNX9GPL SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -30 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFFE2' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION</string> <key>Hint</key><string></string> </dict> </dict> </plist>