Features and benefits of pureQuery client optimization

Enabling a Java application with pureQuery client optimization can improve application security, maintainability, and performance. A key benefit of enabling pureQuery client optimization is the ability to convert the application from executing SQL dynamically to executing SQL statically.
Other benefits of pureQuery client optimization include: With pureQuery client optimization, a database administrator can use the existing skills in the organization to mange the applications.
The following sections describe the benefits of pureQuery client optimization and of executing SQL statically in the following areas:

Summary of features and benefits

The following list summarizes the capabilities that are available with pureQuery client optimization:
  • Development framework independence
  • Tooling integration (with InfoSphere® Optim™ Optim products)
  • SQL statement retrieval
  • Identifying poorly performing SQL (when working with other InfoSphere Optim products)
  • Capturing SQL performance data (when working with other InfoSphere Optim products)
  • Capturing data type information of SQL statements
  • Choice of executing SQL statements dynamically or statistically
  • Running only previously captured SQL
  • Ability to substitute SQL literals with SQL parameter markers
  • SQL statement replacement
The following list summarizes the benefits of executing SQL statements statically:
  • Consistent SQL performance
  • Predictable SQL access plans
  • Improved database security when executing SQL
  • Reduced network traffic
  • Elimination of runtime PREPARE and DESCRIBE statements
  • SQL statement retrieval
  • Tracking of SQL execution
  • DB2® Explain support
  • Ability to identify poorly performing SQL
  • Execution-specific error information

Development process benefits

You can develop, test, and deploy applications in a flexible environment.

Benefits include:
Choice of framework during development
Several technologies are available for Java application development that are used by application developers for data access. pureQuery client optimization works with the frameworks and provides no restrictions on choosing a particular framework for application development.

For example, frameworks such as Hibernate, Spring, iBatis, and Java Persistence Architecture (JPA) can be used at development time. pureQuery client optimization works with the JDBC driver and is not affected by the layers of application logic above it. Developers can continue to develop applications that use dynamic SQL and do not need to worry about the semantics of static SQL and deployment considerations.

pureQuery client optimization is more of a configuration step than a development step. pureQuery client optimization should be enabled during integrated application testing. Capturing SQL statements during testing ensures that most of the SQL statements are captured and converted to run statically so that the maximum benefits of executing SQL statically are achieved.

A pureQueryXML file contains SQL statements and can be used by an application that is enabled with pureQuery client optimization. You can create an pureQueryXML file with one of the following resources:
  • JPA application XML files: If you use the IBM® JPA implementation, you can use the wsdb2gen utility to generate an XML file that contains the SQL statements used in the persistence units.

    The file that is generated by the wsdb2gen utility is compatible with pureQuery client optimization. Optionally, you can use this file to capture additional SQL statements for an application that is enabled with pureQuery client optimization. The file will contain the SQL statements that was created by the wsdb2gen utility and any additional SQL statements that were captured by pureQuery Runtime. You use the file with the pureQuery Configure and StaticBinder utilities to create DB2 packages that contain the SQL statements and to bind the packages to the database.

  • SQL text files: Some applications isolate the SQL statements that are used in the application into a text file and use application logic to process the file. The text file contains the SQL statements separated by a separator such as semicolon (;). You can use the pureQuery GeneratePureQueryXml utility to generate a pureQueryXML file from the text file.

    After you create a pureQueryXML file with the GeneratePureQueryXml utility, you can use the file with pureQuery Runtime to capture additional SQL statements, or you can use the file with the pureQuery Configure and StaticBinder utilities to create DB2 packages that contain the SQL statements and to bind the packages to the database.

Choice of dynamic or static SQL execution
When an application is enabled with pureQuery client optimization, you can specify which of the SQL statements that are executed by application are executed statically and which are executed dynamically. For example, you can choose to run the most critical SQL statements statically and allow others to execute dynamically.

Developers do not need to know or understand details of the database artifacts that affect static SQL execution or the deployment steps that are needed to convert the application to execute SQL statically. Developers can develop applications that execute SQL dynamically based on the framework that they are familiar with. Developers can focus on functional characteristics of the application and the business logic of the application. The database administrators can focus on deploying and optimizing the SQL statements that are used by the application.

When executing SQL statically, pureQuery client optimization can use the package versioning capability of static SQL in a DB2 database server. Static SQL execution allows a staged rollout with concurrent execution of new application code and DB2 access plan changes. Package versioning lets you save existing DB2 packages and create new packages. You can use package versions to create a clear and simple fallback procedure in cases when the changes have negative effects.

In JDBC-based dynamic SQL applications, no fallback procedure exists for DB2 access plan changes. When executing SQL statically, you can use package versioning to execute SQL with a previously generated access plan.

Note: For applications that are enabled with pureQuery client optimization to execute SQL statically, it is recommended that the configure and bind steps occur for the completed application in a test phase before migrating of the application to a production environment. Performing the steps in the test phase ensures extensive testing and verification of the process of deploying and running SQL statements statically. For information about the steps for enabling purQuery client optimization see Steps to enable pureQuery client optimization.
Tooling integration
pureQuery client optimization is integrated with the IBM Data Studio. You can enable pureQuery for a Java project. IBM Data Studio provides many helpful views to get more insight into the application and its associated packages.

Tuning and problem determination benefits

pureQuery Runtime captures successfully executed SQL statements and related information such as SQL parameter information and result set information. You can tune the SQL statements captured by pureQuery Runtime and you can use the information that is captured by pureQuery Runtime to determine the source of problems.

Benefits include:
Type parameter and result set metadata for captured SQL statements
When an SQL statement is executed dynamically, it is prepared and described by the JDBC driver. These actions ensure the SQL statement syntax and semantic validity. Additionally, the parameters and result columns are checked for type, length, CCSID, and other attributes against target database columns. With static SQL execution, type checking occurs only once at program preparation time instead of at run time as in dynamic SQL execution.

During the capture process, pureQuery Runtime intercepts the calls to the JDBC driver and captures the information. The SQL statement is captured only if it is successfully executed.

The process of creating packages from the SQL statements captured by pureQuery and binding the packages to the database can proceed smoothly because the SQL statements were successfully executed against the database. Also, using the type information, the bind process can select the appropriate indexes for the access paths.

Package-level accounting and snapshot information with SQL statements executed statically
Package-level accounting information is available both for DB2 for Linux, UNIX, and Windows and DB2 for z/OS®. In DB2 for Linux, UNIX, and Windows, information is in the package snapshot, and in DB2 for z/OS information is in an accounting report.

Package-level performance information allows database administrators to identify the specific sections of an application that are problematic without the need for application-level traces. Package-level performance information also helps to identify the sections of code that are most frequently executed, and which sections of code should be focused on for detailed tuning analysis. In a dynamic environment, it can be difficult to identify the code sections without defining embedded user specific traces within the application.

pureQuery Runtime also captures stack traces from the application. The database administrator can use the stack traces and work with the developer to diagnose the source of a problem.

SQL statement retrieval
Database administrators can use multiple approaches to retrieve individual SQL statements for further tuning. To capture SQL statements in an application that executes SQL dynamically, either a SQL trace needs to be enabled, or information that is contained in a cache must be retrieved, or the SQL statement must be used with the DB2 EXPLAIN and executed dynamically. In contrast, static SQL statements are predefined and information about the statements can be easily retrieved. SQL statements are captured and preserved in the DB2 catalog tables, and their access plans can be captured in the DB2 explain tables in advance. Querying the catalog and explain tables can retrieve the SQL statement in question and the access plan associated with it.
DB2 EXPLAIN facility

You can gather performance characteristics of an SQL statement either by using Visual Explain at development time in IBM Data Studio, or by using the bind option EXPLAIN YES when running the pureQuery StaticBinder utility.

By using the bind option EXPLAIN YES, a database administrator can review the access path decisions made by the DB2 optimizer. Administrators can determine which data statistics to collect to improve access path selection. Other tools can also help analyze SQL statements within explain tables and make tuning recommendations based upon their content. The access plan analysis and tuning is done during the program preparation time, and a predefined access plan can be included with the package that is created for the SQL statements that will be run statically. When you execute SQL dynamically, access plans cannot be predefined.

DB2 error messages
When you use a DB2 database, error messages such as lock timeouts, deadlocks, and resource unavailable errors often include a package name when SQL is executed statically. By linking the error to an application module, a database administrator can use package information to identify the SQL statement that is causing the error, or that is affected by the error.

DB2 for z/OS error messages include information about the package that is being executed. These error messages contain information about the location, package name, and consistency token. The information can be used to identify the application source quickly. A similar error message that is generated when an SQL statement is executed dynamically does not supply information about the application that issued that SQL. All SQL statements go through the same JDBC packages.

With DB2 for Linux, UNIX, and Windows, tools such as the db2deadlock event monitor, when used with static SQL statements, provide the package name and section number information for the deadlock event in addition to the specific lock resources involved in the deadlock. This information can be displayed with the DB2 db2evmon tool.

Application stack traces
If you have access to the Java code for the application, you can use stack trace information captured by pureQuery Runtime to associate an SQL statement with a location in the Java source code. pureQuery Runtime stores the stack trace information with the SQL statement in the pureQueryXML file. With IBM Data Studio, you can use the stack trace information to locate the Java code that issued the statement.

Operational benefits

When an application that is enabled with pureQuery client optimization executes SQL statements statically, the SQL statements are provided to the database in advance of program execution. Identifying the SQL statements allows database administrators to exploit the full set of tuning tools at their disposal and does not require an online trace to identify SQL statements.

Operational benefits include:
Security model when executing SQL statements statically
In the SQL static execution model, the authorization ID that is used at run time is not required to have access to base database objects. Instead of giving access to base database objects as in a dynamic JDBC implementation, the runtime authorization ID is given access to a specific predefined package and the included SQL statements.

Authorization of a package allows an improved security implementation because authorization IDs cannot change the SQL statements by using programming logic. In addition, if the ID is used to connect from an alternate access mechanism due to a security breach, the ID cannot execute SQL dynamically. Authorization of a package also allows a strict auditing of all SQL statements that will be executed against a set of tables.

In the static model, a user who has the authority to access the base table will bind the packages and become the package owner. The package owner can then grant the ability to execute the package to a runtime environment authorization ID, such as the user ID stored within the WebSphere® Application Server data source definition. The WebSphere user ID cannot execute any other SQL dynamically against the database objects outside of the SQL statements that are defined in the package.

In contrast, in a dynamic SQL environment, a user ID is used for all data access on behalf of the users connected to the application server. This user ID can execute SQL dynamically against the database outside of the application server environment.

SQL literal substitution
pureQuery Runtime has an SQL literal substitution capability that can convert non-parameterized SQL statements that the application executes into parameterized SQL statements. This capability is useful for applications that generate SQL statements on the fly during run time. Additionally, for SQL statements that execute dynamically, SQL literal substitution reduces the total number of SQL statements in the server dynamic cache, which increases the cache-hit rate for statements.
Capability to execute captured-only SQL
pureQuery client optimization has the capability to execute only captured SQL statements. This capability restricts the statements that an application can execute to those that the database administrator has approved and that are in the pureQueryXML file, even if the SQL statements execute dynamically. Executing only captured SQL improves the security of any application. This capability is useful in environments that do not support static SQL. This capability can help prevent SQL injection attacks.

pureQuery client optimization can also limit the execution of SQL statements to statements that can be executed statically.

SQL statement replacement
Using pureQuery client optimization for an existing JDBC application makes it easy to substitute executed SQL with an optimized equivalent SQL. A database administrator can replace an optimized SQL statement for a statement that is performing poorly without modifying the application.

The replaced SQL statement can contain changes like additional WHERE clauses for better index selectivity. The replaced SQL statement cannot add parameters or result columns because the metadata is already captured.

Performance benefits

In addition to the operational, tuning, and development benefits, static SQL execution can improve performance.

Performance tuning benefits include:
Elimination of runtime PREPARE and DESCRIBE

With static SQL execution, statement preparation occurs before the statement is run in the runtime environment and preparation occurs only once. As a result, the prepare and describe activities are not repeated for the SQL statement in each transaction as they are repeated with dynamic SQL execution. Static SQL execution results in a reduction of CPU consumption at both the database server where prepares occur and at the application server where a PreparedStatement object is created.

In a JDBC environment, you can try to reduce these prepared statement costs by improving SQL cache hits. However, the caching implementations do not guarantee a 100% hit ratio.

Reduced network flow
Without the need to prepare each SQL statement, there is no need to flow prepare and describe activity across the network with each statement. Therefore network traffic and transaction elapsed time are both reduced.
Predictable access path selection
SQL statement access plans are created in advance and are not created at execution time. Access paths will not change as the result of a running RUNSTATS or changes in the data distribution. A tested application can execute the same SQL statement repeatedly and the selected access paths will not change because of data variations or database maintenance.

Feedback