Options for storing pureQuery data

When you run applications that are enabled with pureQuery client optimization, you can specify the location where pureQuery Runtime retrieves the runtime configuration information, and pureQueryXML information. When you capture SQL data, you can specify where the data is stored.
You usually create the following files that are used during the pureQuery client optimization enablement process and during runtime:
  • pureQuery Runtime configuration properties file
  • pureQueryXML file that contains SQL data used during runtime
  • Configure properties file and bind properties file
  • pureQueryXML files that contain captured SQL data
You will need to decide where to store pureQuery properties and data. You can use the default pureQuery Runtime location or specify a location.
Local file system (default behavior)
By default, pureQuery Runtime looks for properties and data on the local file system. Under this model, the pureQuery Runtime properties pureQueryXml and outputPureQueryXml specify the files that contain pureQuery data. These two properties can be specified in various places, including files named pdq.properties and pdq.appwide.properties.
Specify a file system or repository in a database
You can, however, specify that the pureQuery data and runtime properties are in a repository in a database, in a file system, or a combination of both. Use the pureQuery Runtime properties finalRepositoryProperties and outputXmlRepository to specify the location.

When you use an application that is enabled with pureQuery client optimization and store the pureQuery data in a repository, you use pureQuery utility ManageRepository to access and store the pureQuery data in the repository. The ManageRepository utility can be used create a repository and to manage and maintain the repository.

Storing pureQuery data and properties in a repository has advantages, but you must plan appropriately when creating and maintaining your repository.

Advantages of using a repository

Advantages to using a repository in a database as a central store for pureQuery data include:
  • The pureQuery data can be accessed or updated without interrupting running applications.
  • A centrally managed repository allows a single copy of pureQuery properties and XML files to be used by applications that are deployed on multiple application servers.
  • A running application can be configured to periodically check for updates to the pureQuery properties and XML files, and automatically begin using the new data.
  • Authorization to access and update the pureQuery data can be enforced by the database.
  • An administrator can more easily examine the pureQueryXML file to inspect SQL for an application and to see source code location information for any problem SQL statements.

Repository configuration considerations

When you use a database to store pureQuery artifacts, you have some flexibility regarding the configuration. The primary decision points are:
  • Where will the database or databases reside that contain the pureQuery data?
  • How should an application react when it cannot access the repository during initialization?
  • Should an application use the automatic refresh capability? If so, what is a recommended interval for polling the repository DBMS for updates?
Determine Repository Database Location
The two types of data are input data and output data:
  • pureQuery Runtime execution and configuration data (input)
  • pureQuery captured SQL data (output)
It is possible to configure the application execution environment to use different locations for the input and output data. You can locate each type of data in one of:
  • The same database where the application performs transactional work (the transaction database)
  • A database that runs at the same location as the application server
  • A remote database which is separate from either the application or the transaction database server
Default recommendation:
Create a single repository in the transaction database for both input and output. There are two advantages:
Simplicity
This approach affords simplicity because it avoids the need to create and maintain data in a separate database.
Availability
Applications might be configured to fail if the pureQuery database repository is not available during initialization. In such cases, using the transaction database tends to increase application availability because the transaction database normally has excellent availability and other quality of service characteristics. Using a separate server with similar characteristics, increases in the possible points of failure.
Other Database location considerations:
  • If you are concerned about increased activity in the transaction database or on the network due to pureQuery automatic refresh, or writing output capture records, consider placing the repository database on the application server or a separate server.
  • Your site might also have standards regarding the types of data that are permitted in the transaction database. These requirements can also push you to choose a separate server for the database.
  • One compromise variation that you might consider is to put the input data repository on the transaction database, and create a repository on a separate server to store the output capture records.
  • Configurations that involve a combination of input data in a database repository and output data in a local or remote file system are also allowed.
Behavior for when the repository is unavailable
It is important to consider ahead of time how you expect your application to behave in the event that pureQuery data is unavailable when an application starts. The pureQuery Runtime time property repositoryRequired controls the behavior.

The default setting no causes pureQuery Runtime to revert to the default behavior for all pureQuery client optimization properties. Which means that the application will execute using dynamic SQL. If this fallback is preferred over having the application fail when it starts, then keep the default behavior.

However, in some cases, such as when executing SQL statements statically, it might be problematic or impossible to run the application with the default behaviors. For example, the authorization to prepare and execute SQL statements dynamically might not be in place for all application users. In those cases, you should specify the pureQuery Runtime property repositoryRequired with the value atStartUp.

Similarly, if capturing of output data is critical when the application is running, you can have pureQuery Runtime verify the availability of the output database when the application starts by specifying the repositoryRequired property with the value forOutput. In most cases, this setting is not necessary. If the output database becomes available during the execution of the application, pureQuery will detect the change and begin to write the output.

If you need to ensure that both input and output pureQueryXml databases are available, you can specify the repositoryRequired property with the value atStartupAndForOutput. This setting might also be a useful option during an initial configuration to be sure that everything is configured correctly, and to avoid running the application for an extended period before realizing that something was not configured correctly.

Automatic refresh interval
You can enable the pureQuery capability to automatically refresh the pureQuery Runtime properties and pureQueryXML for long-running applications and cached connections by using the propertiesRefreshInterval property. Although this capability is off by default, you can enable the periodic polling and refresh by specifying a positive integer value, which specifies the number of minutes between refresh attempts.

Applications that receive frequent maintenance updates, and frameworks that continue to generate SQL not previously captured are good candidates for the refresh. The interval specified will depend on the immediacy with which you need to see updates reflected. For many applications, refreshing once per day (specifying the propertiesRefreshInterval with a value of 1440) is sufficient.

During initial application setup, including capture, and when first switching from dynamic to static, you might want a more frequent interval, such as 10 minutes to update the changes quickly.

In many cases, you will want to leave this capability disabled. If your application has little or no maintenance updates after deployment, and virtually all SQL has been captured and bound, there is little need to run in continuous capture mode or enable automatic refresh. Also, if it is easy for you to restart your application and application server whenever you want pureQuery Runtime to recognize new runtime properties or pureQueryXML, then there is no need to use the automatic refresh capability.

If you have enabled automatic refresh, and later want to change the refresh interval while the application is running, you can do so by changing the property value in the database repository. pureQuery will detect the change at the next interval, and will subsequently switch to the new value.


Feedback