Overview of the SQL Management repository and runtime groups

An SQL Management repository is created in a database and stores pureQuery data such as pureQuery runtime properties, data from a pureQueryXML file, and captured SQL data. In a repository, sets of pureQuery data are stored in runtime groups. An application that is enabled with pureQuery client optimization can be configured to retrieve pureQuery data from a runtime group or store captured SQL data in a runtime group.

After a database administrator creates a repository by using the ManageRepository utility, different applications can use pureQuery data that is stored in a repository. The pureQuery data for an application's each data source is stored in a separate runtime group. Multiple versions of the pureQuery data can be stored within each runtime group.

One application can run on several computers. The pureQuery data from a runtime group can be accessed by an application that is running several computers. SQL data captured from the application's data source can stored in a runtime group in a repository.

SQL Management repository

When configuring applications to use pureQuery client optimization, pureQuery Runtime captures SQL statements and related information and stores the captured SQL in files. pureQuery client optimization uses the captured SQL information to map the dynamic invocation of an SQL statement by the application to static invocation of the SQL statement in a bound package. Storing the captured SQL and pureQuery runtime properties a repository provides the following advantages:
  • Centrally managed information can be extracted and used by applications that use pureQuery client optimization.
  • The backup strategy for the information can be aligned with database backup strategies.
  • Access control can be enforced by the database.
  • The derived data from captured SQL can be shared with Optim™ Performance Manager Extended Edition, which enables an end-to-end monitoring solution that is controlled by database administrators.

SQL data that is captured from an application can be stored either in a file or in the repository that is specified by the finalRepositoryProperties property. If the outputPureQueryXml property is retrieved from the location that is specified by the finalRepositoryProperties property, then the captured data is written to the file. If the outputXmlRepository property is retrieved from the location that is specified by the finalRepositoryProperties property, then the captured SQL data is written to a repository. The outputPureQueryXml and the outputXmlRepository properties cannot be set at the same time. Only one of the properties can be set.

Runtime groups

In a repository, one runtime group stores the pureQuery information that is used with an application's data source. Each runtime group contains runtime group versions that store different versions of the pureQuery data used by pureQuery client optimization. Only one version of a runtime group can be active at a time. The pureQuery Runtime uses only the pureQuery data from the active runtime group version.

The following table describes the types of pureQuery information stored in the repository. Each runtime group version stores a complete set of data.
Table 1. Types of pureQuery information stored in the repository
Repository information Description Example file name
pureQueryXML data SQL statements and information that is related to the SQL statements. The information can be used by the pureQuery Runtime to control the execution of SQL statements. The StaticBinder utility uses the SQL statements to create packages and binds the packages in a database that supports static SQL. caputure.pdqxml
Options for configuration Options that are used by the Configure utility to process the SQL statements in the pureQueryXML file. The Configure utility prepares the SQL statements in the pureQueryXML file for use by the StaticBinder utility and pureQuery Runtime. default.genProps
Options for bind Options that are used by the StaticBinder utility to create packages and bind the packages from the SQL statements in the pureQueryXML data. default.bindProps
Runtime properties Properties that are used by pureQuery Runtime to control execution of the SQL statements that are sent to a data source from an application. pdq.properties
Captured SQL data SQL statements and SQL-related information, such as special register, stack trace, and performance, that is captured when using pureQuery client optimization.* capture.pdqxml*
Note:

* The captured SQL data must be processed by the Configure utility before it can be used by pureQuery client optimization to execute SQL statements statically. Sets of captured SQL data can be combined by using the Merge utility. The workbench uses the Configure and Merge utilities to process the captured SQL data.

The following runtime group information is also stored in the repository to help track runtime group versions:
  • A timestamp when the information was created or updated
  • Contact information, such as a name and other information (optional)

You assign a runtime group ID to each runtime group you create in the repository. When you configure an application to use pureQuery client optimization, you use the finalRepositoryProperties and propertiesGroupId properties to specify the repository and the runtime group ID that pureQuery Runtime uses to retrieve the pureQuery runtime properties from the repository. When an application that is enabled with pureQuery client optimization is configured to use the information from a runtime group, the information from the active version is used with pureQuery Runtime to manage the SQL that is issued by the application.


Feedback