For a CLI 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 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 application runs.
The Configure utility updates the
pureQueryXML file for use by the StaticBinder utility. The CLI 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 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 CLI configuration file for the application, set
the value of the pureQuery keyword executionMode to STATIC.
For example, in the db2cli.ini, set the following pureQuery
keywords:
executionMode=STATIC
allowDynamicSQL=TRUE
pureQueryXml=capture.pdqxml
Alternatively, if you
use a db2dsdriver.cfg file, 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
CLI 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 Configure and StaticBinder utilities to update
or create new packages and bind the packages on the database.