Scenarios: Controlling storage of pureQuery data with pureQuery runtime properties

You can use pureQuery runtime properties to control the location that the pureQuery Runtime uses to retrieve pureQuery information and to store captured SQL data.

Configuring an application to use pureQuery client optimization and a repository

The following steps outline how a repository can be used initially with pureQuery client optimization:
  1. The database administrator creates a SQL Management repository.
  2. The administrator creates at least one runtime group version in the repository.
  3. The application administrator sets the following pureQuery runtime properties for a runtime group version. The database administrator uploads the pureQuery runtime properties to a runtime group version:
    executionMode
    The value is set to DYNAMIC.
    captureMode
    The value is set to ON.
    propertiesRefreshInterval
    The value is set to 1 (one minute).

    Other pureQuery runtime properties are set for the application as needed.

    The runtime group version contains no pureQueryXML data.

  4. The database administrator activates the runtime group version.
  5. For the application configured to use pureQuery client optimization, the application administrator specifies the following pureQuery runtime properties to retrieve the pureQuery data from the SQL Management repository:
    finalRepositoryProperties
    Specifies the repository previously created.
    propertiesGroupId
    Specifies the pureQuery runtime group previously created.
    repositoryRequired
    The value is set to the behavior specified by the application administrator.
    propertiesRefreshInterval
    The value is et to 1 (one minute).
  6. The application administrator starts the application.
  7. The application runs. pureQuery client optimization captures SQL data and stores it in the repository as part of the runtime group data.
  8. The database administrator performs the following steps to prepare the captured SQL for use with pureQuery client optimization:
    1. Extracts the captured SQL data and the other pureQuery data from the repository using the ManageRepository utility.
    2. If needed, merges the captured SQL data into a single pureQueryXML file.
    3. Configures the pureQueryXML file.
    4. Uses the StaticBinder utility to create packages on the application database from the SQL statements in the pureQueryXML file, and bind the packages to the database.
  9. The database administrator uploads the following pureQuery data to the repository in a new version of the existing runtime group.
    • The pureQueryXML data.
    • The following pureQuery runtime properties:
      executionMode
      The value is set to STATIC.
      allowDynamicSQL
      The value is set to TRUE.
      captureMode
      The value is set to OFF. The property can be set to ON to capture additional SQL statements from the application.
      propertiesRefreshInterval
      Remains at 1 minute.
  10. The database administrator sets the new runtime group version to active.
  11. After the pureQuery-enabled application checks for changes to the pureQuery information and updates the pureQuery information, the application invokes the static SQL statements for the statements that were captured and bound to the database.
    Note: If some SQL statements that use WHERE CURRENT OF require a long execution time, the execution time might delay the use of the static SQL statement.
  12. The database administrator can repeat the process and continue capturing SQL data from the application and updating the pureQuery information to test other pureQuery features. The application server administrator does not need to restart the application during the database administrator testing.

Configuring an existing application to use a repository

An existing application that is enabled with pureQuery client optimization can be configured to use a SQL Management repository. When the application is using the pureQuery data from the repository, the database administrator can modify the pureQuery data in the repository without requiring the application to be redeployed.

The following steps outline how to move pureQuery runtime properties and pureQueryXML data to a repository. The following steps assume the following environment:
  • The application is currently using pureQuery client optimization and is running static SQL.
  • The pureQuery properties and the pureQueryXML are in the application's EAR file and DataSource.pdqProperties.
  1. The application administrator determines the pureQuery properties the application currently uses.
    Note: To verify the current property settings, the administrator can use a JCC trace to display the current properties.
  2. The application administrator get the current pureQueryXML file. For example, extracts the file from the EAR file or from a source code control system.
  3. The application administrator creates a pureQuery properties file that contains all the current properties and values.
    Note: The pureQueryXml property is not included in the file. The finalRepositoryProperties and propertiesGroupId properties specify the location of the pureQueryXML file.
  4. The database administrator creates a SQL Management repository if one does not exist.
  5. The database administrator creates a runtime group version in the repository and uploads the pureQuery runtime properties file and the pureQueryXML file from the application administrator.
  6. The database administrator activates the runtime group version.
  7. The application administrator updates the pureQuery runtime properties that are used by the application at startup to retrieve the pureQuery information from the repository:
    finalRepositoryProperties
    Specifies the repository that contains the application's pureQueryXML data and runtime properties.
    propertiesGroupId
    Specifies the runtime group in the repository
    repositoryRequired
    Set to the behavior specified by the application administrator.
  8. The application administrator adds the pureQuery runtime properties to one the following locations:
    • The DataSource.pdqProperties if all applications that use that data source can use the repository.
    • pdq.appwide.properties in the EAR file if the application uses only one data source.
    • pdq.targetDSName.properties in the EAR file where targetDSName is the name of the data source to reference the repository properties and pureQueryXML data.
    Note: If the EAR file changed, the application administrator must redeploy the updated EAR file.
  9. The application administrator redeploys and starts the application. The application retrieves the pureQuery runtime properties and pureQueryXML data from the runtime group version in the repository.

Updating pureQueryXML that is data stored in a repository with captured SQL data

The following steps outline how to use the captured SQL data that is stored in a repository. The following steps assume the following environment:
  • The application that is configured to use pureQuery client optimization is invoking static SQL and incremental capture is enabled.
  • The pureQuery properties and captured SQL data are in the repository, and the value of the propertiesRefreshInterval property is 60, which is one hour.
The database administrator performs the following steps:
  1. Extract the captured SQL from the runtime group in a repository, and if needed, extract the pureQueryXML data and the configuration data. Use the ManageRepository utility.
    Note: While the captured SQL data is being retrieved, the captureMode property remains set to ON. SQL data is still being captured. However, the ManageRepository utility extracts only the complete sets of captured SQL data.
  2. Use the Merge, Configure, and StaticBinder utilities to create a new pureQueryXML file and bind the SQL statements in the pureQueryXML file to the database.
  3. Upload the new pureQueryXML and the pureQuery runtime properties into the repository in a new version of the runtime group.
  4. Optional: Delete the captured SQL data that was merged into the pureQueryXML file.
  5. Set the new runtime group version as active.

Within one hour, the pureQuery Runtime used by the application checks for updated pureQuery data in the repository and uses the updated pureQueryXML data and pureQuery properties in the new version of the runtime group.

Note: When using the workbench, you can connect to the repository and use the Runtime Group Working Copy Editor to manage the pureQuery data in a runtime group version.

Feedback