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 "V2"
-repositoryURL "jdbc:db2://testserver.test.com:32706/sample"
-repositoryUsername "*****" –repositoryPasword "*****"
-pureQueryProperties ".\pqtest.properties"
-pureQueryXml "testApp.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.
If performance is a concern while capturing SQL data, you can set
the value of the captureMode property to NEW_STMT or LITE to
decrease the amount of data captured.
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.