Syntax for running the StaticBinder utility for client optimization with an options file

You can use a command and an options file to specify the names of pureQueryXML files and the options for creating DB2® packages or DBRM files that are based on those files. You can also use an options file even if you have only one pureQueryXML file.

You can list the pureQueryXML files in the command and use only the defaultOptions entry in the options file.

You can list pureQueryXML files in the command and in the options file. If a file is listed both in the command and in the options file, the StaticBinder utility processes the file. If a file is listed in the options file but not in the command, the StaticBinder utility does not process the file. If a file is listed in the command but not in the options file, the StaticBinder utility processes the file with the options on the command line and in the defaultOptions entry of the options file.

When you run the StaticBinder utility from a command line and use an options file, the utility recognizes options in the following order of precedence:
  1. Options on the command line
  2. Options for individual pureQueryXML files and statement sets that are in the specified options file
  3. Default options in the specified options file
Read syntax diagramSkip visual syntax diagram
                                        (1)   
>>-java--com.ibm.pdq.tools.StaticBinder------------------------->

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

>--+-----------------------------------------------------------------+-->
   |                 .---------------------------------------------. |   
   |                 V                                             | |   
   '- -pureQueryXml----+-pureQueryXML-file-----------------------+-+-'   
                       '-pureQueryXML-file--:--base-package-name-'       

>-- -optionsFile--file-name--+-----------------------------+---->
                             |                   .-FALSE-. |   
                             '- -differenceOnly--+-TRUE--+-'   

>--+--------------------------+--------------------------------->
   |                .-FALSE-. |   
   '- -showDetails--+-TRUE--+-'   

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

>--+-----------------------+------------------------------------>
   |                   (3) |   
   '-| Trace options |-----'   

>--+-------------------------------+---------------------------->
   '- -verifyPackages--+-DETAIL--+-'   
                       '-SUMMARY-'     

>--+--------------------------+--------------------------------><
   |                .-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 in the command, you must specify them in the options file, if you are not creating DBRM files only.
  3. For the syntax, see the description of these options.

Descriptions of options

-optionsFile file-name
The name of the file, including its absolute or relative path, that lists the pureQueryXML files that contain the SQL statements that you want to bind.
-password password
The password to use to connect to the data source.
-pureQueryXml pureQueryXML-file|pureQueryXML-file:base-package-name
Specifies either the pureQueryXML file that contains the sets of SQL statements that you want to bind as packages or a single set of SQL statements that you want to bind as a package. You can specify more than one value.
pureQueryXML-file
The name of the pureQueryXML file. 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
The name of the pureQueryXML file and the base name of the package to bind. 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.
Note: If you specified the Configure utility option -forceSingleBindIsolation when you configured the pureQueryXML file, the StaticBinder utility does not append an integer to the package name. The integer represents the isolation level that applies to all of the SQL statements that are in the package.
When you use -pureQueryXml together with -optionsFile in a single command, a number of outcomes are possible. See Using an options file while specifying pureQueryXML files and statement sets in a command.
-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.
Trace options
You can specify the file to log messages in and the level of information to log.
Read syntax diagramSkip visual syntax diagram
>>-+------------------------+--+---------------------------+---><
   '- -traceFile--file-name-'  |               .-OFF-----. |   
                               '- -traceLevel--+-ALL-----+-'   
                                               +-SEVERE--+     
                                               +-WARNING-+     
                                               +-INFO----+     
                                               +-CONFIG--+     
                                               +-FINE----+     
                                               +-FINER---+     
                                               '-FINEST--'     

-traceFile file-name
Specifies the absolute or relative path and name of the file to use for logging information about the operation.
If the file already exists, pureQuery appends new messages to the existing content of the file. As the default, the entries are written to System.err.
-traceLevel OFF|SEVERE|WARNING|INFO|CONFIG|FINE|FINER|FINEST|ALL
Specifies the type of information to log. The default level is OFF. If you do not specify a file in which to write the log entries and you set this option to any value other than OFF, the entries are written to System.err.
-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.
-verifyPackages DETAIL|SUMMARY
Specifies whether the StaticBinder utility generates a report of the packages that exist and do not exist for the SQL statements that are associated with the pureQueryXML file. When you use this option, the StaticBinder utility does not bind packages.

For example, suppose that you ran the Configure utility on a pureQueryXML named myApp.pdqxml. When you ran the utility, you supplied values for the -collection, -pkgVersion, and -rootPkgName options, and the utility stored these values in the pureQueryXML file. You run the StaticBinder utility, specifying the name of this file, and the utility creates DB2 packages.

At a later time, you want to see a list of the packages that the StaticBinder utility created from the pureQueryXML file. When you run the utility, you can use the -verifyPackages option, specifying the value DETAIL, and again supply the name of the file.

The -verifyPackages option works on the premise that, after you ran the Configure utility on a pureQueryXML file and then ran the StaticBinder utility on that file, you did not run the Configure utility again on the file and supply different values for -collection, -pkgVersion, and -rootPkgName.

If you ran the Configure utility on myApp.pdqxml after you first ran the StaticBinder utility and you changed any of the values for -collection, -pkgName, and -rootPkgName, the StaticBinder utility would not find any packages that matched the new values of these options. In its report, the StaticBinder utility would say that the packages that you were looking for do not exist.

If the values for the -collection, -pkgVersion, and -rootPkgName options are the same as when you ran the StaticBinder utility the previous time, the utility finds the packages and lists them.

You can specify this option together with the -bindOptions option. However, the StaticBinder utility will not bind packages. Use -bindOptions only to specify the collection for the packages that you want to verify if you used this option to specify the collection when you created the packages.

DETAIL
Produces a report that explains the following information, which is based on the values of -collection, -pkgVersion, and -rootPkgName:
  • Which packages do not exist.
  • Which packages do exist. For each package, the report lists the name, consistency token, timestamp, and the isolation level.
  • The number of packages that exist or do not exist.
SUMMARY
Produces a report that enumerates the packages that exist and do not exist, given the values of the values of -collection, -pkgVersion, and -rootPkgName.
-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