When you specify the -groupSQLBySpecialRegisters option, the Configure utility groups SQL statements in the pureQueryXML file into statement sets based on the special register information recorded when the SQL statements were captured. The recorded special register settings are settings that might affect the behavior of the SQL statement.
When you specify the -optionsFileForBind option, the Configure utility generates a StaticBinder options file, and the utility attempts to generate a set of bind options for statement sets that is based on the special register settings that are recorded when the SQL statements were captured. If the statements in the statement set are grouped according to a single set of special register values, the Configure utility generates a single set of bind options. If the statement set is grouped based on multiple sets of special register values, the utility creates a comment containing the a set of bind options for each special register set. The utility also adds comments that contain information and warnings about the statement sets, SQL statements, and special register information in the pureQueryXML file.
The bind options might be needed when you bind packages to a DB2 database to run SQL statements statically. The bind options might be required so that when an SQL statement is run statically, the behavior is similar to when it is run dynamically.
Before you use the generated bind options with the StaticBinder utility, review the bind options. If necessary, modify the bind options for your environment so that the SQL statements have the correct behavior when they are run statically.
In some cases, you might not be able to specify a set of bind options for a statement set. You can specify that the StaticBinder utility not perform a bind operation. In the pureQueryXML file, change the value of the XML attribute isBindable to false for the statement set and configure the pureQueryXML file again. For information about the isBindable attribute and changing the attribute, see The XML attribute isBindable in the pureQueryXML file.The file that is generated by Configure utility is in the pureQuery StaticBinder utility options file format. The options file that is generated by the Configure utility consists of comments, the defaultOptions entry, and entries for binding statement sets.
A comment contains warnings and information and can also contain a set of bind options. For information about the information in the comments, see Comments generated by the Configure utility.
defaultOptions =
The Configure utility adds bind options to the defaultOptions entry only if the -groupSQLBySpecialRegisters was not specified and none of the statement sets were grouped based on special register information.
The Configure utility generates bind options and might add a comment with additional information. You can add other options for items such as the connection URL or isolation level. For information about the defaultOptions entry, see the link to "Syntax for default options in an options file for the StaticBinder utility for client optimization" at the end of this topic.
defaultOptions = -bindOptions "QUALIFIER(SCHM4) FUNCPATH(SYSIBM,SYSFUN,SYSPROC,SYSADM) ROUNDING(HALFEVEN) DEC(15)" # WARNING: [specialRegValueId(C:\myDir\capture.pdqxml:4) minimumDivideScale(8) SQLID(ADMF003)]
defaultOptions = -bindOptions "QUALIFIER(SCHM4) FUNCPATH(SYSIBM,SYSFUN,SYSPROC,SYSADM) ROUNDING(HALFEVEN) DEC(15)"
# WARNING: [specialRegValueId(C:\myDir\capture.pdqxml:4) minimumDivideScale(8) SQLID(ADMF003)]
defaultOptions = # WARNING: SQLGroupedBySpecialRegisters(NOT_GROUPED)
The following format specifies bind options for a statement set:
pureQueryXML-file:base-package-name=bind-options
The entry that is generated by the Configure utility contains only the statement set information and bind options. You can add other options. The entry can contain a comment with additional information and warnings for the statement set.
For information about an entry to bind a statement set, see the link to "Syntax for entries in an options file for the StaticBinder utility for client optimization" at the end of this topic.
The following example is an entry for binding a statement set. The entry is on multiple lines for readability:
C:\myDir\capture.pdqxml:MYRPN1K = -bindOptions "QUALIFIER(SCHM9) FUNCPATH(SYSIBM,SYSFUN,SYSPROC,SYSADM) ROUNDING(HALFEVEN) DEC(15)" # WARNING: [specialRegValueId(10) minimumDivideScale(3) UNTRACKED_SET] INFO: configureStatus(FINAL) SQLGroupedByStrings(*NO_MATCH_FOUND*)
C:\myDir\capture.pdqxml:MYRPN1K = -bindOptions "QUALIFIER(SCHM9) FUNCPATH(SYSIBM,SYSFUN,SYSPROC,SYSADM) ROUNDING(HALFEVEN) DEC(15)"
# WARNING: [specialRegValueId(10) minimumDivideScale(3) UNTRACKED_SET] INFO: configureStatus(FINAL) SQLGroupedByStrings(*NO_MATCH_FOUND*)
MYRPN1K = -bindOptions "QUALIFIER(SCHM9) FUNCPATH(SYSIBM,SYSFUN,SYSPROC,SYSADM) ROUNDING(HALFEVEN) DEC(15)" # WARNING: [specialRegValueId(10) minimumDivideScale(3) UNTRACKED_SET] INFO: configureStatus(FINAL)
# WARNING: [specialRegValueId(10) minimumDivideScale(3) UNTRACKED_SET] INFO: configureStatus(FINAL)
INFO: configureStatus(FINAL)
The Configure utility generates bind options for a statement set if the -groupSQLBySpecialRegisters was specified, or if the statement set was grouped based on special register information. The Configure utility generates bind options for a statement set if a single set of special register values is recorded with the statement set. If more than one set of special register values is recorded for a statement set, the Configure utility creates comments that contain the bind options that are based on the special register values sets that were recorded with the SQL statements in the statement set.
# -bindOptions "QUALIFIER(SCHM13) FUNCPATH(SYSIBM,SYSFUN,SYSPROC,SYSADM) ROUNDING(HALFEVEN) DEC(15)" # WARNING: [specialRegValueId(3) UNTRACKED_SET]
For the comment, the bind options are based on the special register values set with ID 3 in the pureQueryXML file C:\myDir\capture.pdqxml. For the special register values set 3, pureQuery Runtime also detected special register changes for special registers that pureQuery tracks but for which values are not recorded.
In the Staticbinder options file that is created by the pureQuery Configure utility, the utility adds comments after the bind options. The comments contain tokens that represent informational or warning messages. The tokens that are preceded by WARNING: are reasons the SQL statements in the statement sets might not have the correct behavior when they are run statically. The tokens proceeded by INFO: are informational.
When the comments that are appended to a statement contain the token isBindable(FALSE), only the INFO: label is used. The token is listed for a statement set when the value of the XML attribute isBindable is false. If the value is false, the StaticBinder utility ignores the statement set when performing the bind process. If the statement set is not bound, no warnings are necessary.
If the value of the XML attribute isBindable is false for a statement set, the Configure utility does not generate options -bindOptions and -configureWarning. However, all tokens appear in the comments for the statement.
For information about the isBindable attribute, see The XML attribute isBindable in the pureQueryXML file
The following tokens provide information about statement sets:
If you specify the Configure utility option -setPostStatusOfAllPkgs with the value FINAL, the token is not added for every statement set. The token is added as a comment only when the value of the configureStatus attribute is FINAL for the statement set in the input pureQueryXML file. The option -setPostStatusOfAllPkgs sets the configureStatus attribute of all statement sets to FINAL after configuration.
When you change the pureQueryXML file, ensure that your changes do not unintentionally affect the statement sets that have the value of the configureStatus attribute set to FINAL.
If the Configure utility generates warning tokens for the statement set, you can either ignore the warnings or attempt to fix the warnings. You ignore the warnings if you want to keep the existing packages that you bound on the database. To attempt to fix the warnings, see the information for the specific warning token. Attempting to fix a warning usually requires configuring the statement set. After a statement set is configured, the packages associated with the statement set must be rebound by the StaticBinder utility before the SQL statements in the statement set can be run statically.
The Configure utility also adds the -configureWarning option with the value INCREMENTAL_SPECIAL_REGISTER_VALUES_PRESENT to the bind options for statement set.
The token can appear in the comment for a statement set that is grouped by special register settings or in a comment on the defaultOptions line.
When you run the Configure utility, add the option -optionsFileForBind to generate a new options file. Verify that the warning is not in the new options file.
When running the SQL statements statically, a DB2 database requires that a positioned update statement is in the same package as its associated query statement. If a positioned update statement must be run with special register settings that are different from the settings that are used by its associated query, then both SQL statements must be run dynamically.
If you cannot create a set of bind options for the statement set, you can specify that the StaticBinder utility not bind the SQL statements in the set. Set the value of the isBindable attribute to false.
The Configure utility also adds the -configureWarning option with the value SPECIAL_REGISTER_GROUPING_INCONSISTENT to the bind options for the statement set.
If you cannot specify a set of bind options, you can specify that the StaticBinder utility not perform a bind operation. Set the value of the isBindable attribute to false for the statement set.
The Configure utility also adds the -configureWarning option with the value MULTIPLE_SPECIAL_REGISTER_VALUE_SETS to the bind options for statement set.
If you cannot specify a set of bind options for the statement set, you can specify that the StaticBinder utility not perform a bind operation. Set the value of the isBindable attribute to false for the statement set.
To capture the statements and record the special register information, run the application again with the current version of pureQuery Runtime.
To record the special register information, specify a user with the authorization required to access special register information.
If special register values are required to run the SQL statements in the statement set correctly, specify a set of bind options that produce the correct settings on the line for the statement set in the options file.
If you do not know the correct bind options, you can specify that the StaticBinder utility not perform a bind operation. Set the value of the isBindable attribute to false for the statement set.
To generate a new options file and verify that the warning is no longer in the file, specify the Configure utility option -optionsFileForBind.
For information about how the SQL statements are grouped, see the Configure utility option -groupSQLByStrings.
The following tokens provide information about a special register values set that was used to run statements in a statement set. When these tokens appear in comments, they are surrounded by square brackets to indicate which tokens correspond to a particular special register values set.
# WARNING: [specialRegValueId(10) minimumDivideScale(3)]The special register values set ID is 10. For the special register values set, the minimum divide scale specified by CURRENT PRECISION special register is 3.
The comment is marked as a warning because on some databases, bind options cannot set the minimum divide scale value for a package. For information about special register values set information in a pureQueryXML file, see Special register information in the pureQueryXML file.
The token can appear in the comment for a statement set that is grouped by special register settings or in a comment on the defaultOptions line.
Determine if any statements in the statement set will run incorrectly with the minimum divide scale for division functions set to the default setting for the database.
If an SQL statement might not run correctly when it is run statically, you can specify that the StaticBinder utility not bind the SQL statements in the set. Set the value of the isBindable attribute to false.
The token can appear in the comment for a statement set that is grouped by special register settings or in a comment on the defaultOptions line.
The Configure utility also adds the -configureWarning option with the value SPECIAL_REGISTER_VALUE_SET_MISSING to the bind options for the statement set.
One reason this warning appears is that the definition for the special register values set is deleted from the file.
If you cannot recover the definition, the SQL statements might not have the correct behavior when they are run statically. You can specify that the StaticBinder utility not bind the SQL statements in the set. Set the value of the isBindable attribute to false for the set.
The token can appear in the comment for a statement set that is grouped by special register settings or in a comment on the defaultOptions line.
If the token is listed in the defaultOptions entry, the Configure utility adds a pureQueryXML file name before the ID. The file contains the specified special register values set.
The token can appear in the comment for a statement set that are grouped by special register settings or in a comment on the defaultOptions line.
The token appears only when the token SQLGroupedBySpecialRegisters(INCONSISTENT_SRINFO) is also present. For information about grouping the SQL statements into different statement sets, see the information for the token SQLGroupedBySpecialRegisters(INCONSISTENT_SRINFO).
If you group the SQL statements in this statement set based on special register settings, some of the statements will be in a set with the token SQLGroupedBySpecialRegisters(NO_SRINFO).
The token can appear in the comment for a statement set that is grouped by special register settings or in a comment on the defaultOptions line.
In some cases, you can use the OWNER bind option to achieve some of the same effects as those that are caused by the CURRENT SQLID special register.
If you cannot create a set of bind options for the statement set, you can specify that the StaticBinder utility not bind the SQL statements in the set. Set the value of the isBindable attribute to false for the set.
The token can appear in the comment for a statement set that is grouped by special register settings or in a comment on the defaultOptions line.
The value of specialRegisters can be one or more keywords. If more than one keyword is not recognized, the Configure utility separates each keyword with a comma.
Unrecognized special registers can be recorded if the SQL statements in the pureQueryXML file were captured with a version of pureQuery Runtime that is earlier than the version of the Configure utility. Unrecognized special registers can be created when special register information in the file is incorrectly manually edited.
If this token appears, use a version of the Configure utility that is from the same release or later than the version that captures the SQL statements in the pureQueryXML file. If the pureQueryXML file was manually edited incorrectly, the edits must be reversed.
The token can appear in the comment for a statement set that is grouped by special register settings or in a comment on the defaultOptions line.
If you cannot create a set of bind options for the statement set, you can specify that the StaticBinder utility not bind the SQL statements in the set. Set the value of the isBindable attribute to false for the set.