Scenario: Creating and configuring a repository for pureQuery Runtime

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:
  • Create a repository that is in the transaction database to store pureQuery Runtime properties.

    When the repository is in the transaction database, the repository gains all the benefits of the transaction database in terms of backup and maintenance.

  • Configure the repository to work with the WebSphere® application:
    • Create a runtime group version in the repository.
    • Upload the pureQuery Runtime properties into the repository for the runtime group version.

      Storing pureQuery Runtime properties in a repository allows you to specify the pureQuery Runtime properties centrally and change them when required. You use the automatic refresh capability of pureQuery Runtime by setting the pureQuery Runtime property refreshInterval. Setting the refreshInterval property enables pureQuery Runtime to automatically refresh the runtime properties. With automatic refresh, a database administrator can change pureQuery Runtime properties to control the SQL issued by the application.

    • Activate the runtime group version that is associated with the application to enable pureQuery Runtime to use the runtime group version.

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:

  1. 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.

  2. 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.

  3. 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.
  4. 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".
  5. 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.


Feedback