Start of change

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.

Begin program-specific programming interface information.
The actions that are taken when you call the SET_PLAN_HINT stored procedure depend on a mode value and the type of hint that you specify. You can specify the following modes: CREATE, DELETE, MODIFY or VALIDATE.

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

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

>>-CALL--------------------------------------------------------->

>--SET_PLAN_HINT--(--+-major_version-+--,--+-minor_version-+--,-->
                     '-NULL----------'     '-NULL----------'      

>--+-requested_locale-+--,--+-xml_input-+--,--+-xml_filter-+--,--xml_output--,--xml_message--)-><
   '-NULL-------------'     '-NULL------'     '-NULL-------'                                     

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>
    </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.

Start of changeDeployment 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.End of change

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
  1. 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
  2. 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
  1. The staging table DSN8BQRY.DSN_USERQUERY_TABLE is populated with the information that is specified in the DeploymentParameters and SQLStatement dictionaries.
  2. The following command is issued to check that the new hint does not already exist:
    BIND QUERY
EXPLAININPUTSCHEMA('DSN8BQRY')
LOOKUP(YES)
  3. If the new hint 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')
  • 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.

  2. The following command is issued to check that the new hint does not already exist:

    BIND QUERY EXPLAININPUTSCHEMA
('DSN8BQRY') LOOKUP(YES)

  3. If the new hint 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')
  • StatementLevelRules (any one of the following values)
    • REOPT
    • STARJOIN
    • MAX_PAR_DEGREE
    • DEGREE
    • SJTABLES
  • SQLStatement
    • SQLText
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
Statement-level optimization parameters The FREE QUERY command is issued for the QUERYID value that is specified in the DeploymentParameters dictionary.
  • DeploymentParameters
    • 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
  1. 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.

  2. 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.

  2. 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
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
All types
  1. 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
  2. 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.
  3. 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
Notes:
  1. 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.
    Related information:
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.
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.
Keys that correspond to the following PLAN_TABLE columns:
  • OPTHINT
  • PROGNAME1
  • APPLNAME
  • VERSION1
  • COLLID1
  • QUERYNO
  • BIND_TIME
  • QUERYID

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>
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>
  </dict>
</array>
Start of changeDeploymentParametersEnd of change
Start of changeDeployment 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.End of change
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.
The following example shows the format of the SQLStatement dictionary:
<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.
The following example shows the format of the ExistingAccessPlanIdentifier dictionary:
<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.

The following example shows the format of the StatementLevelRules dictionary:
<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.

The following example shows the format of this TableAccessRules array:
<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.
The following example shows the format of this JoinSequence array:
<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>
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 ,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:
<?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>
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>

End program-specific programming interface information.

End of change