Use the following recommended options
with the pureQuery Configure utility and StaticBinder utility when
you prepare SQL statements to be run statically with pureQuery client
optimization.
Options for the Configure utility
The following
list describes recommendations for processing pureQueryXML files with
the Configure utility:
- Use the pureQuery Configure utility with the -cleanConfigure option
when a complete capture is performed.
- Use the -collection option to set the collection
during a configure operation if you know the collection ID that will
be used during the bind operation.
- Use the -groupSQLBySpecialRegisters option to
group SQL statements based on special register information recorded
with the captured SQL statements. Use the -optionsFileForBind option
to generate a StaticBinder options file that contains bind options
based on special register settings.
- If you see a warning about recorded SQL SET statements or methods,
the application might not run as expected. The SET statements or methods
might affect the behavior of some SQL statements when the application
runs the statements dynamically. The SET statements or methods might
not affect the behavior of the same SQL statements when they are run
statically on a DB2® database.
For example, the following warning might be generated by the Configure
utility:
Warning: The pureQueryXML file contains n special register SET statements or
methods that were recorded after other SQL statements were issued using the associated
connection. This might indicate that the file contains SQL statements that were
originally prepared using multiple, different special register settings.
For
example, you can use the SQL SET statement to change the value of
the special register CURRENT SCHEMA. An application can use the special
register CURRENT SCHEMA to control the database schema when running
SQL statements dynamically. The application runs SQL statements that
contain unqualified table names. The application runs the SET method
to change the value of special register CURRENT SCHEMA to WEEK1_SALES
before running some SQL statements. For other statements, the application
sets the value to WEEK5_SALES before running the statements.
When
you run the SQL statements statically, you control the database schema
by specifying bind option QUALIFER when you bind packages to the database.
The value of the special register CURRENT SCHEMA does not affect SQL
statements run statically.
When you configure a pureQueryXML
file, you can specify Configure utility options to help you create
statement sets and bind options so that the SQL statements run correctly
when the statements are run statically:
- Specify the option -groupSQLBySpecialRegisters to
group the SQL statements based on special register information recorded
when the SQL statements were captured. When you configure the pureQueryXML
file, the SQL statements that were run with the value of the special
register CURRENT_SCHEMA set to WEEK1_SALES are in a different statement
set than the SQL statements that were run with the value set to WEEK5_SALES.
- Specify the option -optionsFileForBind to generate
a StaticBinder options file that contains bind options for statement
sets based on the recorded special register settings. When you configure
the pureQueryXML file, the utility generates the bind option "QUALIFIER(WEEK1_SCHEMA)"
for the statement sets that contain the SQL statements that were run
with the value of the with the CURRENT_SCHEMA set to WEEK1_SCHEMA.
The utility generates the bind option "QUALIFIER(WEEK5_SCHEMA)" for
the statement sets that contain the SQL statements that were run with
the value set to WEEK5_SCHEMA.
The Configure utility groups any SQL statements that are
run with both special register values in a separate statement set.
You can specify that the statement set containing the statements not
be run statically by setting the isBindable attribute to false.
Binding packages from pureQueryXML files
If
the packages need to be bound only at specific isolation levels, use
the StaticBinder -isolationLevel option to specify
the isolation level.
As the default, the StaticBinder utility
creates packages for the four isolation levels, UR, CS, RS, and RR.
If your application uses more than one level, use the -isolationLevel option,
and specify only the required isolation levels. If your application
uses only one isolation level, you can specify the Configure utility
option -forceSingleBindIsolation option during configuration.
Then you specify the appropriate isolation level when running the
StaticBinder utility. If you specify the -forceSingleBindIsolation option,
the number for the isolation level is not appended to the name of
the created package.
Validating pureQueryXML files
If a problem
occurs with the pureQueryXML file, you can validate pureQueryXml files
by specifying the -validateXml TRUE option with the
pureQuery utilities Merge, Configure, and StaticBinder. Any XML validation
errors are displayed when the utility is run. The utility validates
the input XML documents with the pureQueryXML schema.
Recommendation: IBM® strongly recommends that you edit pureQueryXML
files by using the workbench only. 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 first. Use the -validateXml option
with the pureQuery Merge, Configure, and StaticBinder utilities to
perform XML schema validation on input pureQueryXML files.