Configure utility

With the pureQuery Configure utility, you can set several characteristics of the DB2® packages that you eventually create for the SQL statements in your pureQueryXML files. Characteristics you can set include the collection, version, and root package name of DB2 packages, and you can set the maximum number of SQL statements that are grouped in a package. XML schema validation can also be performed on input pureQueryXML files.

The characteristics that you set are added to your pureQueryXML file. When you run the StaticBinder utility to bind the SQL statements that are in a pureQueryXML file into DB2 packages, the StaticBinder utility reads these characteristics. pureQuery Runtime also uses information in the file such as the collection ID, package ID, version ID, and section number when running SQL statements statically.

Note: The pureQueryXML file specified by the -pureQueryXml option cannot be a pureQueryXML file that was created in the following ways:
  • The file was created to capture additional SQL statements when both the outputPureQueryXml and the pureQueryXml pureQuery Runtime properties were specified. When both properties are specified, the pureQueryXML file specified by the outputPureQueryXml property contains captured SQL statements that do not appear in the pureQueryXML file specified by the pureQueryXml property. Before configuring, you must merge multiple files. Use the file specified by the pureQueryXml property as the base file.
  • The file contains captured SQL data that was extracted from a repository and the active runtime group version contained pureQueryXML data. Before configuring, you must extract the pureQueryXML data from the runtime group version and merge multiple files. Use the file containing the pureQueryXML data as the base file.

This topic contains the following sections:

To understand the conventions that are used in the diagrams, see How to read syntax diagrams.

Configure utility options files

A Configure utility options file lists the pureQueryXML file or pureQueryXML files that you want the pureQuery Configure utility to process, and the options that tell the Configure utility how to process the pureQueryXML files. You can set some Configure utility options as default options that apply to all of the pureQueryXML files that you list in an options file. You can also set options for pureQueryXML files so that you can override the default options and set other options that are specific to individual pureQueryXML files.

In the command to run the Configure utility, you can specify the file to use with the -optionsFile option.

For example, a simple options file for the pureQuery Configure utility might look like this:
defaultOptions = -collection COLL01
C:\capture_files\capture_sales.pdqxml = -collection NULLID -rootPkgName SALES
C:\capture_files\capture_employee.pdqxml = -collection COLL02 -rootPkgName EMP
C:\capture_files\capture_orders.pdqxml = -rootPkgName ORDERS
In this example, the line that begins with defaultOptions specifies the collection ID for the DB2 packages that will eventually be created and bound. The next two lines override the default collection ID. For all three pureQueryXML files, the mandatory -rootPkgName option gives the root package name of the DB2 packages.
You can also include comments in options files by prefacing each line of a comment with a # symbol. If you need this symbol to appear in the value of one of the options, enclose the value in double-quotation marks, like this:
-pkgVersion "ver#1"
When you run the Configure utility from a command line and use an options file, pureQuery recognizes options in the following order of precedence:
  1. Options on the command line
  2. Options for individual pureQueryXML files in the specified options file
  3. Default options in the specified options file

Syntax for the command to run the Configure utility without using an options file

If you have only one pureQueryXML file, you can choose to use this command to specify the name of the pureQueryXML file and the options for the DB2 package.

Read syntax diagramSkip visual syntax diagram
                                     (1)   
>>-java--com.ibm.pdq.tools.Configure---------------------------->

>--+-----------------------------------+------------------------>
   |               .-NULLID----------. |   
   '- -collection--+-collection-name-+-'   

>--+-----------------------------+------------------------------>
   '- -markDDLForBind--+-TRUE--+-'   
                       '-FALSE-'     

>--+--------------------------------+--------------------------->
   '- -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--+-'   

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

>--+-------------------------------------------+---------------->
   |                      .------------------. |   
   |                      |    .-|------.    | |   
   |                      V    V        |    | |   
   '- -groupSQLByStrings----(----string-+--)-+-'   

>--+----------------------------------+------------------------->
   |                       .- -1----. |   
   '- -maxMissingSections--+-number-+-'   

>--+--------------------------------------+--------------------->
   '- -removeSQLInactiveForDays----days---'   

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

                                           (2)   
>--+-------------------------------------+---------------------->
   |                           .-FALSE-. |       
   '- -restoreInvalidSQLForce--+-TRUE--+-'       

>--+----------------------------------------------------------------+-->
   |                        .----------------------------------.    |   
   |                        V                                  |    |   
   '- -replaceLocations--"----(--original_name-->--new_name--)-+--"-'   

>--+--------------------------------------------------------------+-->
   |                      .----------------------------------.    |   
   |                      V                                  |    |   
   '- -replaceSchemas--"----(--original_name-->--new_name--)-+--"-'   

>--+--------------------------+--+------------------------+----->
   |                .-FALSE-. |  |             .-100----. |   
   '- -showDetails--+-TRUE--+-'  '- -sqlLimit--+-number-+-'   

>--+---------------------------------------+-------------------->
   '- -setPreStatusOfAllPkgs--+-AUTO-----+-'   
                              +-FINAL----+     
                              '-REQUIRED-'     

>--+---------------------------------+-------------------------->
   '- -setPostStatusOfAllPkgs--FINAL-'   

>--+-------------------------------------------------------------+-->
   '- -optionsFileForBind--+-bind-options-file-----------------+-'   
                           '-DEFAULT_IN_PUREQUERYXML_DIRECTORY-'     

>--+--------------------------+--+-----------------------+------>
   |                .-FALSE-. |  |                   (3) |   
   '- -validateXml--+-TRUE--+-'  '-| Trace options |-----'   

>-- -pureQueryXml--pureQueryXML-file--+--------+---------------><
                                      '- -help-'   

Notes:
  1. You can specify the options in any order.
  2. The -replaceLocations and -replaceSchemas options require that the JAR file pdqsqlparser.jar be in your classpath. The options are supported for DB2 for z/OS® Version 10 and DB2 for Linux, UNIX, and Windows Version 9.8. A warning is issued if an unsupported database is detected.
  3. For the syntax, see the description of these options.

Syntax for the command to run the Configure utility with an options file

If you have more than one pureQueryXML file, use the command and an options file to specify the names of the pureQueryXML files and the options for the DB2 packages. You can use the command and an options file even if you have only one pureQueryXML file.

Read syntax diagramSkip visual syntax diagram
>>-java--com.ibm.pdq.tools.Configure---------------------------->

>-- -optionsFile--file-name--+-----------------------+---------><
                             |                   (1) |   
                             '-| Trace options |-----'   

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

Syntax to specify default options in an options file for the Configure utility

This syntax diagram shows the default options that you can set for all of the pureQueryXML files that you list in an options file.

Read syntax diagramSkip visual syntax diagram
                     (1)   
>>-defaultOptions--=-------------------------------------------->

>--+-----------------------------------+------------------------>
   |               .-NULLID----------. |   
   '- -collection--+-collection-name-+-'   

>--+-----------------------------+------------------------------>
   '- -markDDLForBind--+-TRUE--+-'   
                       '-FALSE-'     

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

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

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

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

>--+-------------------------------------------+---------------->
   |                      .------------------. |   
   |                      |    .-|------.    | |   
   |                      V    V        |    | |   
   '- -groupSQLByStrings----(----string-+--)-+-'   

                                        (2)   
>--+----------------------------------+------------------------->
   |                       .- -1----. |       
   '- -maxMissingSections--+-number-+-'       

>--+----------------------------------------------------------------+-->
   |                        .----------------------------------.    |   
   |                        V                                  |    |   
   '- -replaceLocations--"----(--original_name-->--new_name--)-+--"-'   

>--+--------------------------------------------------------------+-->
   |                      .----------------------------------.    |   
   |                      V                                  |    |   
   '- -replaceSchemas--"----(--original_name-->--new_name--)-+--"-'   

>--+--------------------------------------+--------------------->
   '- -removeSQLInactiveForDays----days---'   

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

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

>--+--------------------------+--+------------------------+----->
   |                .-FALSE-. |  |             .-100----. |   
   '- -showDetails--+-TRUE--+-'  '- -sqlLimit--+-number-+-'   

>--+---------------------------------------+-------------------->
   '- -setPreStatusOfAllPkgs--+-AUTO-----+-'   
                              +-FINAL----+     
                              '-REQUIRED-'     

>--+---------------------------------+-------------------------->
   '- -setPostStatusOfAllPkgs--FINAL-'   

>--+-------------------------------------------------------------+-->
   '- -optionsFileForBind--+-bind-options-file-----------------+-'   
                           '-DEFAULT_IN_PUREQUERYXML_DIRECTORY-'     

>--+--------------------------+--+-----------------------+-----><
   |                .-FALSE-. |  |                   (3) |   
   '- -validateXml--+-TRUE--+-'  '-| Trace options |-----'   

Notes:
  1. You can specify the options in any order.
  2. The -replaceLocations and -replaceSchemas options require that the JAR file pdqsqlparser.jar be in your classpath. The options are supported for DB2 for z/OS Version 10 and DB2 for Linux, UNIX, and Windows Version 9.8. A warning is issued if an unsupported database is detected.
  3. For the syntax, see the description of these options.

Syntax to specify options for individual pureQueryXML files in an options file for the Configure utility

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--=----------------------------------------->

>--+-----------------------------------+------------------------>
   |               .-NULLID----------. |   
   '- -collection--+-collection-name-+-'   

>--+-----------------------------+------------------------------>
   '- -markDDLForBind--+-TRUE--+-'   
                       '-FALSE-'     

>--+--------------------------------+--------------------------->
   '- -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--+-'   

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

>--+-------------------------------------------+---------------->
   |                      .------------------. |   
   |                      |    .-|------.    | |   
   |                      V    V        |    | |   
   '- -groupSQLByStrings----(----string-+--)-+-'   

>--+----------------------------------+------------------------->
   |                       .- -1----. |   
   '- -maxMissingSections--+-number-+-'   

>--+--------------------------------------+--------------------->
   '- -removeSQLInactiveForDays----days---'   

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

                                           (2)   
>--+-------------------------------------+---------------------->
   |                           .-FALSE-. |       
   '- -restoreInvalidSQLForce--+-TRUE--+-'       

>--+----------------------------------------------------------------+-->
   |                        .----------------------------------.    |   
   |                        V                                  |    |   
   '- -replaceLocations--"----(--original_name-->--new_name--)-+--"-'   

>--+--------------------------------------------------------------+-->
   |                      .----------------------------------.    |   
   |                      V                                  |    |   
   '- -replaceSchemas--"----(--original_name-->--new_name--)-+--"-'   

>--+--------------------------+--+------------------------+----->
   |                .-FALSE-. |  |             .-100----. |   
   '- -showDetails--+-TRUE--+-'  '- -sqlLimit--+-number-+-'   

>--+---------------------------------------+-------------------->
   '- -setPreStatusOfAllPkgs--+-AUTO-----+-'   
                              +-FINAL----+     
                              '-REQUIRED-'     

>--+---------------------------------+-------------------------->
   '- -setPostStatusOfAllPkgs--FINAL-'   

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

Notes:
  1. You can specify the options in any order.
  2. The -replaceLocations and -replaceSchemas options require that the JAR file pdqsqlparser.jar be in your classpath. The options are supported for DB2 for z/OS Version 10 and DB2 for Linux, UNIX, and Windows Version 9.8. A warning is issued if an unsupported database is detected.

Descriptions of options

pureQueryXML-file = options
Use this format in an options file to specify the name of a pureQueryXML file and the options for the file. The file name must start the line in the file. The file name includes its absolute or relative path. The file name must have the extension .pdqxml or .xml.
The options that you specify on this line override the options that you specify on the defaultOptions line.
-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.

The Configure utility disables rowset cursors for any SQL statements for which pureQuery Runtime does not support rowset cursors, even if the IBM Data Server Driver for JDBC and SQLJ used rowset cursors for those statements.

If you do not specify this option and pureQuery Runtime supports rowset cursors for the statement, 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.

If the -groupSQLBySpecialRegisters option is specified, a warning is logged if a change to a special register attribute has occurred. No error is issued in this case.

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

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.

-groupSQLBySpecialRegisters TRUE|FALSE
Specifies whether the SQL statements are grouped into statement sets based on the values of the recorded special registers that are associated with the statement. The default value is FALSE, and SQL statements are not grouped by recorded special register values. If the value is TRUE, SQL statements are grouped into statement sets based on special register settings. Each SQL statement in the same statement set has the same recorded special register values when the SQL was captured. The following rules are applied when creating statement sets based on special registers:
  • SQL statements with a single set of recorded special register values are grouped into statement sets. A statement set is created for each different set of recorded special register values.
  • SQL statements with no recorded special register information are grouped into a single statement set.
  • SQL statements that are associated with more than one set of recorded special register values are grouped into a single statement set. An SQL statement can be associated with multiple sets of recorded special register values if the statement was issued several times by the application, and the special registers information changed between captures of the SQL statement.

When both the -groupSQLBySpecialRegisters and -groupSQLByStrings options are used to create statement sets, the statements are first grouped by special register values. For each group based on special registers, the SQL statements are grouped based on the values specified by the -groupSQLByStrings option. After the grouping is completed, the grouping based on the -sqlLimit is performed, if needed. After the statement sets are created, they are named.

If the pureQueryXML file contains a combination of named and unnamed packages and the -cleanConfigure option is set to FALSE, only the unnamed packages are grouped, and the grouping of the named packages is not changed. If the -cleanConfigure option is TRUE, then the SQL statements in all packages, named and unnamed, are grouped.

If you are running SQL statements statically, you can use the -groupSQLBySpecialRegisters option and -optionsFileForBind option to help you bind packages to a DB2 database.

For information about the special register information recorded by pureQuery Runtime, see the related links at the end of this topic.

-groupSQLByStrings (String1|String2|...)...(StringN|StringM|...)
Specifies grouping SQL statements based on substring matches by using the strings specified by the option. If the option is not specified, grouping occurs based on other options that control grouping.

The strings enclosed in each set of parentheses are used to search for and group the SQL statements into a statement set. Multiple strings can be used to assign an SQL statement to statement set by separating the strings with a vertical bar ( | ). A warning is logged if the same string appears multiple times or if a string is a substring of another string.

An SQL statement is assigned to a statement set if the statement contains the string. If multiple strings are used to create a statement set, the SQL statement is assigned to the statement set if the statement contains any of the strings.

When creating the statement sets, the sets are created in the order listed by the groupSQLByStrings option, and the search for matching statements is performed in the order listed by the option. For example, the option specifies the value (Table1) (Table2 | Table3), the Configure utility creates the following statement sets for a pureQueryXML file:
  • SQL statements that contain the string Table1 are assigned to a statement set.
  • SQL statements that contain either the string Table2 or the string Table3 are assigned to the second statement set.
  • SQL statements that do not contain any of the specified strings are assigned to a third statement set.

When both the -groupSQLBySpecialRegisters and -groupSQLByStrings options are used to create statement sets, the statements are first grouped by special register values. Within each group that has special registers, the SQL statements are grouped based on the values that are specified by the -groupSQLByStrings option. After the grouping is completed, the -sqlLimit is applied if needed, and the statement sets that are created are named.

If the pureQueryXML file contains a combination of named and unnamed packages and the -cleanConfigure option is set to FALSE, only the unnamed packages are grouped, and the grouping of the named packages is not changed. If the -cleanConfigure option is TRUE, then the SQL statements in all packages, named and unnamed, are grouped.

For examples of grouping SQL statements by strings, see Examples.

-markDDLForBind
Specifies the value that the Configure utility sets for the isBindable attributes for all DDL statements in a pureQueryXML file. The Configure utility also sets this value for the isBindable attribute on statement sets that contain only DDL statements.

Valid values are the case-independent strings TRUE and FALSE. There is no default value.

TRUE
When the value of isBindable is TRUE, the corresponding DDL statement or DDL statement set is bound when the StaticBinder utility processes the pureQueryXML file.
FALSE
When the value of isBindable is FALSE, the corresponding DDL statement or DDL statement set is not bound when the StaticBinder utility processes the pureQueryXML file.

If you do not specify this option, the Configure utility checks whether isBindable is TRUE for any of the DDL statements that are in unnamed statement sets in the pureQueryXML file. If isBindable is TRUE for at least one of those DDL statements, when the Configure utility combines the DDL statements into a single, named DDL statement set, the utility sets the isBindable attribute for the DDL statement set to TRUE.

When the StaticBinder utility encounters the value TRUE for the isBindable attribute of statement sets, it attempts to bind any statements in that statement set that have the value TRUE for their isBindable attribute.

-optionsFile file-name
The name of the file, including its absolute or relative path, that lists the pureQueryXML files that you want to modify with metadata for the pureQuery StaticBinder utility.
-maxMissingSections number
Specifies when the Configure utility removes unnecessary gaps between section numbers in a statement set. Section numbers are assigned to SQL statements. When the pureQuery StaticBinder utility binds the pureQueryXML file, the unnecessary gaps between section numbers become unused section holes in the DB2 packages created by the bind operation.

The value number specifies the maximum number of unnecessary gaps that are allowed in a statement set before the Configure utility reassigns the section numbers. The value number can be an integer -1 or larger. The default value is -1, the Configure utility does not reassign section numbers. For example, if the value of number is either 0 (zero) or 1, all unnecessary gaps between section numbers are removed.

The Configure utility reassigns section numbers only on a statement sets that is named and when the configureStatus attribute for the set is not FINAL.

When you use the option -removeInvalidSQL or the option -removeSQLInactiveForDays, some SQL statements might be removed from statement sets. The Configure utility does not automatically reassign section numbers for the remaining SQL statements. Reassigning section numbers requires any corresponding database packages to be rebound.

If you specify the option -removeInvalidSQL or the option -removeSQLInactiveForDays with the -maxMissingSections option, the SQL statements are removed before the section numbers are reassigned.

-optionsFileForBind bind-options-file|DEFAULT_IN_PUREQUERYXML_DIRECTORY
Specifies that the Configure utility generate a StaticBinder options file. The options file contains bind options and other information based on the SQL statements, statement sets, and DB2 special register information in the pureQueryXML files. If the file exists, the file is replaced with the new file.
This option takes the following values:
bind-options-file
Species the name of the file, including its absolute or relative path.
DEFAULT_IN_PUREQUERYXML_DIRECTORY
Specifies that the file observed.bindProps is created in the directory that contains the pureQueryXML file.

If this value is specified on the defaultOptions line of a Configure utility options file, all pureQueryXML files listed in the file must be in a single directory.

The bind options and other information in the file help you specify a set of bind options so that when an SQL statement is run statically, the behavior is similar to when it is issued from the application and run dynamically. You use the bind options with the pureQuery StaticBinder utility to create packages on a DB2 database and bind the packages on the database.

The StaticBinder options file contains the following types of information for the statement sets and SQL statements in the pureQueryXML file:
  • Bind options for the statement sets.
  • Information and warnings for the SQL statements and statement sets.

    The Configure utility adds comments with information about the statement sets and special register values sets referenced in the file.

    The Configure utility adds the StaticBinder option -configureWarning with warnings. The warnings indicate the reasons why SQL statements might not behave as expected when you run them statically after you bind packages to the database from the statement set.

For information about the StaticBinder options file created by the Configure utility and the special register information recorded when SQL statements are captured, see the related links at the end of this topic.

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

-pureQueryXml pureQueryXML-file
The name of the pureQueryXML file. This file must have the extension .pdqxml or .xml. You must provide the full or relative path to the file.
-removeSQLInactiveForDays days
Specifies the number of days after which an unused SQL statement is removed based the last used timestamp of the statement. If the days variable is 0, all SQL statements are written to the output file. The default setting is to write all SQL statements to the output file. If the value of days is a positive number, the Configure utility does not write SQL statements to the output file if the last used timestamp of the statement is the number of days before the current date or earlier, inclusive. If the days variable is 1, SQL statements are not written if the timestamp is the previous day or earlier.

For example, if the days variable is 5, and the current date is 11/25, November 25, SQL statements with a timestamp 11/20, November 20 and earlier, are not written.

The timestamp is updated when pureQuery Runtime captures the SQL statement or when you run the Merge utility with incremental captures files.

If the SQL statement does not have a timestamp, the SQL statement is written to the output file. For example, a previous version pureQueryXML file might not have timestamp information. If you configure a pureQueryXML file that does not have timestamp information, version 3 or earlier, the option is ignored, all SQL statements are written to the output file, and a message is displayed.

A message is written to the log file for the SQL statements that are not written to the output pureQueryXML file. If the -showDetails option is true, all of the statements that have not been written to the output file are displayed.

-replaceLocations "(original_name1>new_name1 ) ... (original_nameN>new_nameN )"
Specifies the replacement of existing database location names in the pureQuery file with new location names. The existing name is separated from the new name by a right angle bracket (>). Each pair of location names is enclosed in parentheses. The replacement list is enclosed in double quotation mark (") characters. Double quote characters used to delimit a name must be prefixed with a \ character. The following example option replaces two database location names:
-replaceLocations "(SAMPLE > SAMPLE1) (STLEC1 > STLEC2)"
The following example uses double quote characters:
-replaceLocations "(\"test1\">\"prod1\")"

Name replacement is a single pass process. For example, if you specify the replacement string "(S1>S2) (S2>S3)", the occurrences of S1 are replaced with S2 and the original occurrences of S2 are replaced by S3. The Configure utility does not replace S1 with S2 in one pass and then replace S2 with S3 in another pass. For name replacement examples, see the example at the end of this topic.

This option is supported for only DB2 for z/OS Version 10 and DB2 for Linux, UNIX, and Windows Version 9.8. The Configure utility does not prevent location name replacement with other versions of the supported databases. The utility displays a warning when you specify a pureQueryXML file that is used with later versions of the database. For earlier database versions, a warning is not displayed. The Configure utility determines the database and version from information in the pureQueryXML file.

Before you use the Configure utility with the -replaceLocations option, the JAR file pdqsqlparser.jar must be added to your classpath. The JAR file is in the pureQuery Runtime installation directory lib/sqlparser.

The value of the configureStatus attribute affects how the Configure utility processes the statement sets that are affected by the -replaceLocations option.
  • If the value of configureStatus is REQUIRED, the names are changed to the values that are specified by the replace option. The statement set consistency token is updated.
  • If the value of configureStatus is AUTO, the names are changed to the values that are specified by the replace options. The statement set consistency token is updated if a name replacement is performed.
  • If the value of configureStatus is FINAL, the names are not changed. The Configure utility prints a message stating that the utility did not change the database location or schema name for a SQL statement because the value of the configureStatus is FINAL for the statement set that contains the statement.
The value of the -cleanConfigure and -setPreStatusOfAllPkgs options affect the replacement of the names.
  • In case of -cleanConfigure set to true, the value of -setPreStatusOfAllPkgs is ignored and the replacement is done with the values of the options -replaceLocations and -replaceSchemas.
  • In case of -cleanConfigure false, the replacement is done for only the statement sets that have the configureStatus of REQUIRED and AUTO. Setting the value of -setPreStatusOfAllPkgs to FINAL will prevent the replacement from being done in those named statement sets. However identifiers in all unnamed statement sets are replaced as usual

The values of other options have no effect on the -replaceLocations and -replaceSchemas options. The replacement of names is done prior to applying other options, apart from -cleanConfigure and -setPreStatusOfAllPkgs.

-replaceSchemas "(original_name1>new_name1 ) ... (original_nameN>new_nameN )"
Specifies the replacement of existing schema names in the pureQuery file with new schema names. The existing name is separated from the new name by a right angle bracket (>). Each pair of schema names is enclosed in parentheses. The replacement list is enclosed in double quotation mark (") characters. Double quote characters used to delimit a name must be prefixed with a \ character. The following example option replaces two schema names:
-replaceSchemas "(MYSCH1 > MYSCH2) (MYSCH3 > MYSCH4)"
The following example uses double quote characters:
-replaceSchemas "(\"schmTest\">\"schmProd\")"

Name replacement is a single pass process. For example, if you specify the replacement string "(S1>S2) (S2>S3)", the occurrences of S1 are replaced with S2 and the original occurrences of S2 are replaced by S3. The Configure utility does not replace S1 with S2 in one pass and then replace S2 with S3 in another pass. For name replacement examples, see the example at the end of this topic.

This option is supported for only DB2 for z/OS Version 10 and DB2 for Linux, UNIX, and Windows Version 9.8. The Configure utility does not prevent schema name replacement with other versions of the supported databases. The utility displays a warning when you specify a pureQueryXML file that is used with later versions of the database. For earlier database versions, a warning is not displayed. The Configure utility determines the database and version from information in the pureQueryXML file.

Before you use the Configure utility with the -replaceSchemas option, the JAR file pdqsqlparser.jar must be added to your classpath. The JAR file is in the pureQuery Runtime installation directory lib/sqlparser.

See the -replaceLocations option for information about the affects of the value of configureStatus attribute and the -cleanConfigure and -setPreStatusOfAllPkgs options.

-removeInvalidSQL TRUE|FALSE
Specifies whether the Configure utility removes the SQL statements that are marked as invalid in the pureQueryXML file.

Valid values are TRUE and FALSE. The default value is FALSE, invalid SQL statements are not removed from the pureQueryXML file.

TRUE
The Configure utility removes the SQL statements that are marked invalid from the pureQueryXML file.

If value of the configureStatus attribute for the statement set is FINAL, invalid SQL statements in the statement set are not removed. A warning message is displayed.

An error is displayed if you also specify the -restoreInvalidSQLForce option with the value TRUE.

FALSE
If the value is FALSE or if the option is not specified, no action is taken on SQL statements that are marked invalid in the pureQueryXML file.

See the pureQuery StaticBinder utility option -statementBindError for information about SQL statements that are marked invalid.

-restoreInvalidSQLForce TRUE|FALSE
Specifies whether the Configure utility restores SQL statements that are marked invalid in a pureQueryXML file. After the SQL statements are restored, they can be used with pureQuery Runtime. You use this option to restore SQL statements that have been mistakenly marked as invalid.

Valid values are TRUE and FALSE. The default value is FALSE, SQL statements that are marked invalid are not restored.

TRUE
Configure utility restores invalid SQL statements for normal use by pureQuery Runtime.

An error is displayed if you also specify the -removeInvalidSQL option with the value TRUE.

Note: The Configure utility restores SQL statements in a statement set even if the configureStatus attribute for the set is FINAL.
FALSE
If the value is FALSE or if the option is not specified, no changes are made to invalid SQL statements.

When restoring invalid SQL statements, the Configure utility updates invalid SQL statements so that they are no longer marked as invalid. The Configure utility also makes other changes in the pureQueryXML file so that pureQuery Runtime recognizes the SQL statements as valid statements. For example, SQL statements are assigned section numbers.

See the pureQuery StaticBinder utility option -statementBindError for information about SQL statements that are marked invalid.

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

-setPreStatusOfAllPkgs AUTO|FINAL|REQUIRED
Specifies a package status. The Configure utility sets all statement sets to the specified status before processing the set. The value can be set to AUTO, FINAL, or REQUIRED.
If the -cleanConfigure option is set to FALSE or is not specified, the following processing occurs when a -setPreStatusOfAllPkgs value is specified:
  • AUTO: Unnamed statement sets are configured as usual. Named sets are validated and configured only if needed.
  • FINAL: Sets the status of all named statement sets is set to FINAL. The status on any unnamed packages is ignored and the package is configured. Named statement sets are validated and warnings are printed.
  • REQUIRED: For unnamed packages, this value is the default. For named statement sets, a configuration is performed and any new option values that are specified for the options -collection, -forceSingleBindIsolation, -markDDLForBind, pkgVersion, and -allowStaticRowsetCursors are applied to the named packages.

If the -cleanConfigure option is set to TRUE, the -setPreStatusOfAllPkgs option is ignored. No message is logged.

-setPostStatusOfAllPkgs FINAL
Specifies that after processing, the Configure utility sets the status of all statement sets in the output pureQueryXML file to FINAL.
The following occurs if the value of the -setPostStatusOfAllPkgs option is FINAL:
  • If the -cleanConfigure option is set to FALSE or is not specified, all unnamed statement sets are configured, all named statement sets are validated and configured as needed, and the status of all packages is set to FINAL.
  • If the -cleanConfigure option is set to TRUE, configure all SQL statements, and set the status of all statement sets to FINAL.
-showDetails TRUE|FALSE
Specifies whether the Configure utility displays a summary of its activity. The default value is FALSE.
-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 System.err.
-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. The default value is FALSE.

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

If a pureQueryXML file fails schema validation, the configure action does not complete for that file. The first schema validation error and the configure failure for the file are reported.

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

Validation errors are printed as part of the Configure utility output.

-help
Displays summary usage information.
Not valid in an options file.

Output of the Configure utility

The Configure utility generates from 1 to 4 additional files for every pureQueryXML file that it updates.
  • The first generated file, which the Configure utility always produces, is a copy of the pureQueryXML file that you provided. If the name of the pureQueryXML file is sampleJDBC.pdqxml, the copy is sampleJDBC.pdqxml.org, where "org" is an abbreviation of "original."
  • The second file is generated if you specify a version ID with the -pkgVersion option when you run the Configure utility. If the Configure utility changes the file, it appends the version ID to the name of a copy of the pureQueryXML file. For example, -pkgVersion Ver1.0 -pureQueryXml sampleJDBC.pdqxml produces a file named sampleJDBC.pdqxml_Ver1.0. If the Configure utility does not change the file, it does not create a copy of the file with the version ID.
    You can use versioning if you want to be able to switch to earlier versions of the DB2 packages that are associated with your application. For example, suppose that you have three pureQueryXML files:
    • sampleJDBC.pdqxml and sampleJDBC.pdqxml_3.0, which are identical.
    • sampleJDBC.pdqxml_2.5
    • sampleJDBC.pdqxml_2.0
    You also have corresponding DB2 packages that use the same version numbers. After running your application with the most recent DB2 package, which is at version 3.0, you decide that you want to revert to the previous package, which is at version 2.5. You replace the current sampleJDBC.pdqxml file with a copy of sampleJDBC.pdqxml_2.5, and then you run your application. Then, if you want to revert to version 3.0 of your DB2 package, you simply replace sampleJDBC.pdqxml with a copy of sampleJDBC.pdqxml_3.0.
  • The third file is generated if the pureQueryXML file contains DDL statements. With the exception of DECLARE GLOBAL TEMPORARY TABLE statements, the Configure utility copies any DDL statements that are in the pureQueryXML file into a text file with the file type .ddl. This file allows a database administrator to run the statements at the target DBMS before binding the DML statements that reference those DDL statements. The database objects that the DDL statements create or alter must be present so that DML statements that reference those objects can be bound. You might need to edit this .ddl file. For example, if the file contains DROP statements after a matching CREATE TABLE or CREATE VIEW statement, then the DROP statements must be removed.
  • The fourth file is generated if you specify the -optionsFileForBind option. The Configure utility generates a StaticBinder options file that contains DB2 bind options. The Configure utility also adds comments to the file that contain warnings and other information about the contents of the file. The bind options can be used with the pureQuery StaticBinder utility.

To see the updates that the Configure utility made to a pureQueryXML file, you can open that file in an editor.

You can capture more SQL statements to a pureQueryXML file that you already processed with the Configure utility. When you use the Configure utility to process the new SQL statements in the pureQueryXML file, specifying the -cleanConfigure option affects how existing SQL statements and statement sets are processed:
  • With the default value for the -cleanConfigure option, the Configure utility attempts to preserve the contents of existing, previously configured and bound, statement sets in the pureQueryXML file. A message is displayed when the information cannot be preserved.

    Use this setting to continue using the DB2 packages created from the SQL statements in the pureQueryXML file.

  • With the value of the -cleanConfigure option set to TRUE, the Configure utility does not preserve the statement set information.

The XML attribute isBindable in the pureQueryXML file

The pureQueryXML file is an XML file that contains the SQL statements and the related information captured by pureQuery Runtime. In the file, the SQL statements are grouped into one or more statement sets. The XML attribute named isBindable is an attribute of statement sets and individual SQL statements. The isBindable attribute controls whether an SQL statement is run statically on a DB2 database. The value of the attribute can be either true or false and controls the following actions:
  • Whether to perform a bind operation. When you run the StaticBinder utility to bind packages to a DB2 database, the utility uses the value of the isBindable attribute determine whether to bind the packages with the SQL statement or statement set.
    • If the value is true for the statement set, StaticBinder performs the binding operation.
    • If the value is false for the statement set, the operation is not performed.
    • If the value is true for the statement set and false for an SQL statement, the bind operation is performed but the statement is excluded.
  • Whether to run the SQL statement statically. When the value of the pureQuery Runtime property executionMode is set to STATIC, and the value of the isBindable attribute is true for the statement set and the SQL statement, pureQuery Runtime attempts to run the statement statically.

    If the value of the isBindable attribute is true for the statement set and false for an SQL statement in the set, the value of the pureQuery Runtime property named allowDynamicSQL, controls whether pureQuery Runtime tries to run the statement dynamically or returns an error.

    If the value of the isBindable attribute is false for the statement set, the value of the pureQuery Runtime property allowDynamicSQL, controls whether pureQuery Runtime tries to run an SQL statement in the set dynamically or returns an error.

By using the pureQueryXML editor in the workbench, you can change the value of the isBindable attribute in a pureQueryXML file for a statement set and for a statement.

Recommendation: IBM strongly recommends that you edit the pureQueryXML files by using only the workbench. If you edit a pureQueryXML file outside of the workbench, you risk making the file unusable.

If you manually edit a pureQueryXML file, be sure that you create a backup copy of the file before you edit. Use the -validateXml option with the pureQuery Merge, Configure, or StaticBinder utilities to perform XML schema validation on the input pureQueryXML files.

If you must edit a pureQueryXML file outside of the workbench, in the start tag of the element named statementSet of the statement set in which you are editing, set the value of the configureStatus attribute to REQUIRED. After you finish editing the file, run the Configure utility on the file.

The following XML fragment sets the value of the configureStatus attribute to REQUIRED:
<statementSet configureStatus="REQUIRED">

If you are editing a pureQueryXML file outside the workbench, you can change the value of the isBindable attribute for a statement set or a statement.

To change the isBindable attribute for a statement set, you change the value of the isBindable attribute for the package element that is the child element of the statement set element.

To change the isBindable attribute for an SQL statement, you change the value of the isBindable attribute for the statementAttributes element that is the child element of the statement element.

The following XML fragment is part of a statement set from a pureQueryXML file:
<statementSet configureStatus="AUTO">
      <package collection="NULLID" consistencyToken="0000012551b7579f" isBindable="true" 
            isDDLPackage="false" name="pureQu" version=""/>
      <statements>
         <statement id="1" sectionNumber="1">
            <statementDescriptor>
               <prepareSql>SELECT CID FROM CUSTOMER WHERE CID &gt; 1002 </prepareSql>
               <statementType>QUERY</statementType>
               <resultSetMetadata>
                  <column columnName="CID" columnNameX="CID" databaseName="XMLTEST" 
                      databaseSchema="TEST " db2type="492" length="8" nullable="false" 
                      tableName="CUSTOMER"/>
               </resultSetMetadata>
               <statementAttributes cursorName="DB_PDQ_SPC2" isBindable="true" 
                    prepareOptions="FOR READ ONLY " resultSetConcurrency="CONCUR_READ_ONLY" 
                    resultSetHoldability="HOLD_CURSORS_OVER_COMMIT" 
                    resultSetType="TYPE_FORWARD_ONLY"/>
               <processedSql />
            </statementDescriptor>
            <statementMetadata>
               <traceInfo>
                  <sqlDefinitionStackTraces>
                     <trace>
          . . .
</statementSet>

In the following XML fragment from the example, the isBindable attribute is in the start tag of the package element. The value is true.

<package collection="NULLID" consistencyToken="0000012551b7579f" isBindable="true" 
     isDDLPackage="false" name="pureQu" version=""/>

The example statement set contains the SQL statement SELECT CID FROM CUSTOMER WHERE CID > 1002. In the following XML fragment, the isBindable attribute for the SQL statement is an attribute of the statementAttributes element. The statementAttributes element is a child element of the statement element that contains the SQL statement. The value is true.

<statementAttributes cursorName="DB_PDQ_SPC2" isBindable="true" 
      prepareOptions="FOR READ ONLY " resultSetConcurrency="CONCUR_READ_ONLY" 
      resultSetHoldability="HOLD_CURSORS_OVER_COMMIT" 
      resultSetType="TYPE_FORWARD_ONLY"/>

DB2 special registers

When pureQuery Runtime captures SQL statements running against a DB2 database, some DB2 special register values are tracked and recorded with the SQL statement. The recorded special register values are values that are commonly set and that might affect the behavior of the SQL statement if they are changed.

The Configure utility displays special register activity recorded in the pureQueryXML file as part of the Configure utility activity.

To create statement sets based on the special register information that was recorded with the SQL statements, specify the -groupSQLBySpecialRegisters option.

To create a StaticBinder options file that contains bind information based on the special register information that is recorded when the SQL statements were captured, specify the -optionsFileForBind option. You can create a StaticBinder options file, even if you did not specify the -groupSQLBySpecialRegisters option to create statement sets based on SQL statement special register information. For information about the file and the special register information recorded by pureQuery Runtime, see the related links at the end of this topic.

Special register information in the pureQueryXML file

When pureQuery Runtime captures an SQL statement in a pureQueryXML file, it records special register information for the statement. In the file, the special register information is stored in the XML element named specialRegValuesSet.

The specialRegValuesSet element contains one or more instances of the XML element named specialRegValue. The attribute named specialRegValueId is an attribute of the specialRegValue element. The value of the specialRegValueId attribute is the ID of the specialRegValue element.

The specialRegValue element also contains a set of special register information that is recorded when an SQL statement is captured. The captured SQL statements are stored in an instance of the XML element named statementSet. Each statementSet element contains one or more instances of an XML element named statement. Each statement element contains SQL information about the captured SQL statement.

The value of the specialRegValueId attribute is used to relate the special register information in the specialRegValue element to the SQL statements. pureQuery Runtime specifies the value of the specialRegValueId attribute as the value of an attribute of the element named statementAttributes of the SQL statement.

For information about the special register information that is recorded by pureQuery Runtime, see the related links at the end of this topic.

The following XML fragment is an example of the element specialRegValuesSet from a pureQueryXML file:

<specialRegValuesSet>
    <specialRegValue specialRegValueId="0" 
         specialRegisterVectorValue="UNTRACKED_SET: FALSE|
         SCHEMA: MYTEST |
         PATH: &quot;SYSIBM&quot;,&quot;SYSFUN&quot;,&quot;SYSPROC&quot;,
            &quot;SYSIBMADM&quot;,&quot;MYTEST&quot;|
         SQLID: MYTEST |
         DECFLOAT ROUNDING: ROUND_HALF_EVEN"/>
    <specialRegValue specialRegValueId="1" 
         specialRegisterVectorValue="UNTRACKED_SET: FALSE|
         SCHEMA: DB2ADMIN|
         PATH: &quot;SYSIBM&quot;,&quot;SYSFUN&quot;,&quot;SYSPROC&quot;,
            &quot;SYSIBMADM&quot;,&quot;DB2ADMIN&quot;|
         SQLID: DB2ADMIN|
         DECFLOAT ROUNDING: ROUND_HALF_EVEN"/>
</specialRegValuesSet> 
The following fragment of a pureQueryXML file shows part of the statement element of an SQL statement in a pureQueryXML file.
<statement id="1" sectionNumber="1">
     <statementDescriptor>
        <prepareSql>SELECT count(EMPNO) FROM EMPLOYEE WHERE WORKDEPT NOT LIKE 'E21' </prepareSql>
        <statementType>QUERY</statementType>
        <resultSetMetadata>
           <column columnName="1" databaseName="SAMPLE" db2type="496" length="4" nullable="false" 
               unnamedColumn="1"/>
        </resultSetMetadata>
        <statementAttributes cursorName="DB_PDQ_SPC5" 
            isBindable="true" prepareOptions="FOR READ ONLY " resultSetConcurrency="CONCUR_READ_ONLY" 
            resultSetHoldability="HOLD_CURSORS_OVER_COMMIT" resultSetType="TYPE_FORWARD_ONLY" 
            specialRegValuesUsed="0,1"/>
        <processedSql />
     </statementDescriptor>
     . . .
</statement>

In the example, the value of the specialRegValuesUsed attribute for the statementAttributes element specifies two the special register set IDs. The values of the set IDs are 0 and 1. pureQuery Runtime captured the SQL statement multiple times. When pureQuery captured the statement, two different sets of special register information were recorded.

Example of running the Configure utility on two files for the first time

Suppose that you have two pureQueryXML files, one for each connection that your non-pureQuery API application makes. The names of the files are capture_sales.pdqxml (which contains 450 SQL statements) and capture_employee.pdqxml (which also contains 450 SQL statements, 50 of which are DDL statements).

For these files, you want to supply the following values to the Configure utility when you run the utility for the first time on these files:

Table 2. Example values for the Configure utility
Options for the Configure utility capture_sales.pdqxml capture_employee.pdqxml
-collection first second
-markDDLForBind FALSE FALSE
-pkgVersion 1.0 1.0
-rootPkgName PK001S PK005E
-sqlLimit 200 200
In this situation, there are two methods that you can use to run the Configure utility.
  • Run the Configure utility twice and supply a different .pdqxml file each time:
    java com.ibm.pdq.tool.Configure -collection first -pkgVersion 1.0 -rootPkgName PK001S \
    -sqlLimit 200 -pureQueryXml C:\capture_files\capture_sales.pdqxml
    java com.ibm.pdq.tool.Configure -collection second -pkgVersion 1.0 -rootPkgName PK005E \
    -sqlLimit 200 -pureQueryXml C:\capture_files\capture_employee.pdqxml
  • Run the Configure utility once and supply an options file in which the .pdqxml files are listed.

    The command might look like this command:

    java com.ibm.pdq.tool.Configure -optionsFile myOptionsFile.txt

    The options file, which can have any name and extension that your operating system supports, hasthese entries:

    defaultOptions = -pkgVersion 1.0 -sqlLimit 200
    
    C:\capture_files\capture_sales.pdqxml = -collection first -rootPkgName PK001S
    
    C:\capture_files\capture_employee.pdqxml = -collection second -rootPkgName PK005E
No matter which method you use, the Configure utility updates the two .pdqxml files with the values that you specified for the different options. The Configure utility also generates the following files:
Files that are based on capture_sales.pdqxml
capture_sales.pdqxml.org

capture_sales.pdqxml_1.0

Files that are based on capture_employee.pdqxml
capture_employee.pdqxml.org

capture_employee.pdqxml_1.0

capture_employee.ddl

Examples of creating statement sets with the -groupSQLByStrings option

The following examples assume that the pureQueryXML file contains the following three SQL statements:
SELECT * FROM TABLE3
SELECT * FROM TABLE2
SELECT * FROM TABLE1
If the value for the -groupSQLByStrings option is (TABLE3) (TABLE2) (TABLE1), the statements are assigned to the following statement sets:
Statement set 1: 
    SELECT * FROM TABLE3
Statement set 2: 
    SELECT * FROM TABLE2
Statement set 3: 
    SELECT * FROM TABLE1
If the value is (TABLE1) (TABLE3|TABLE2), the statements are assigned to the following statement sets:
Statement set 1: 
    SELECT * FROM TABLE1
Statement set 2: 
    SELECT * FROM TABLE2
    SELECT * FROM TABLE3
If the value is (TABLE1|TABLE2) (TABLE3|TABLE2), the statements are assigned to the following statement sets:
Statement set 1: 
    SELECT * FROM TABLE1, 
    SELECT * FROM TABLE2
Statement set 2: 
    SELECT * FROM TABLE3
Note: Using the same table name in multiple statement set definitions is not recommended, and a warning message is logged.

Updating schema names and database locations

Name replacement is a single pass process. For example, if you specify the replacement string "(S1>S2) (S2>S3)", the occurrences of S1 are replaced with S2 and the original occurrences of S2 are replaced by S3. The Configure utility does not replace S1 with S2 in one pass and then replace S2 with S3 in another pass.

If you specify a replacement string similar to "(SCH1>SCH2) (SCH2>SCH3)", you might get unexpected results when you configure a pureQueryXML file multiple times. The first time you configure, the name SCH1 is changed to SCH2, and the name that is originally SCH2 in the file is changed to SCH3. The second time you configure the file, the name SCH2 is changed to SCH3, including the instances of the name SCH2 that were changed the first time you configured the file. All names that were originally SCH1 or SCH2 in the file are changed to SCH3.

If you want to perform a name replacement such as replacing schema names SCH1 to SCH2 and then SCH2 to SCH1, run the Configure utility twice. First run the utility with the option -replaceSchemas "(SCH1>SCH2)" and then the utility again with the option -replaceSchemas "(SCH2>SCH1)".

Name replacement examples

The Configure utility can distinguish between the identifier and the character string literal. For example, a pureQueryXML file contains the following SQL statement. In the statement, the same identifier appears as a schema name in a character string literal:
INSERT INTO "schm0".table0 (col1, col2, col3) values (1, 'schm0', 'col3')
If you specify -replaceSchemas "(\"schm0\">\"prodSch\")", the utility replaces the schema name but not the literal:
INSERT INTO "prodSch".table0 (col1, col2, col3) values (1, 'schm0', 'col3')
If the replacement names contain space characters, the utility trims the space characters within the quotes For example, the following two -replaceLocations options are identical:
-replaceLocations " (   sample >   testDB ) "
-replaceLocations "(sample > testDB)"
For delimited schema and database location names in a SQL statement, the utility can change the schema name appearing in following two statements:
SET CURRENT SCHEMA = "schm0"
SET CURRENT SCHEMA = 'schm0';
With a replacement string "('schm0'>'prodSchm')" or "(\"schm0\">\"prodSchm\")", both of the names are replaced preserving the quotes:
SET CURRENT SCHEMA = "prodSchm"
SET CURRENT SCHEMA = 'prodSchm';
If the following SQL statement is in a pureQueryXML file for a DB2 for Linux, UNIX and Windows database:
SET PACKAGE PATH = 'schema1, schema2'
The Configure utility recognizes the two names as schema names and replaces them if required.
However, if this SQL statement is in pureQueryXML file for a DB2 for z/OS database, value abc is a collection ID and is not changed.
SET PACKAGE PATH = 'abc'
For the following SET PATH statement, the value schema1 is a schema name for DB2 for z/OS and for DB2 for Linux, UNIX, and Windows.
SET PATH = 'schema1';
The name is changed by the utility if required.

Feedback