Scenario: Updating the SQL information that is used with pureQuery client optimization in a production environment

In this scenario, you can update SQL information in the pureQueryXML file that is used with pureQuery client optimization in a production environment without restarting the application server.
Capturing the SQL statements that are used with pureQuery client optimization in a production environment is not recommended. Performance might degrade while capturing SQL statements, and capturing might require stopping the application. However, you might be required to update the SQL statements that an application issues in a production environment. Situations such as the following require that the new SQL statements are captured in a production environment:
  • A test environment to capture SQL statements with pureQuery Runtime cannot be set up.
  • The updates are done as a maintenance change, and the maintenance group does not have the resources to isolate the application in a test environment.

To support capturing SQL statements in a production environment, you can configure an application that uses pureQuery client optimization to store the pureQuery configuration information in a file system that is separate from the pureQuery-enabled application or a repository in a database. pureQuery Runtime captures SQL data continuously and stores the captured SQL in the specified location. The database administrator extracts the captured SQL data from the location and uses the pureQuery Merge, Configure, and StaticBinder utilities to update the SQL to be run statically on the transaction database without restarting the application.

The following figure shows a representation of the continuous process in a production WebSphere® Application Server environment.

Figure 1. pureQuery client optimization in production environment with continuing maintenance
pureQuery client optimization with continuing maintenance in a production environment

In the figure, each application instance is enabled with pureQuery client optimization and is executing SQL statements statically. Each application is using a repository in a database that is separate from the transaction database to retrieve pureQuery properties and pureQueryXML data. The repository also stores SQL data that is captured by pureQuery Runtime. The script extracts the captured SQL data and the pureQueryXML data that is used by pureQuery Runtime, merges the Captured SQL with the pureQueryXML data, updates the DB2® packages to run SQL statically and stores the pureQueryXML data in a repository. Updating the pureQuery information and the DB2 packages does not require restarting the application instances, and the database administrator can control when to run the script.

For example, suppose that you update an existing production web application that is enabled with pureQuery client optimization with changes that affect the SQL statements it issues. If you configure pureQuery client optimization to run in capture mode, the application executes the SQL statements that are in the pureQueryXML file statically. At the same time, the application new SQL statements are run dynamically and are captured by pureQuery Runtime and stored in a repository. The SQL data captured from the updated application contain metadata and statistics such as execution count and last-used date, new stack traces, special register information, and information about batching, if that information changes.

The pureQuery Merge utility can process the new captured SQL data and apply it to existing statements in the pureQueryXML file that is used by pureQuery Runtime. The Merge utility can also remove statements that were not executed for a long period of time.

The database administrator can extract the captured SQL data from the repository, run the Merge utility to merge them with the pureQueryXML file that is used by pureQuery Runtime with the web application, configure, bind and then upload the updated pureQueryXML file to the repository.

Note: In the figure, the pureQueryXML file that is used by the pureQuery Runtime and the SQL data that is captured by pureQuery Runtime are in the same repository. You can configure pureQuery Runtime to store the captured SQL in a repository separate from the pureQueryXML and pureQuery Runtime properties.

Feedback