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.
The following scenarios describe how to use pureQuery
runtime properties to control how pureQuery Runtime uses different
storage locations:
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:
- The database administrator creates a SQL Management repository.
- The administrator creates at least one runtime group version in
the repository.
- 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.
- The database administrator activates the runtime group version.
- 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).
- The application administrator starts the application.
- The application runs. pureQuery client optimization captures SQL
data and stores it in the repository as part of the runtime group
data.
- The database administrator performs the following steps to prepare
the captured SQL for use with pureQuery client optimization:
- Extracts the captured SQL data and the other pureQuery data from
the repository using the ManageRepository utility.
- If needed, merges the captured SQL data into a single pureQueryXML
file.
- Configures the pureQueryXML file.
- 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.
- 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.
- The database administrator sets the new runtime group version
to active.
- 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.
- 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.
- 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.
- The application administrator get the current pureQueryXML file.
For example, extracts the file from the EAR file or from a source
code control system.
- 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.
- The database administrator creates a SQL Management repository
if one does not exist.
- 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.
- The database administrator activates the runtime group version.
- 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.
- 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.
- 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:
- 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.
- 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.
- Upload the new pureQueryXML and the pureQuery runtime properties
into the repository in a new version of the runtime group.
- Optional: Delete the captured SQL data that was merged into the
pureQueryXML file.
- 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.