Syntax of entries in the Default.genProps file for configuring the static execution of SQL statements in non-pureQuery API applications

Use the Default.genProps file to set options for the DB2® packages that you want to contain bound SQL statements.

To open this file in an editor in the workbench, double-click it. The file is located in the folder pureQueryFolder in your Java project.

In this file, you can specify options that apply to all of the pureQueryXML files that are in your project. You can also specify options specific to individual pureQueryXML files.

You can add comments to this file. Begin each comment with a # symbol.

As you create or edit entries, you can type CTRL+SPACE (or your own keystroke, if you changed this default one) for assistance.

For help understanding syntax diagrams, see How to read syntax diagrams.

After you save the file, the workbench runs the Configuration utility on it and refreshes the SQL Outline view. The view still shows only unbound packages because you did not yet perform the bind operation.

The following diagram shows the syntax that is allowed for setting default options for all of the pureQueryXML files that the file lists.

Read syntax diagramSkip visual syntax diagram
>>-defaultOptions--=--+-----------------------------------+----->
                      |               .-NULLID----------. |   
                      '- -collection--+-collection-name-+-'   

>--+--------------------------------+--------------------------->
   '- -pkgVersion-- -+-AUTO-------+-'   
                     '-version-ID-'     

>--+--------------------------------------------------------+--->
   '- -allowStaticRowsetCursors--+-READ_ONLY--------------+-'   
                                 +-READ_ONLY_SCROLLABLE---+     
                                 +-READ_ONLY_FORWARD_ONLY-+     
                                 '-NEVER------------------'     

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

>--+------------------------+--+-----------------------+-------><
   |             .-100----. |  |                   (1) |   
   '- -sqlLimit--+-number-+-'  '-| Trace options |-----'   

Notes:
  1. For the syntax, see the description of these options.

The next syntax diagram shows the syntax that is allowed for setting options for individual pureQueryXML files that the file lists.

Read syntax diagramSkip visual syntax diagram
>>-pureQueryXML-file--+-----------------------------------+----->
                      |               .-NULLID----------. |   
                      '- -collection--+-collection-name-+-'   

>--+--------------------------------+--------------------------->
   '- -pkgVersion-- -+-AUTO-------+-'   
                     '-version-ID-'     

>-- -rootPkgName--package-name-stem----------------------------->

>--+--------------------------------------------------------+--->
   '- -allowStaticRowsetCursors--+-READ_ONLY--------------+-'   
                                 +-READ_ONLY_SCROLLABLE---+     
                                 +-READ_ONLY_FORWARD_ONLY-+     
                                 '-NEVER------------------'     

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

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

>--+------------------------+--+-----------------------+-------><
   |             .-100----. |  |                   (1) |   
   '- -sqlLimit--+-number-+-'  '-| Trace options |-----'   

Notes:
  1. For the syntax, see the description of these options.
pureQueryXML-file
Specifies that the options on this line of the options file apply only to the SQL statements that are in the pureQueryXML file. These options override options that you specify as defaults.
The name of this file must end with the extension .pdqxml or .xml.

In addition to the file name, you must specify an absolute path to the file.

-allowStaticRowsetCursors
Use this option when you want there to be a difference between which statements used rowset cursors during capture and which statements use rowset cursors during static execution. These are some examples of situations in which this might occur:
  • You want to use rowset cursors during static execution, but you captured using a database other than DB2 for z/OS®. For example, you might have captured using DB2 for Linux, UNIX, and Windows.
  • Your capture environment used rowset cursors, but you do not want to use rowset cursors during static execution,
  • You want to use rowset cursors during static execution. You captured against DB2 for z/OS, and you will execute statically against DB2 for z/OS, but you will be using a different driver type during static execution than what you used during capture. For example, if you captured using a IBM® Data Server Driver for JDBC and SQLJ T4 driver, but will execute statically with a IBM Data Server Driver for JDBC and SQLJ T2 driver, you might want to specify "-allowStaticRowsetCursors READ_ONLY". Similarly, if you captured using a IBM Data Server Driver for JDBC and SQLJ T2 driver, but you will execute statically with a IBM Data Server Driver for JDBC and SQLJ T4 driver, you might want to specify "-allowStaticRowsetCursors READ_ONLY_SCROLLABLE".
  • You want to use rowset cursors during static execution. Your capture file was created with a version of pureQuery that is older than 2.2, so none of the statements in it are marked to use rowset cursors.

Only DB2 for z/OS supports rowset cursors. Rowset cursors are used only when the IBM Data Server Driver for JDBC and SQLJ properties indicate that they should be used. If you capture from a different type of data source or without having rowset cursors enabled, none of the statements that are captured use rowset cursors.

When rowset cursors are not used for forward-only cursors, a Type 4 connection can internally use a similar mechanism to minimize network traffic.

This option takes one of four values:

READ_ONLY
Specifies to allow the use of rowset cursors by SELECT statements (that open read-only cursors that are either forward-only or scrollable) that do not select any columns with LOB or XML data types. No other SELECT statements are allowed to use rowset cursors.
This option adds the attribute allowStaticRowsetCursors to each SELECT statement in the pureQueryXML file that meets the criteria and sets the value in each case to TRUE.
READ_ONLY_SCROLLABLE
Specifies to allow the use of rowset cursors by SELECT statements that open read-only, scrollable cursors and that do not select any columns with LOB or XML data types. No other SELECT statements are allowed to use rowset cursors.
This option adds the attribute allowStaticRowsetCursors to each SELECT statement in the pureQueryXML file that meets the criteria and sets the value in each case to TRUE.
READ_ONLY_FORWARD_ONLY
Specifies to allow the use of rowset cursors by SELECT statements that open read-only, forward-only cursors and that do not select any columns with LOB or XML data types. No other SELECT statements are allowed to use rowset cursors.
This option adds the attribute allowStaticRowsetCursors to each SELECT statement in the pureQueryXML file that meets the criteria and sets the value in each case to TRUE.
NEVER
Specifies that no SELECT statements are allowed to use rowset cursors.
This option adds the attribute allowStaticRowsetCursors to each SELECT statement in the pureQueryXML file and sets the value in each case to FALSE. If you want to enable rowset cursors for a subset of these SELECT statements, you can set this attribute to TRUE in the workbench.

You do not need to use this option if either of these conditions is true:

  • You set the IBM Data Server Driver for JDBC and SQLJ to use multiple-row FETCH for scrollable cursors. You captured the SELECT statements by running them against a DB2 for z/OS data server and by using a T4 connection. You plan to run the statements statically against the same data server or a compatible one by using a T4 connection.
  • You set the IBM Data Server Driver for JDBC and SQLJ to use multiple-row FETCH for forward-only and scrollable cursors. You captured the SELECT statements by running them against a DB2 for z/OS data server and by using a T2 connection. You plan to run the statements statically against the same data server or a compatible one by using a T2 connection.
The SQL statements that you want to allow to use rowset cursors when pureQuery runs those statements statically must meet these conditions:
  • The captured statements are SELECT statements.
  • None of these SELECT statements is run by a stored procedure.
  • None of the columns in query results are of the LOB or XML data types.
  • The cursors that the statements open are read-only. If a static updatable cursor uses rowsets, the results are unpredictable.

    pureQuery considers a statement as updatable if either of these conditions are true:

    • The concurrency attribute is set to java.sql.ResultSet.CONCUR_UPDATABLE.
    • The SQL statement contains the FOR UPDATE clause.
    pureQuery considers a statement as read-only if the statement is not updatable.

If you run statements statically against a data source that does not support rowset cursors, pureQuery does not try to use a rowset cursor for any statements that you specified to use one.

If you do not specify this option, by default pureQuery uses rowset cursors for statements that the IBM Data Server Driver for JDBC and SQLJ used rowset cursors for when you captured statements.

-cleanConfigure TRUE|FALSE
Specifies the extent to which you want the Configure utility to process a pureQueryXML file.
TRUE
During processing, all SQL statements are organized into statement sets according to the options specified, such as sqlLimit, groupSQLByString, and groupSQLBySpecialRegister. The Configure utility reorganizes SQL statements into statement sets as necessary.

For all statement sets, the Configure utility sets the value of configureStatus to AUTO during the configuration process. Overrides the statement set configureStatus value of FINAL.

FALSE
Causes the Configure utility to set the names and collection IDs of statement sets in a pureQueryXML file. The affected statement sets are those for which the configureStatus flag is set to REQUIRED or AUTO and those that have no name. The Configure utility modifies statement sets for which the configureStatus flag is set to AUTO only if the utility determines that the statement set must be modified.

FALSE is the default value.

For unnamed statement sets, configuration applies all specified options.

For named statement sets with the value of the configureStatus set to REQUIRED, some options are applied. Options that are applied include the -collection, -pkgVersion , -forceSingleBindIsolation, and -markDDLForBind options. The consistency token is updated. If you run SQL statements in the statement set statically, you need to rebind the package that is created from the statement set.

During the configuration process, the Configure utility changes the value of configureStatus from REQUIRED to AUTO.

For named statement sets with the value of the configureStatus set to AUTO, the Configure utility applies options only if it determines that the statement set must be modified. The consistency token is not updated if the statement set is not modified. When the configureStatus flag is set to AUTO and the -pkgVersion option is specified, the utility updates the statement set using the version ID. If you run SQL statements in the statement set statically and the statement set is modified, you need to rebind the package that is created from the statement set.

For named statement sets with the configureStatus flag set to FINAL, the Configure utility displays errors when problems are found with the statement set. Errors include section numbers not in increasing order and position update statements not associated with a SELECT statement. The Configure utility displays warnings when statements need to be removed from a statement set. For example, a statement might be removed from a statement set when the -removeSQLInactiveForDays option is specified and the timestamp for the statement matches the removal criteria. A statement might be moved to another statement set if the special register values associated with the statement changes.

-collection collection-name
The qualifier for the packages that the pureQuery StaticBinder utility binds. The qualifier must meet the constraints for collection names for the DB2 database that you are using.
If you do not specify this parameter, the value defaults to NULLID.
defaultOptions
Specifies that the options on the line apply to the SQL statements in all of the pureQueryXML files that are listed in this file. Only one line in the file is allowed to start with this keyword.
-forceSingleBindIsolation TRUE|FALSE
Specifies whether you plan to create a DB2 package for only a single isolation level and do not want a number appended to the name of the package to indicate the isolation level. The default value is false, which means that a numeric indicator is appended.
Use this option together with the -isolationLevel option in the Default.bindProps file.

If you want to use the -isolationLevel option to bind a single statement set into two or three different packages, with each package at a different isolation level, do not use the -forceSingleBindIsolation option when you run the Configure utility on the pureQueryXML file in which the statement set appears.

For example, in an options file for the StaticBinder utility, suppose that you included these two entries:

C:/dir/captureFile.pdqxml:MYPKGA = -isolationLevel UR
C:/dir/captureFile.pdqxml:MYPKGA = -isolationLevel CS

If you used the -forceSingleBindIsolation option when you ran the Configure utility on the captureFile.pdqxml file, the StaticBinder utility performs these actions:

  1. Bind the package MYPKGA at the UR isolation level.
  2. Rebind the package MYPKGA at the CS isolation level.

The result is a single MYPKGA package that is bound at the CS isolation level.

If you want the result to be two MYPKGA packages, one at the UR and the other at the CS isolation level, do not use the -forceSingleBindIsolation option when you run the Configure utility on captureFile.pdqxml.

-pkgVersion AUTO|version-ID
Specifies the package version to use when packages are bound.

DB2 allows multiple versions of a package to exist at the same time, so that you can bind new packages without replacing older versions of packages with the same names. If you encounter problems with a newer package, you can use an older version of that package.

The Configure utility also creates a copy of the pureQueryXML file, appending the version ID to the name of the file.

AUTO
Specifies that the version ID is automatically generated by using the current timestamp. A single timestamp is used for the version ID.
version-ID
Specifies the version identifier for a package. The string must be valid for the version of DB2 that you are using.

The version ID can include only characters that are valid for file names on your operating system. Also, the length of the string and the name of the package cannot exceed the length that your operating system supports for file names.

If you specify this option and the Configure utility does not change the pureQueryXML file, the utility does not create a copy of the pureQueryXML file.

If you do not specify this option, database packages that result from subsequent binds are created without a version and a copy of the file is not created.

Verification of the version at run time is based on the consistency token, not the version name.

-rootPkgName package-name-stem
Specifies the stem of the name to use for DB2 packages. For a full discussion of how the Configure utility creates the names of DB2 packages, see the description for sqlLimit.

package-name-stem must be within the constraints that are set by the DB2 database that you are using.

If you plan to use the pureQuery StaticBinder utility to generate DBRM files rather than create DB2 packages, package-name-stem must be in uppercase and should be no more than 6 characters long. The Configure utility adds characters to the name stem when either the pureQueryXml file contains DDL statements, or the number of statements per package (specified with sqlLimit) is reached. If you use the StaticBinder utility to generate a DBRM file and the combination of package-name-stem, the extra characters due to the activity of the Configure utility, and the isolation level is longer than 8 characters, the StaticBinder utility throws an exception.

-sqlLimit number
Specifies a limit for the number of SQL statements that are allowed in the DB2 package. In general, if the number of SQL statements exceeds this limit, the pureQuery StaticBinder utility binds another package that contains the excess statements. In some cases, the StaticBinder utility includes slightly more SQL statements in a package.

The default value is 100.

For example, suppose that you want to bind four packages (a package for the four different isolation levels) with a root name of myPackage, assuming that your database supports long package names. If the pureQueryXML file that you are using contains between 101 and 199 statements and you use the default value for sqlLimit, the bind operation produces the following packages:
Table 1. Table of packages
  Package with first 100 SQL statements Package with next n SQL statements, with 100 < n < 200
Isolation level CS myPackageA1 myPackageB1
Isolation level RR myPackageA2 myPackageB2
Isolation level RS myPackageA3 myPackageB3
Isolation level UR myPackageA4 myPackageB4

The numbers designate the isolation level of the packages, and the letters designate the number of packages that are created due to the value of sqlLimit and the number of statements in the pureQueryXML file. The letters proceed down the English alphabet. If the letter z is reached and more packages need to be created, letters continue to be appended: AA, AB, AC, and so on. If the names become too long for your database, reduce the length of your root package name or increase the value of sqlLimit. You can also use the -forceSingleBindIsolation option to remove the number that indicates the isolation level, if you plan to create a package for a single isolation level.

There are situations in which the Configure utility exceeds the SQL statement limit when creating statement sets that represent DB2 packages. All SQL statements that manipulate a cursor must be in the same package as the DECLARE CURSOR statement for that cursor. For example, if an application runs fifteen different UPDATE statements that refer to the same cursor, all fifteen statements must be in the DB2 package where the DECLARE CURSOR statement for that cursor is located.

For the best performance of your application, do not set sqlLimit to a value higher than 400.

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 the Console view.

Feedback