For a CLI or .NET application that is enabled with pureQuery® client optimization
and that connects to a DB2® database,
you can configure the application to run SQL statements statically.
As part of the configuration process, you run the Java-based pureQuery utilities Configure
and StaticBinder to create packages on the database and bind the packages.
Before you begin
On the computer where you run the Java based Configure
utility and StaticBinder utility, you must install and enable pureQuery Runtime. The pureQuery Runtime JAR files,
pdq.jar and pdqmgmt.jar, must be listed in the CLASSPATH. You must
also have the following products installed on the computer:
- Java™ Runtime Environment
(JRE) Version 1.5 or later
- IBM® Data Server Driver for
JDBC and SQLJ Version 3.57.xx or 4.7.xx
About this task
After you capture the SQL data in a pureQueryXML file,
you copy the file to a computer where pureQuery Runtime is installed. You run
the Configure and StaticBinder utilities on that computer, and then
copy the updated pureQueryXML file back to the computer where the
CLI or .NET application runs.
The Configure utility updates
the pureQueryXML file for use by the StaticBinder utility. The CLI
or .NET application that was used to capture the SQL data also uses
the pureQueryXML file to determine which SQL statements to run statically.
Procedure
To configure CLI or .NET applications to run SQL statements
statically on DB2 databases:
- Copy the pureQueryXML file to the computer where pureQuery Runtime is installed.
Ensure that the pureQueryXML file is not being written to
when you retrieve the file. If you retrieve the file while it is being
written to, the contents of the file might not be valid or the application
might not be able to update the file, which causes an I/O error.
You
can use the -validateXml option with the Configure
utility to ensure the validity of the file. The -validateXml option
is supported with pureQuery Runtime
version 2.2.0.3 and later.
- Configure the pureQueryXML file with the pureQuery Configure utility.
The
following example command configures the pureQueryXML file capture.pdqxml.
The
-rootpkgname option specifies the base string
that the StaticBinder utility uses to create package names from the
pureQueryXML SQL information. The
-validateXml option
specifies that the Configure utility performs XML schema validation
to ensure that the pureQueryXML file conforms to the pureQuery XML schema.
java com.ibm.pdq.tools.Configure -rootpkgname TESTPKG
-validateXml TRUE
-pureQueryXml capture.pdqxml
- Run the pureQuery StaticBinder
utility to create a package on the target database and bind the packages
on the database.
The following example command creates
packages and binds the packages on the database that is specified
by the
-url option. The packages contain the SQL
statements from the pureQueryXML file capture.pdqxml. The
-isolationLevel option
specifies that the StaticBinder utility creates packages only for
the CS isolation level.
java com.ibm.pdq.tools.StaticBinder
–url "jdbc:db2://testserver.test.com:446/STLEC1"
-username "testuser" -password "testpwd"
-isolationLevel "CS"
-pureQueryXml "capture.pdqxml"
By default, the StaticBinder utility creates four
packages for a given pureQueryXML file; one for each type of isolation
level. This example specifies the isolation level CS. The StaticBinder
creates only one package for that isolation level.
- Copy the configured pureQueryXML file back to the computer
that is running the CLI application where the pureQueryXML file was
created.
pureQuery Runtime
uses the information from the configured pureQueryXML file to determine
the SQL statements to execute statically.
- In the configuration file for CLI or .NET application,
set the value of the pureQuery keyword executionMode to STATIC.
- If you use a db2cli.ini configuration file for your CLI application,
set the following pureQuery keywords:
executionMode=STATIC
allowDynamicSQL=TRUE
pureQueryXml=capture.pdqxml
- If you use a db2dsdriver.cfg file for
either a CLI or .NET application, you set the keywords as attributes
in parameter elements:
<parameter name="executionMode" value="STATIC" />
<parameter name="allowDynamicSQL" value="TRUE" />
<parameter name="pureQueryXml" value="capture.pdqxml" />
With the keywords values allowDynamicSQL=TRUE, pureQuery Runtime runs SQL
statements dynamically if the SQL statement is not in the pureQueryXML
file capture.pdqxml.
The default value for pureQuery keyword allowDynamicSQL is TRUE and
is not required in the properties file. Including the property in
the file is reminder of the default value.
- Run or restart the application to use the updated information
in the configuration file and pureQueryXML file.
The
application reads the updated pureQuery keywords
and pureQueryXML information and runs the SQL statements statically.
What to do next
If you capture new SQL statements in the pureQueryXML
file, you can use the pureQuery utilities
Configure and StaticBinder to update or create new packages and bind
the packages on the database.
A CLI or .NET application that
is enabled with pureQuery client optimization can retrieve pureQueryXML
file data from a repository. The application cannot retrieve pureQuery
Runtime configuration information from a repository. The application
cannot update pureQueryXML data that is stored in a repository.