You use the pureQuery ManageRepository utility to create
and manage a repository that is in the transaction database. After
creating the repository, you upload the pureQuery Runtime properties
to the repository.
About this task
You use the pureQuery ManageRepository utility to perform
the following tasks:
The WebSphere Web
application that is enabled with pureQuery client optimization will
use the information in the repository runtime group version.
Procedure
To create and configure a repository for pureQuery Runtime:
- Create the repository in the transaction database.
Run the ManageRepository utility with the
-create
repository option. The following command creates a repository:
java com.ibm.pdq.tools.ManageRepository -create repository
-repositoryDriverClass com.ibm.db2.jcc.DB2Driver
-repositoryURL "jdbc:db2://testserver.test.com:32706/sample"
-repositoryUsername "myuser" -repositoryPassword "mypwd"
-repositoryType pureQueryRuntimeOnly
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 "repository"
-repositoryURL "jdbc:db2://testserver.test.com:32706/sample"
-repositoryUsername "****" -repositoryPassword "*****"
-repositoryDriverClass "com.ibm.db2.jcc.DB2Driver"
-repositoryType "pureQueryRuntimeOnly"
The ManageRepository utility successfully created the repository.
The
-repositoryType option was specified
as pureQueryRuntimeOnly for creating a lightweight Repository for
pureQuery Runtime use only.
Tip: You can use the
-generateScriptOnly option
with the ManageReposoitory command to generate an SQL script that
contains the SQL statements to create a repository but does not create
a repository. You can modify the script to put tables in their own
table spaces instead of the default table space. However, you cannot
modify the schema name, table names, or column names.
If you create
a repository by using a script, you can use the -verify repository option
to ensure that the structure of the repository is correct.
- Create a runtime group version in the repository.
Create
a runtime group with the ID testApp and the version ID V1.
When you create a runtime group you must also specify a version.
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 V1
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 "V1"
-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 "V1".
The group ID testApp is the same as the value that
you will specify for the pureQuery Runtime property propertiesGroupId when
you create the custom data source property pdqProperties on
the WebSphere data source.
- Create a file containing pureQuery Runtime properties.
Create a file
pqtest.properties in the
current directory of the local file system with the value of the pureQuery
Runtime property
captureMode set to
ON,
NEW_STMT,
or
LITE. The file contains the following lines
of text:
pdq.captureMode=ON
pdq.allowDynamicSQL=TRUE
When you specify pureQuery
Runtime properties in a properties file, you add the prefix pdq. to
the property names.
When the value of the captureMode property
is ON and outputPureQueryXml property
and the outputXmlRepository property are not set,
then pureQuery Runtime stores the captured SQL data in the repository
that is specified by the pureQuery property finalRepositoryProperties.
In the repository, the data is stored in the active version of the
runtime group that is specified by the pureQuery property propertiesGroupId.
The
default value for pureQuery Runtime property allowDynamicSQL is TRUE and
is not required in the properties file. Including the property in
the file is reminder of the default value. By default, pureQuery Runtime
allows SQL statements that are issued by the application to be run
dynamically even if the SQL statement was not previously captured.
Tip: 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 SQL data captured.
- Upload pureQuery Runtime properties to the repository.
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 -runtimeGroupVersion V1
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 "*****" -repositoryPassword "****"
-repositoryDriverClass "com.ibm.db2.jcc.DB2Driver"
-pureQueryProperties "./pqtest.properties"
-runtimeGroupId "testApp"
The ManageRepository utility successfully updated the runtime group "testApp" version "V1".
- Activate the runtime group version V1 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 V1
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 "V1" -repositoryURL "jdbc:db2://testserver.test.com:32706/sample"
-repositoryUsername "*****" -repositoryPassword "*****"
-pureQueryProperties "./pqtest.properties"
-runtimeGroupId "testApp"
The ManageRepository utility successfully activated the runtime group "testApp" version "V1".
Results
You created a repository and added the pureQuery Runtime
properties to a runtime group version in the repository. You also
activated the runtime group version in the repository to enable pureQuery
Runtime to retrieve the properties for the runtime group from the
repository.
When you run the application, pureQuery Runtime
captures the SQL statements issued by the application and data associated
with the SQL statements. The capture process creates new records in
the repository that contain the capture information.
What to do next
In the next task, you configure the WebSphere Application Server to use the
repository and the pureQuery data in the repository when you run the
application. When you configure pureQuery Runtime to retrieve the
pureQuery information from a repository, you specify the runtime group.
pureQuery Runtime retrieves the information from the active version
of the runtime group.