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:
- Diagnosing problems with SQL statements with facilities to trace
back to the application source code
- Replacing the poor-performing SQL statements with optimized statements
- Reducing risk of SQL injection attacks by running a restricted
set of SQL statements
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 Optim™ products)
- SQL statement retrieval
- Identifying poorly performing SQL (when working with other Optim
products)
- Capturing SQL performance data (when working with other 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 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 Optim Development Studio. You can enable pureQuery
for a Java project. Optim Development 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 Optim Development 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 Optim Development
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.