StaticBinder options file created by the Configure utility

You use the Configure options -groupSQLBySpecialRegisters and -optionsFileForBind to create a StaticBinder options file for a pureQueryXML file. The bind options and other information in the file help you specify a set of bind options so that an SQL statement runs statically on a DB2® database, the behavior is similar to when it is issued from the application and run dynamically.

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.

Format of the StaticBinder options 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.

Comment
A comment starts with a # symbol. The text from the # symbol to the end of the line is ignored by the StaticBinder utility. The Configure utility appends comments to an entry line or creates a separate comment line.

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 entry
Specifies bind options for all of the pureQueryXML files that you list in an options file. The defaultsOptions entry starts with the following text:
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.

For the following example defaultOptions entry, all of the SQL statements use a single special register values set. The entry is on multiple lines for readability:
defaultOptions = -bindOptions "QUALIFIER(SCHM4) FUNCPATH(SYSIBM,SYSFUN,SYSPROC,SYSADM) 
   ROUNDING(HALFEVEN) DEC(15)" 
   # WARNING: [specialRegValueId(C:\myDir\capture.pdqxml:4) 
   minimumDivideScale(8) SQLID(ADMF003)]
The text before the # character is the defaultOptions entry.
defaultOptions = -bindOptions "QUALIFIER(SCHM4) FUNCPATH(SYSIBM,SYSFUN,SYSPROC,SYSADM)
   ROUNDING(HALFEVEN) DEC(15)"
The text after the # is the comment text for the line. The comment in the example entry contains warning tokens.
# WARNING: [specialRegValueId(C:\myDir\capture.pdqxml:4) minimumDivideScale(8) SQLID(ADMF003)]
The following example defaultOptions entry contains only a comment:
defaultOptions = # WARNING: SQLGroupedBySpecialRegisters(NOT_GROUPED)
statement set bind options

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*)
The text before the # character is the entry for binding the statement set MYRPN1K in the pureQueryXML file C:\myDir\capture.pdqxml:
C:\myDir\capture.pdqxml:MYRPN1K = -bindOptions "QUALIFIER(SCHM9) 
    FUNCPATH(SYSIBM,SYSFUN,SYSPROC,SYSADM) 
    ROUNDING(HALFEVEN) DEC(15)"
The text after the # is the comment text for the line. The comment contains warnings and information tokens related to the statement set.
# WARNING: [specialRegValueId(10) minimumDivideScale(3) UNTRACKED_SET] 
INFO: configureStatus(FINAL) SQLGroupedByStrings(*NO_MATCH_FOUND*)

Comments generated by the Configure utility

The Configure utility appends comments to an entry in the options file or creates a separate comment line.
  • A comment that is appended to an entry contains information for that entry.
  • A separate comment line lists a set of bind options for a special register values set.
If a comment is appended to an options file entry, the comments are specific to the entry. For the following example entry, a comment is appended to the entry. The entry is on multiple lines for readability:
MYRPN1K = -bindOptions "QUALIFIER(SCHM9) 
    FUNCPATH(SYSIBM,SYSFUN,SYSPROC,SYSADM) ROUNDING(HALFEVEN) DEC(15)" 
    # WARNING: [specialRegValueId(10) minimumDivideScale(3) UNTRACKED_SET] 
    INFO: configureStatus(FINAL) 
In the comment of the example entry, the tokens that are enclosed by brackets associate the tokens with a special register values set. In the example entry, the brackets group the two tokens minimumDivideScale(3) and UNTRACKED_SET with the special register value set ID 10.
# WARNING: [specialRegValueId(10) minimumDivideScale(3) UNTRACKED_SET] 
    INFO: configureStatus(FINAL)
The tokens that are not enclosed in brackets refer to the statement set. The example entry contains one informational token specifying that the configure status for the statement set MYRPN1K is 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.

The following example comment line contains bind options based on a special register values set. The comment is on multiple lines for readability:
#  -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.

Comment tokens in the Staticbinder options file

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

Comment tokens for statement sets

The following tokens provide information about statement sets:

configureStatus(FINAL)
An informational token. In the pureQueryXML file, the value of the XML attribute configureStatus is FINAL for the statement set. When the Configure utility processes the statement set, the utility reports errors that are found in the statement set. The utility does not try to fix the errors.

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.

incrementalSpecialRegValuesPresent
A warning token. The statement set attribute configureStatus is set to FINAL, and for one or more SQL statements in the set the XML information for the statement contains the attribute incrementalSpecialRegValuesUsed. The incrementalSpecialRegValuesUsed attribute specifies that new special register values sets were recorded. However, the new special register values sets were not considered when the SQL statements were grouped according to special register settings.

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.

Use one of the following methods to allow the utility to configure a statement set when the configureStatus for the set is set to FINAL:
  • Use the pureQueryXML editor in IBM® Optim™ Development Studio to change the configureStatus attribute is set to REQUIRED before running the Configure utility.
  • Add the option -setPreStatusOfAllPkgs with the value REQUIRED or AUTO when you run the Configure utility. Using this option affects all statement sets in the pureQueryXML file. Use the option only if you do not need to preserve any existing statement sets.
  • Add the option -cleanConfigure with the value TRUE when you run the Configure utility. Do not use the option if you need to preserve any existing statement sets.
Note: Before you change the value of the configureStatus attribute from FINAL, ensure that binding new packages for the statement set does not adversely affect the application running the SQL statements statically. When the Configure utility changes a statement set, the packages that were previously created for the original statement set cannot be used to run the SQL statements statically. You must run the StaticBinder utility to bind new packages.

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.

isBindable(FALSE)
An informational token. In the pureQueryXML file, the value for the XML attribute isBindable is set to false for the statement set. The StaticBinder utility does not perform a bind operation on the statement set.
positionedUpdateSpecialRegValueId
A warning token. The statement set contains positioned update statements and their associated SQL queries. The special register values that were recorded with one or more positioned update statements are different from the special register settings for the queries.

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.

Perform the following steps to specify the bind options for the statement set:
  1. Create a set of bind options that result in correct behavior when running the SQL statements in the statement set statically.
  2. Update the bind options for the statement set in the options file to reflect that set of bind options.

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.

SQLGroupedBySpecialRegisters(warning)
A warning token. The Configure utility detected an issue with the special register information that is recorded with the SQL statements in the statement set.
warning is one of the following values:
  • INCONSISTENT_SRINFO
  • MULTI_SRINFO
  • NO_SRINFO
  • NOT_GROUPED
INCONSISTENT_SRINFO
The SQL statements in the statement set are not correctly grouped according to special register.

The Configure utility also adds the -configureWarning option with the value SPECIAL_REGISTER_GROUPING_INCONSISTENT to the bind options for the statement set.

When configuring statement sets, you can group the SQL statements based on special register settings by specifying the Configure utility option -groupSQLBySpecialRegisters with the value TRUE. Specify the option -optionsFileForBind to generate a new options file and verify that the newly generated options file no longer reports the problem. You can use one of the following methods to configure a statement set:
  • Use the pureQueryXML editor in IBM Optim Development Studio to change the value of configure status to REQUIRED before running the Configure utility.
  • Specify the Configure utility option -setPreStatusOfAllPkgs with the value REQUIRED. Using this option affects all statement sets in the pureQueryXML file.
  • Specify the Configure utility option -cleanConfigure with the value TRUE. Use the option only if you do not need to preserve any existing statement sets.
If you do not change the grouping of the statements in these statement sets, you can use the information in the options file to specify a set of bind options:
  1. Create a set of bind options for running the SQL statements in the statement set statically.
  2. Add the set of bind options to the line for the statement set.
  3. In the line, remove the value SPECIAL_REGISTER_GROUPING_INCONSISTENT from the StaticBinder option -configureWarning. Remove the option if no other values are specified.

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.

MULTI_SRINFO
For each SQL statements in the statement set, the statement was captured multiple times. For the different captures, different special register values were recorded. The Configure utility lists the bind options that correspond to the special register values sets in the options file.

The Configure utility also adds the -configureWarning option with the value MULTIPLE_SPECIAL_REGISTER_VALUE_SETS to the bind options for statement set.

Use the bind options that are listed by the Configure utility to perform the following steps:
  1. Create a set of bind options that result in correct behavior when you run the SQL statements in the statement set statically.
  2. Add the bind options to the entry for the statement set.
  3. In the entry, remove the value MULTIPLE_SPECIAL_REGISTER_VALUE_SETS from the StaticBinder option -configureWarning. Remove the option if no other values are specified.

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.

NO_SRINFO
No special register information is recorded for the SQL statements in the statement set.
The following list describes some reasons that special register information is not recorded with the SQL statements:
  • The SQL statements were captured with an earlier version of the pureQuery Runtime that does not support recording special register information.

    To capture the statements and record the special register information, run the application again with the current version of pureQuery Runtime.

  • The user account that was used to capture the SQL statements did not have the authorization required to access special register information.

    To record the special register information, specify a user with the authorization required to access special register information.

  • The SQL statements were added to the pureQueryXML file with the GeneratePureQueryXml utility.

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.

NOT_GROUPED
The SQL statements in the statement set are not grouped by special register settings. However, SQL statements in other statement sets are grouped by special register settings.
If special register values are required for the SQL statements in the statement sets to obtain the correct behaior, group the SQL statements based on special register settings. Use one of the following methods:
  • Use the pureQueryXML editor in Optim Development Studio to remove the names of the statement sets. Then run the Configure utility again and specify the option -groupSQLBySpecialRegisters with the value TRUE.
  • Run the Configure utility and specify the option -cleanConfigure with the value TRUE and the option -groupSQLBySpecialRegisters with the value TRUE. Do not use the -cleanConfigure option if you need to preserve any existing statement sets.

To generate a new options file and verify that the warning is no longer in the file, specify the Configure utility option -optionsFileForBind.

SQLGroupedByStrings(strings)
An informational token. SQL statements in the statement set are grouped based on the specified strings.

For information about how the SQL statements are grouped, see the Configure utility option -groupSQLByStrings.

Comment tokens for special register values sets

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.

The following example comment contains tokens for a 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.

minimumDivideScale(scale)
A warning token. In the associated special register values set, the CURRENT PRECISION special register value includes the specified minimum divide scale for division operations. The format of the CURRENT PRECISION special register value is Dpp.s. The variable pp is the precision and is either 15 or 31. The Configure utility generates the bind option DEC to specify the precision. The variable s is the optional minimum divide scale. The value of s is an integer between 1 and 9. The value of s is the value of scale for the token minimumDivideScale(scale). On some databases, bind options cannot set the minimum divide scale value for a package.

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.

missingSpecialRegValueId(IDs)
A warning token. The SQL statements in the statement set are associated with special register values sets in the IDs list. However, the special register values sets are not defined 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.

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.

You can recover the definition for the special register values set in one of the following ways:
  • If you manually edited the file and removed the special register values set definition, reverse the changes that removed the definition.
  • Remove the statement set and recapture the SQL statements in the set.

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.

specialRegValueId(IDs)
An informational token. For the SQL statements in the statement set, pureQuery Runtime recorded the special register values sets that are listed by the IDs. The value of the IDs can be one or more special register IDs. If more than one special register values set is associated with the statement set, the Configure utility separates each ID with a comma.

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.

A special register ID can have one of the following formats:
  • A single integer that specifies the ID of the special register values set.
  • pureQueryXmlFilePath:ID, a file name and integer separated by colon.

    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.

specialRegValueId(NONE)
A warning token. The statement set contains some SQL statements that are associated with special register information and some SQL statements that are not associated with special register information.

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

SQLID(sqlid)
A warning token. The value of sqlid is the value of the CURRENT SQLID special register in the associated special register values set. The token is not included when the value of the CURRENT SQLID special register matches the value of the CURRENT SCHEMA special register.

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.

Before using the bind options for the statement set, perform the following steps to ensure you are using the correct options:
  1. Determine if the CURRENT SQLID value is necessary for correct execution of SQL statements in the statement set.
  2. Create a set of bind options that will result in correct behavior when running the SQL statements in the statement set statically.

    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.

  3. If necessary, update the entry for the statement set in the options file to reflect that set of bind options.

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.

unrecognizedSpecialRegisters(specialRegisters)
A warning token. One or more special register sets contained one or more special registers that the Configure utility did not recognize.

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.

UNTRACKED_SET
A warning token. The SQL statements that were executed with the associated special register values set were run after the application ran an SQL SET statement for a special register that pureQuery client optimization does not record.

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.

Before using the bind options for the statement set, perform the following steps to ensure you are using the correct option:
  1. Refer to the application and to all pureQueryXML files that have been captured to identify any SQL SET statements that were executed for special registers that the pureQuery Runtime does not track and considers unsafe. For information about the special register information that is recorded when SQL statements are captured, see Tracking DB2 special register changes with pureQuery client optimization.
  2. For any SQL SET statements in the statement set, determine whether they are necessary for the correct execution of the SQL statements in the statement set.
  3. Create a set of bind options that will result in correct behavior when running the SQL statements in the statement set statically.
  4. If necessary, update the entry for the statement set in the options file to reflect that set of bind options.

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.


Feedback