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.
This topic contains the following sections:
To understand the conventions that are used in the diagrams, see How to read syntax diagrams.
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.
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.-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-. | '- -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-'
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-. | '- -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 |-----'
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-. | '- -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--+-'
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:
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.
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.
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 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.
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.
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.
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 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.
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 statement sets, it attempts to bind any statements in that statement set that have the value TRUE for their isBindable attribute.
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.
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 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.
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.
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.
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 "(SAMPLE > SAMPLE1) (STLEC1 > STLEC2)"
-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 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 "(MYSCH1 > MYSCH2) (MYSCH3 > MYSCH4)"
-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.
Valid values are TRUE and FALSE. The default value is FALSE, invalid SQL statements are not removed 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.
See the pureQuery StaticBinder utility option -statementBindError for information about SQL statements that are marked invalid.
Valid values are TRUE and FALSE. The default value is FALSE, SQL statements that are marked invalid are not restored.
An error is displayed if you also specify the -removeInvalidSQL option with the value TRUE.
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.
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.
If the -cleanConfigure option is set to TRUE, the -setPreStatusOfAllPkgs option is ignored. No message is logged.
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--'
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.
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 using the DB2 packages created from the SQL statements in the pureQueryXML file.
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.
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.
<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.
<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 > 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"/>
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.
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: "SYSIBM","SYSFUN","SYSPROC",
"SYSIBMADM","MYTEST"|
SQLID: MYTEST |
DECFLOAT ROUNDING: ROUND_HALF_EVEN"/>
<specialRegValue specialRegValueId="1"
specialRegisterVectorValue="UNTRACKED_SET: FALSE|
SCHEMA: DB2ADMIN|
PATH: "SYSIBM","SYSFUN","SYSPROC",
"SYSIBMADM","DB2ADMIN"|
SQLID: DB2ADMIN|
DECFLOAT ROUNDING: ROUND_HALF_EVEN"/>
</specialRegValuesSet>
<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.
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 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
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
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)".
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')
-replaceLocations " ( sample > testDB ) "
-replaceLocations "(sample > testDB)"
SET CURRENT SCHEMA = "schm0"
SET CURRENT SCHEMA = 'schm0';
SET CURRENT SCHEMA = "prodSchm"
SET CURRENT SCHEMA = 'prodSchm';
SET PACKAGE PATH = 'schema1, schema2'
The
Configure utility recognizes the two names as schema names and replaces
them if required.SET PACKAGE PATH = 'abc'
SET PATH = 'schema1';
The
name is changed by the utility if required.