After you create packages containing
SQL statements on the transaction database and bind the packages,
you update the pureQuery Runtime properties and pureQueryXML data
in the repository to enable the application to run the SQL statements
statically.
When the application refreshes
the pureQuery properties, the SQL statements are run statically.
About this task
You use the ManageRepository utility to update the data
in the runtime group and create a version in the repository. You perform
the following actions:
- Upload the data in the pureQueryXML file.
- Update the pureQuery Runtime properties.
- Active the runtime group version in the repository.
Procedure
To update pureQuery Runtime properties to run SQL statically:
- Update the local version of the pqtest.properties file
and set the value of the executionMode property to STATIC.
The file contains the following text:
pdq.captureMode=ON
pdq.allowDynamicSQL=TRUE
pdq.executionMode=STATIC
With the pureQuery
Runtime settings, the application executes SQL statements statically
if the SQL statement is in the pureQueryXML file. If the application
executes an SQL statement that is not in the pureQueryXML file the
statement are captured and executed dynamically.
- Create a version for the runtime group the repository.
Create the version V2 for the runtime group testApp. You
will upload the pureQuery Runtime properties and pureQueryXML data
to the repository.
The following ManageRepository command creates
the runtime group version:
java com.ibm.pdq.tools.ManageRepository -create runtimeGroup
-repositoryDriverClass com.ibm.db2.jcc.DB2Driver
-repositoryURL "jdbc:db2://testserver.test.com:32706/sample"
-repositoryUsername "myuser" -repositoryPassword "mypwd"
-runtimeGroupId testApp
-runtimeGroupVersion V2
The output from the command is similar to the following
example:
IBM Optim pureQuery runtime 2.2.0.3 build 2.18.118
Licensed Materials - Property of IBM
5724-U16
(c) Copyright IBM Corp. 2006, 2010 All Rights Reserved.
=========================================================
Starting to process options: -create "runtimeGroup"
-bind "packages" -runtimeGroupVersion "V2"
-repositoryURL "jdbc:db2://testserver.test.com:32706/sample"
-repositoryUsername "myuser" -repositoryPassword "******"
-repositoryDriverClass "com.ibm.db2.jcc.DB2Driver"
-runtimeGroupId "testApp"
The ManageRepository utility successfully created the runtime group "testApp" version "V2".
- Update the runtime group version in the repository with
the data in pqtest.properties file and pureQueryXML file.
Run
the command:
java com.ibm.pdq.tools.ManageRepository
-update runtimeGroup
-repositoryDriverClass com.ibm.db2.jcc.DB2Driver
-repositoryURL "jdbc:db2://testserver.test.com:32706/sample"
-repositoryUsername "myuser" -repositoryPassword "mypwd"
-runtimeGroupId testApp
-pureQueryProperties ./pqtest.properties
-pureQueryXml ./testApp.pdqxml
-runtimeGroupVersion V2
The output from the command is similar to the following
example:
IBM Optim pureQuery Runtime 2.2.0.3 build 2.18.118
Licensed Materials - Property of IBM
5724-U16
(c) Copyright IBM Corp. 2006, 2010 All Rights Reserved.
===================================================
Starting to process options: -update "runtimeGroup" -runtimeGroupVersion "v1"
-repositoryURL "jdbc:db2://testserver.test.com:32706/sample"
-repositoryUsername "*****" –repositoryPasword "*****"
-pureQueryProperties ".\pqtest.properties"
-pureQueryXml "capture.pdqxml"
-runtimeGroupId "testApp"
The ManageRepository utility successfully updated the runtime group "testApp" version "V2".
- Activate the V2 version in the runtime group testApp.
Run the command:
java com.ibm.pdq.tools.ManageRepository -activate runtimeGroup
-repositoryDriverClass com.ibm.db2.jcc.DB2Driver
-repositoryURL "jdbc:db2://testserver.test.com:32706/sample"
-repositoryUsername "myuser"
-repositoryPassword "mypwd"
-runtimeGroupId testApp
-runtimeGroupVersion V2
When you activate version
V2, version V1 is automatically deactivated. pureQuery Runtime will
retrieve the pureQuery information from the active version of the
runtime group in the repository.
The output from the command
is similar to the following example:
IBM Optim pureQuery Runtime 2.2.0.3 build 2.18.118
Licensed Materials - Property of IBM
5724-U16
(c) Copyright IBM Corp. 2006, 2010 All Rights Reserved.
========================================
Starting to process options: -activate "runtimeGroup" -runtimeGroupVersion "V2"
-repositoryURL "jdbc:db2://testserver.test.com:32706/sample"
-repositoryUsername "*****" -repositoryPassword "*****"
-runtimeGroupId "testApp"
The ManageRepository utility successfully activated the runtime group "testApp" version "V2".
Results
The pureQuery Runtime refresh interval property propertiesRefreshInterval was
set to two minutes for this application. After two minutes, the updated
pureQuery Runtime properties take effect. With the value of the executionMode property
set to STATIC, the application runs the SQL statements
statically.
In version V2 of the runtime group testApp, the
pureQuery Runtime property captureMode is set to ON.
pureQuery Runtime is still capturing SQL data from the application.
If you did not capture all the SQL statements from the application,
you can extract the captured SQL and the pureQueryXML data, and repeat
the process of merging, configuring, and binding the SQL statements
and then uploading the updated pureQueryXML file to the repository.
The
pureQuery data In version V1 of the runtime group testApp is not changed.
You can activate version V1 to return the previous pureQuery Runtime
settings.
Note: One method of determining if SQL statements are
being executed statically is to drop the package. If you drop the
package TESTAPP from the database, the application will not be able
to run SQL statements listed in the pureQueryXML file.
If you run
the StaticBinder utility with the pureQueryXML file to re-create the
package. The application will run the SQL statements statically.
You
can also use a database monitoring tool such as Tivoli® OMEGAMON® XE
for DB2® on z/OS® to verify that SQL is being executed statically.
What to do next
This set of tasks showed how a WebSphere® application can be configured
with pureQuery client optimization and showed how a repository in
a database can be used to store pureQuery Runtime properties and pureQuery
captured SQL data.