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 a DB2 package, the StaticBinder utility reads these characteristics.
This topic contains the following sections:
To understand the conventions that are used in the diagrams, see How to read syntax diagrams.
To see the updates that the Configure utility made to a pureQueryXML file, you can open that file in an editor.
Use this setting to continue the DB2 packages created from the SQL statements in the pureQueryXML file.
When pureQuery Runtime captures SQL statements running against a DB2 database, some DB2 special register values are tracked and captured with the SQL statement. The captured special register values are the values that are commonly set and that might affect data returned by the SQL statement if they are changed.
To display special register activity captured in the input pureQueryXML file as part of the Configure utility summary activity, set the -showDetails option to true.
An 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 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.
defaultOptions = -collection COLL01 -traceFile C:\logs\configure.txt -traceLevel ALL 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 ORDERSIn this example, the line that begins with defaultOptions specifies the collection ID for the DB2 packages that will eventually be created and bound. The line also specifies the file in which to log messages. 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.
-pkgVersion "ver#1"
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.
(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-. | '- -showDetails--+-TRUE--+-' >--+---------------------------------------+--------------------> | .-FALSE-. | '- -forceSingleBindIsolation--+-TRUE--+-' >--+------------------------+-----------------------------------> | .-100----. | '- -sqlLimit--+-number-+-' >--+--------------------------------------+---------------------> '- -removeSQLInactiveForDays----days---' >--+---------------------------------------+--------------------> '- -setPreStatusOfAllPkgs--+-AUTO-----+-' +-FINAL----+ '-REQUIRED-' >--+---------------------------------+--------------------------> '- -setPostStatusOfAllPkgs--FINAL-' >--+-----------------------------------------+------------------> | .-FALSE-. | '- -groupSQLBySpecialRegisters--+-TRUE--+-' >--+-------------------------------------------+----------------> | .------------------. | | | .-|------. | | | V V | | | '- -groupSQLByStrings----(----string-+--)-+-' >--+--------------------------+--+-----------------------+------> | .-FALSE-. | | (2) | '- -validateXml--+-TRUE--+-' '-| Trace options |-----' >-- -pureQueryXml--pureQueryXML-file--+--------+--------------->< '- -help-'
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.
>>-java--com.ibm.pdq.tools.Configure----------------------------> >-- -optionsFile--file-name--+-----------------------+--------->< | (1) | '-| Trace options |-----'
This syntax diagram shows the default options that you can set for all of the pureQueryXML files that you list in an options file.
(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-. | '- -showDetails--+-TRUE--+-' >--+---------------------------------------+--------------------> | .-FALSE-. | '- -forceSingleBindIsolation--+-TRUE--+-' >--+------------------------+-----------------------------------> | .-100----. | '- -sqlLimit--+-number-+-' >--+--------------------------------------+---------------------> '- -removeSQLInactiveForDays----days---' >--+---------------------------------------+--------------------> '- -setPreStatusOfAllPkgs--+-AUTO-----+-' +-FINAL----+ '-REQUIRED-' >--+---------------------------------+--------------------------> '- -setPostStatusOfAllPkgs--FINAL-' >--+-----------------------------------------+------------------> | .-FALSE-. | '- -groupSQLBySpecialRegisters--+-TRUE--+-' >--+-------------------------------------------+----------------> | .------------------. | | | .-|------. | | | V V | | | '- -groupSQLByStrings----(----string-+--)-+-' >--+--------------------------+--+-----------------------+----->< | .-FALSE-. | | (2) | '- -validateXml--+-TRUE--+-' '-| Trace options |-----'
This syntax diagram shows the options that you can set for each pureQueryXML file that you list in an options file.
(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-. | '- -showDetails--+-TRUE--+-' >--+---------------------------------------+--------------------> | .-FALSE-. | '- -forceSingleBindIsolation--+-TRUE--+-' >--+------------------------+-----------------------------------> | .-100----. | '- -sqlLimit--+-number-+-' >--+--------------------------------------+---------------------> '- -removeSQLInactiveForDays----days---' >--+---------------------------------------+--------------------> '- -setPreStatusOfAllPkgs--+-AUTO-----+-' +-FINAL----+ '-REQUIRED-' >--+---------------------------------+--------------------------> '- -setPostStatusOfAllPkgs--FINAL-' >--+-----------------------------------------+------------------> | .-FALSE-. | '- -groupSQLBySpecialRegisters--+-TRUE--+-' >--+-------------------------------------------+----------------> | .------------------. | | | .-|------. | | | V V | | | '- -groupSQLByStrings----(----string-+--)-+-' >--+--------------------------+-------------------------------->< | .-FALSE-. | '- -validateXml--+-TRUE--+-'
Only DB2 for z/OS uses rowset cursors, and it does so only when the IBM Data Server Driver for JDBC and SQLJ properties indicate that rowset cursors 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:
You do not need to use this option if either of these conditions is true:
pureQuery considers a statement as updatable if either of these conditions are true:
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.
During processing, all SQL statements are organized into statement sets according to the options specified, such as sqlLimit, groupSQLByString, and groupSQLBySpecialRegister. The statement sets in the output file might be different than the statement sets in the input file.
You must supply a value for the -rootPkgName option.
This is the default value. You must supply a value for the -rootPkgName option.
For unnamed statement sets, configuration applies all specified options.
For named statement sets with the configureStatus REQUIRED, some options are applied, such as the -collection, -pkgVersion , -forceSingleBindIsolation, and -markDDLForBind. The consistency token is updated.
For named statement sets with the configureStatus flag set to AUTO, the configuration applies options only if the Configure utility determines that the statement set must be modified. The consistency token is not updated if the statement set is not modified.
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 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:
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.
Valid values are the case-independent strings TRUE and FALSE. There is no default value.
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 the DDL statement set, it attempts to bind any statements in that statement set that have the value TRUE for their isBindable attribute.
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.
If you do not specify this option, database packages that result from subsequent binds are created without a version.
Verification of the version at run time is based on the consistency token, not the version name.
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.
When set to true, the Configure utility displays special register activity captured in the input pureQueryXML file as part of the summary activity.
The default value is 100.
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.
>>-+------------------------+--+---------------------------+--->< '- -traceFile--file-name-' | .-OFF-----. | '- -traceLevel--+-ALL-----+-' +-SEVERE--+ +-WARNING-+ +-INFO----+ +-CONFIG--+ +-FINE----+ +-FINER---+ '-FINEST--'
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.
If the SQL statement does not have a timestamp, the SQL statement is written to the output file. For example, if a previous version pureQueryXML file is an input file a SQL statement does not have a timestamp. For a previous version of a pureQueryXML file, 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.
If the -cleanConfigure option is set to TRUE, the -setPreStatusOfAllPkgs option is ignored. No message is logged.
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.
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 a 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.
A 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 both the -groupSQLBySpecialRegisters and -groupSQLByStrings options are used to create statement sets. The statements are first grouped by special register values. Within each group using 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.
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 will be 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.
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:
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 |
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
The command might look like this:
java com.ibm.pdq.tool.Configure -optionsFile myOptionsFile.txt
The options file, which can have any name and extension that your operating system supports, will have these 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
capture_sales.pdqxml_1.0
capture_employee.pdqxml_1.0
capture_employee.ddl
SELECT * FROM TABLE3 SELECT * FROM TABLE2 SELECT * FROM TABLE1
Statement set 1: SELECT * FROM TABLE3 Statement set 2: SELECT * FROM TABLE2 Statement set 3: SELECT * FROM TABLE1
Statement set 1: SELECT * FROM TABLE1 Statement set 2: SELECT * FROM TABLE2 SELECT * FROM TABLE3
Statement set 1: SELECT * FROM TABLE1, SELECT * FROM TABLE2 Statement set 2: SELECT * FROM TABLE3
Using the same table name in multiple statement set definitions is not recommended, and a warning message is logged.