Recommendations for capturing SQL statements with pureQuery client optimization

For best results, perform the capture SQL data with pureQuery client optimization during the end of the development phase or during the test phase. Set the pureQuery Runtime properties appropriately to control the capture SQL data that is captured, and shut down the application after capturing SQL data.
Environment for the capture activity
  • The best time to perform the capture of SQL data is at the end of the development cycle or during the test cycle. Avoid capturing SQL data in production to avoid potential performance degradation.

    The test environment that is used to capture SQL data and test pureQuery client optimization should match the production environment as closely as possible.

  • If you must capture SQL data in a production environment, use a repository in a database.
Property settings to control the capture of SQL data
  • Choose the appropriate capture level:
    • In a WebSphere® environment, capture at the level of data source used within application; avoid impacting other applications using the same data source.

      Set application-specific pureQuery Runtime properties when more than one application is sharing a data source and you need to capture SQL statements at the application level. These application-specific properties can point to a repository in a database for easy property management for an application.

    • For an environment that does not use WebSphere, capture at the level of connection URL used within the application.
  • Select the appropriate pureQuery Runtime property file granularity and scope when capturing SQL data. Do not capture more data than needed.

    Ensure that you are setting the pureQuery Runtime properties at the appropriate level: connection, data source, application specific, or global. For example, if you set pureQuery Runtime properties to capture at a global level in WebSphere installation, all applications will write to the same file.

  • Customize the pureQuery Runtime properties according to your needs when capturing SQL data.

    For example, to minimize the pureQueryXML file size and increase processing speed, you can disable the capturing stack trace information if it is not needed.

  • Avoid capturing all stack traces or all stack trace elements by specifying a value of -1 for the pureQuery Runtime property maxStackTracesCaptured or for the property stackTraceDepth.

    The minimal sized stack traces can be gathered by filtering the contents of the stack trace with the pureQuery Runtime property packagePrefixExclusion.

  • Consider using the pureQuery SQL literal substitution capability if the application executes SQL statements that contain literal values.

    If your application runs many SQL statements that share the same syntax and differ only in the literal values that they contain, pureQuery Runtime can capture and consolidate those statements by substituting parameter markers for the literal values

  • Enable tracing only if problems are encountered.

    Enable IBM® Data Server Driver for JDBC and SQLJ tracing and set pureQuery tracing to the level FINER to direct pureQuery Runtime to report potential static SQL execution issues.

Files containing captured SQL data
After you capture SQL data to a file on disk, shut down the application after a capture operation is complete to ensure that pureQuery Runtime completes writing the data to the file. The capture file must be complete and closed before your run the Merge or Configure utility.

This is practice is particularly necessary for WebSphere applications when pureQuery Runtime is writing the captured SQL data to file on disk.

The application does not need to be shut down if pureQuery Runtime stores captured SQL data in a repository created in a database. The captured SQL data are written to records in the repository. When extracting the data, the pureQuery ManageRepository utility does not create pureQueryXML files from records that are still being used to capturing data.


Feedback