Syntax for entries in an options file for the StaticBinder utility for client optimization

This syntax diagram shows the options that you can set for each pureQueryXML file that you list in an options file.
Read syntax diagramSkip visual syntax diagram
                                                  (1)   
>>-+-pureQueryXML-file-----------------------+--=--------------->
   '-pureQueryXML-file--:--base-package-name-'          

>--+---------------------------------------------------------------------------------------------------------------------+-->
   |  (2)   (3)                                                                                                          |   
   '------------- -url--jdbc--:--db2--:--//--server--+---------+--/--database-- -username--user-ID-- -password--password-'   
                                                     '-:--port-'                                                             

>--+-----------------------------------------+------------------>
   '-+- -bindOptions-- -"--bind-options--"-+-'   
     |                  (4)                |     
     '-| DBRM options |--------------------'     

>--+----------------------------------------------+------------->
   |                     .----------------------. |   
   |                     V                      | |   
   '- -configureWarning---- -configure-warnings-+-'   

>--+-----------------------------+--+----------------+---------->
   |                   .-FALSE-. |  |            (5) |   
   '- -differenceOnly--+-TRUE--+-'  '-| -grant |-----'   

>--+--------------------------+--+--------------------------+--->
   '- -isolationLevel--+-CS-+-'  |                .-FALSE-. |   
                       +-RR-+    '- -showDetails--+-TRUE--+-'   
                       +-RS-+                                   
                       '-UR-'                                   

                         .-NOT_SET------.   
>-- -statementBindError--+-REMOVE-------+----------------------->
                         '-MARK_INVALID-'   

>--+--------------------------+--------------------------------><
   |                .-FALSE-. |   
   '- -validateXml--+-TRUE--+-'   

Notes:
  1. You can specify the options in any order.
  2. If you do not specify the URL, user ID, and password when you use the -bindOptions option, you must specify them either on the command line or in the defaultOptions entry.
  3. You do not need to use the -url, -username- and -password options if you are creating DBRM files only.
  4. For the syntax, see the description of these options.
  5. For the syntax, see the description of this option.

Descriptions of options

-bindOptions "string-of-bind-options"
These options have the same function as DB2® precompile and bind options with the same names. The string can have one of the following formats:
  • "option_1 value_1 option_2 value_2"
  • "option_1(value_1) option_2(value_2)"

The value for each option cannot contain spaces.

For a list and descriptions of these options, see BIND command.

If you use the CONCURRENTACCESSRESOLUTION bind option in DB2 Version 9.7 for Linux, UNIX, and Windows, the option applies to all of the statements that are in packages that are bound at the CS and RR isolation levels. This option, which applies only to these two isolation levels, lets applications find the last committed value in rows that are locked by other applications.
-configureWarning configure-warnings
Specifies the warnings from the pureQuery Configure utility. Multiple warning values are separated by a space.

When you specify the Configure utility -optionsFileForBind option, the Configure utility generates a StaticBinder options file. The options file contains the bind options and additional information that is based on the SQL statements, statement sets, and DB2 special register information in the pureQueryXML file. The Configure utility adds the -configureWarning option to the bind options when it detects a possible problem. If you bind packages to the database by using the specified bind options and run the SQL statements statically, the resulting behavior might not be correct.

The Configure utility also adds a warning token to a comment line of the statement set. The comments can contain warnings and information that are related to the statement set and SQL statement in the set. For information about how to fix the warnings, see the information about the tokens that are generated with the warnings in Bind options file created by the Configure utility

If you specify the -configureWarning option when you run the StaticBinder utility, the utility attempts to bind the associated packages. The StaticBinder utility also prints a warning message indicating the problem specified by the warning.

The -configureWarning option can use the following values:

INCREMENTAL_SPECIAL_REGISTER_VALUES_PRESENT
In the pureQueryXML file, the XML attribute for one or more SQL statements in the statement set contains the attribute incrementalSpecialRegValuesUsed. The attribute records the use of new special register values sets. The statement sets were grouped according to special register settings. However, the new special register values sets were not considered in the grouping.

The Configure utility also adds the token incrementalSpecialRegValuesPresent to a comment line for the statement set.

SPECIAL_REGISTER_VALUE_SET_MISSING
This value indicates that SQL statements in the statement set reference special register values sets, however the special register values sets are not defined in the pureQueryXML file.

The Configure utility also adds the token missingSpecialRegValueId(IDs) to a comment line for the statement set.

MULTIPLE_SPECIAL_REGISTER_VALUE_SETS
This value indicates that each of the SQL statements in the statement set was executed with more than one special register values set.

Configure utility also adds the token SQLGroupedBySpecialRegisters(MULTI_SRINFO) to a comment line of the statement set.

SPECIAL_REGISTER_GROUPING_INCONSISTENT
This value indicates that the statement set is not grouped correctly according to the special register settings.

The Configure utility also adds the token SQLGroupedBySpecialRegisters(INCONSISTENT_SRINFO) to a comment line of the statement set.

DBRM options
Use these options to generate DBRM files, rather than create packages. These options apply only if you are using DB2 for z/OS®.

After the StaticBinder utility generates the DBRM files, you must copy the files to a data set. The default DBRM data set name is prefix.DBRMLIB.DATA, where prefix is the high-level qualifier that is specified in the TSO profile for the user. prefix is usually your user ID in TSO.

If the DBRM data set does not already exist, you must create it. The DBRM data set requires space to hold all the SQL statements, with additional space for each host variable name and some header information. The header information requires approximately two records for each DBRM, 20 bytes for each SQL record, and 6 bytes for each host variable. For an exact format of the DBRM, see the DBRM mapping macro, DSNXDBRM in library prefix.SDSNMACS.

The following syntax diagram describes the options for generating DBRM files.

Read syntax diagramSkip visual syntax diagram
                   .-FALSE-.                           
>>- -generateDBRM--+-TRUE--+-- -outputDBRMPath--path-----------><

-generateDBRM TRUE|FALSE
Specifies whether to generate DBRM files instead of create packages. If generateDBRM is TRUE, then by default the StaticBinder utility generates four DBRM files, one for each isolation level: CS, RR, RS, UR. However, you can generate only one DBRM file if you specify the -isolationLevel option.

The root name of the generated DBRM files is the root package name that you specify when you run the Configure utility.

The default is FALSE.

-outputDBRMPath path
Specifies the directory in which to save the generated DBRM files. The default value is the directory from which you run the StaticBinder utility.
-differenceOnlyTRUE|FALSE
Specifies not to replace DB2 packages that have collection names, package names, and consistency tokens that match these values for the corresponding statement sets within the pureQueryXML file that you run the StaticBinder utility on.

For example, suppose that you run the StaticBinder utility on a pureQueryXML file named capture.pdqxml. The utility creates the packages MYPKGA, MYPKGB, and MYPKGC. Then you edit the statement set MYPKGA in capture.pdqxml with the workbench and run the Configure utility on the file with the -cleanConfigure option at its default value of FALSE. The Configure utility assigns a new consistency token to the statement set because the set has changed. When you run the StaticBinder utility on capture.pdqxml again to bind the new version of MYPKGA, you specify -differenceOnly TRUE. The utility rebinds only MYPKGA and does not rebind the other two packages.

The default value is FALSE.
-grant "grantees(firstID,secondID,...)"
Specifies the list (in parentheses) of comma-separated grantees to whom you want to grant the EXECUTE privilege on packages that the StaticBinder utility creates. If you do not specify this option, the StaticBinder utility does not grant this privilege.
Read syntax diagramSkip visual syntax diagram
                             .-,--------------------.          
                             V                      |          
>>- -grant-- "--grantees--(----+-authorization-ID-+-+--) - "---><
                               '-PUBLIC-----------'            

grantees
The comma-separated list of authorization IDs to which you want to grant the EXECUTE privilege. Ensure that the authorization IDs that you list are valid for the DB2 database that you are using.

For DB2 Database for Linux, UNIX, and Windows: You can use the USER, GROUP, and ROLE keywords. For information about these keywords, see GRANT (Package Privileges) statement.

For DB2 for z/OS: You can use the ROLE keyword. For information about this keyword, see GRANT (package privileges).

Restriction: You cannot use the -grant option when the value of the -generateDBRM option is TRUE.

-isolationLevel CS|RR|RS|UR
Specifies that you want to generate a single DB2 package or DBRM file that is for a particular isolation level, rather than one DB2 package or DBRM file for each of the four isolation levels.

The isolation level applies to all of the SQL statements that are in the package. If you set an isolation level through the Connection.setTransactionIsolation() method of the IBM® Data Server Driver for JDBC and SQLJ, pureQuery ignores that isolation level for statements executed statically.

UR
Specifies Uncommitted Read as the isolation level.
The StaticBinder utility adds 1 to the name of the created package or DBRM file, unless you used the -forceSingleBindIsolation option when you ran the Configure utility.
CS
Specifies Cursor Stability as the isolation level.
The StaticBinder utility adds 2 to the name of the created package or DBRM file, unless you used the -forceSingleBindIsolation option when you ran the Configure utility.
RS
Specifies Read Stability as the isolation level. Read Stability ensures that the execution of SQL statements in the package is isolated from other application processes for rows that are read and changed by the application.
The StaticBinder utility adds 3 to the name of the created package or DBRM file, unless you used the -forceSingleBindIsolation option when you ran the Configure utility.
RR
Specifies Repeatable Read as the isolation level.
The StaticBinder utility adds 4 to the name of the created package or DBRM file, unless you used the -forceSingleBindIsolation option when you ran the Configure utility.
-password password
The password to use to connect to the data source.
pureQueryXML-file
Specifies the pureQueryXML file that contains the sets of SQL statements that you want to bind as packages. For example, C:\directory\captureFile.pdqxml .
This file must have the extension .pdqxml or .xml. The file must either be a resource in the classpath for the application or you must provide the full or relative path to the file.
pureQueryXML-file:base-package-name
Specifies the pureQueryXML file and a single set of SQL statements within that file that you want to bind as a package. For example, C:\directory\captureFile.pdqxml:MYPKGA.

The file must have the extension .pdqxml or .xml. The file must either be a resource in the classpath for the application or you must provide the full or relative path to the file.

The base name of the package is the value of the name attribute of the package element that describes the package in the pureQueryXML file. The base name consists of two parts:
  • The root package name
  • Any characters that the Configure utility appends to the root package name when more than one package name is needed. For example, the utility creates more than one package name when the number of SQL statements in a statement set exceeds value of the Configure utility -sqlLimit option.
-showDetails TRUE|FALSE
Specifies whether the StaticBinder utility displays detailed information regarding the DB2 packages that it produces and the SQL statements that are in the pureQueryXML files that it processes.
The default value is false.
-statementBindError NOT_SET|MARK_INVALID|REMOVE
Specifies how the StaticBinder utility handles SQL statements in the pureQueryXML file when the bind process returns an SQL error for the statement. There is no default value. If the option is not set, the StaticBinder utility reports the SQL statements that return an SQL error during the bind process as they are encountered. The following list describes the supported values and how SQL statements are handled:
NOT_SET
Specifies that the SQL statements in the pureQueryXML file are not modified. This value is the default value.
MARK_INVALID
Specifies that pureQueryXML file is updated to indicate that the SQL statement is invalid when the attempted bind results in an SQL error. The SQL errors and the SQL statements that are marked invalid are displayed after the bind operation completes for the statement set that contains the statements.

SQL statements that have been previously marked as invalid do not appear in the report.

You can restore SQL statements that are marked invalid with Configure option -restoreInvalidSQLForce. You can remove the statements with the option -removeInvalidSQL.

REMOVE
Specifies that the SQL statement is removed from the pureQueryXML file when the attempted bind results in an SQL error. The SQL errors and the SQL statements that are removed are displayed after the bind operation completes for the statement set that contains the statements. SQL statements that were previously marked as invalid are also removed.
CAUTION:
If you use this option with the MARK_INVALID or REMOVE value, your pureQueryXML file might be changed. The utility updates the file to mark an SQL statement as invalid or remove the statement from the file.

If the -statementBindError option is specified with the value MARK_INVALID or REMOVE, the pureQueryXML files must be writeable. If the file cannot be updated by the StaticBinder utility, an error is displayed, and the file is not processed.

The following items affect the ability of the StaticBinder utility to detect invalid SQL statements:
  • Specifying the StaticBinder -differenceOnly option with the value TRUE. The StaticBinder does not attempt to bind all SQL statements. The StaticBinder utility does detect invalid SQL statements in statement sets it does not attempt to bind.
  • Specifying the bind option SQLERROR (CONTINUE) or VALIDATE (RUN) with the StaticBinder option -bindOptions. If either BIND option is specified, DB2 diagnostics that would be reported as SQL errors are instead reported as SQL warnings. The StaticBinder utility does not recognize SQL statements as invalid statements when the diagnostics reports SQL Warnings.

The -statementBindError option is not supported when binding pureQuery Data Access Object (DAO) interface implementation classes. If the StaticBinder utility is run to bind an implementation class and the option is specified with the value MARK_INVALID or REMOVE, the utility displays a warning is displayed and does not attempt to bind the classes.

Note: Invalid SQL statements are statements that return an SQL error during an attempted bind. However, there can be changes to a database object that would not make SQL statements referencing that object invalid. The changes might affect the application that runs the SQL statement. For example, a statement is not invalid if the type definition of column that the statement references changes from VARCHAR(20) to VARCHAR(100).
-url connection-URL
The Type 4 JDBC URL for connecting to the database.
If you bind SQL statements that use named parameter markers, you must specify the property enableNamedParameterMarkers with the value 1 when you specify the connection to the data source. The following example -url option connects to the SAMPLE database on testserver.test.com and specifies the property enableNamedParameterMarkers:
-url jdbc:db2://localhost:50000/SAMPLE:enableNamedParameterMarkers=1;
-username user-ID
The user ID to use to connect to the data source.
-validateXml TRUE|FALSE
Specifies whether XML schema validation is performed on the input pureQueryXML files using the pureQueryXML schema. If the value is TRUE, validation is performed. If the value is FALSE or if the option is not specified, validation is not performed.

Success or failure of XML schema validation is determined and reported for each input file. If one input file fails, the StaticBinder processing does not stop, subsequent files will be processed.

If a pureQueryXML file fails schema validation, the packages within that file will not be bound. The first schema validation error and the bind failure for the file are reported.

Only current release or previous version pureQueryXML files are validated (version 4 or 3). If an earlier version pureQueryXML is detected, validation is not performed on that file.


Feedback