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.
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.