A call level interface (CLI) application that is enabled
with pureQuery client optimization can be configured to run SQL statements
statically on a DB2® database.
Executing SQL statements statically avoids preparing certain SQL statements
at run time and can improve application security and performance.
Before you begin
Enable your CLI application
with pureQuery client optimization.
On the computer where
you run the Java-based pureQuery Configure utility and StaticBinder
utility, you must install and enable pureQuery Runtime. You must 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
The pureQuery Runtime JAR files, pdq.jar and pdqmgmt.jar, and
the Data Server Driver JAR files must be listed in the CLASSPATH on
the computer where you run the utilities.
About this task
The task assumes your CLI application connects to a DB2
database and that you can connect to the database with the following
connection URL:
jdbc:db2://svl01:500/DB2M -user user01 -password myPass
You
set keywords in the db2cli.ini configuration file to configure the
application. You configure your CLI application to capture SQL statements
that are issued by the application, and then configure the application
to execute the statements statically.
Procedure
To run SQL statements statically from a CLI application:
- Set the pureQuery keywords in your CLI application configuration
file to capture SQL statements that are issued by your application.
In the db2cli.ini configuration file that is used by the
application to capture the SQL statements that are issued by the application,
update the following keywords:
captureMode=ON
executionMode=DYNAMIC
pureQueryXml=C:\testapp\Sample1Cptr.pdqxml
Alternatively,
if you use a db2dsdriver.cfg file, you set the keywords as attributes
in parameter elements:
<parameter name="captureMode" value="ON"/>
<parameter name="executionMode" value="DYNAMIC"/>
<parameter name="pureQueryXml" value="C:\testapp\Sample1Cptr.pdqxml"/>
The
keyword settings configure your CLI application to run SQL statements
dynamically and capture successfully run statement in the file C:\testapp\Sample1Cptr.pdqxml.
The
value DYNAMIC for the executionMode keyword
is the default, and is not required in the properties file. Including
the property in the file is a reminder of the default value. The value
will be changed to execute SQL statements statically.
The
value for the pureQueryXml keyword specifies the
location and name of the pureQueryXML file where pureQuery client
optimization stores the SQL data that are captured from the application.
The value is also the location and name of the pureQueryXML file that
pureQuery client optimization uses to control the execution of SQL
statements. When you configure pureQuery client optimization to run
the SQL statements statically on a DB2 database, pureQuery Runtime
uses the information in the file to determine the SQL statements to
run.
- Run your application to capture SQL statements in the pureQueryXML
file.
The SQL statements are captured in the pureQueryXML
file.
- Configure the pureQueryXML file that contains the captured
SQL statements and bind the captured SQL statements.
- If necessary copy the pureQueryXML file to the computer
where the pureQuery Runtime files installed.
- Run the pureQuery Configure utility to configure the
pureQueryXML file with package name and collection ID.
At
a command prompt, run the Configure utility to add information to
the pureQueryXML file, such as the package name and collection ID,
that is used by the pureQuery StaticBinder utility. You can also specify
other options to manage the SQL statements in the file.
The
following example command runs the Configure utility and specifies
the base package name SMPL1 and a collection named COLL01. Enter the
command on a single line.
java com.ibm.pdq.tools.Configure -pureQueryXml Sample1Cptr.pdqxml
-rootPkgName SMPL1 -collection COLL01
- Run the pureQuery StaticBinder utility with the configured
pureQueryXML file to create packages on the target DB2 server and bind the packages.
At
a command prompt, run the StaticBinder utility with options specifying
the database and the package information. The following example StaticBinder
command creates packages on the target DB2 server
and binds the packages. Enter the command on a single line.
java com.ibm.pdq.tools.StaticBinder
-url jdbc:db2://svl01:500/DB2M -user user01 -password myPass
-pureQueryXml Sample1Cptr.pdqxml
- If necessary, replace the pureQuerXML file on the computer
where your application runs with the configured pureQueryXML file.
- Update your CLI application configuration file to run your
captured statements statically.
Update the pureQuery executionMode keyword
in the db2cli.ini configuration to enable static execution of the
SQL statements that are issued by the application. The keyword value
is changed from DYNAMIC to STATIC:
captureMode=ON
executionMode=STATIC
pureQueryXml=C:\testapp\Sample1Cptr.pdqxml
For in a
db2dsdriver.cfg file, you set the keywords as attributes in parameter
elements:
<parameter name="captureMode" value="ON" />
<parameter name="executionMode" value="STATIC" />
<parameter name="pureQueryXml" value="C:\testapp\Sample1Cptr.pdqxml" />
- Run or restart the application to use the updated information
in the configuration file and pureQueryXML file.
Results
When you run the application, the two SQL statements that
are issued by the application are run statically. If you update the
application and change or add SQL statements, the updated or new SQL
statements are executed dynamically and are captured in the pureQueryXML
file.
Tip: You can restrict the application to run
only the SQL statements captured in the pureQueryXML file. One configuration
that restricts the execution of SQL statements is to set the value
of the
captureMode keyword to
OFF and
add the keyword and value
capturedOnly=
TRUE:
captureMode=OFF
capturedOnly=TRUE
executionMode=STATIC
pureQueryXml=C:\testapp\Sample1Cptr.pdqxml
For in a
db2dsdriver.cfg file, you set following parameter elements:
<parameter name="captureMode" value="OFF" />
<parameter name="capturedOnly" value="TRUE" />
<parameter name="executionMode" value="STATIC" />
<parameter name="pureQueryXml" value="C:\testapp\Sample1Cptr.pdqxml" />
With
this configuration, only the SQL statements in the pureQueryXML file
are run and the SQL statements run statically. Updated or new SQL
statements are not captured in the pureQueryXML file.