A call level interface (CLI) or .NET 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 or .NET
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 or .NET 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 or .NET 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 or .NET application:
- Set the pureQuery keywords in your application configuration
file to capture SQL statements that are issued by your application.
- If you use a db2cli.ini configuration
file for your CLI application, set the following pureQuery keywords:
captureMode=ON
executionMode=DYNAMIC
pureQueryXml=C:\testapp\Sample1Cptr.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="captureMode" value="ON"/>
<parameter name="executionMode" value="DYNAMIC"/>
<parameter name="pureQueryXml" value="C:\testapp\Sample1Cptr.pdqxml"/>
The keyword settings configure your 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 the value of the executionMode keyword
your application configuration file to run your captured statements
statically. The keyword value is changed from DYNAMIC to STATIC
- If you use a db2cli.ini configuration file for your CLI application,
change the line that contains the keyword:
captureMode=ON
executionMode=STATIC
pureQueryXml=C:\testapp\Sample1Cptr.pdqxml
- If you use a db2dsdriver.cfg file for either a CLI or .NET
application, you change the attribute in the parameter element:
<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.
In
a db2cli.ini configuration file, the keyword
settings are similar to the following keywords:
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.