Using purequery to optimize existing JDBC applications

Before you start

PureQuery Introduction

The IBM data Studio portfolio includes PureQuery, a high-performance data access platform designed to simplify the development, management, security, and optimization of Java data access. It consists of development tools (in Data Studio Developer), a simple and intuitive API, and a run-time (in Data Studio Purequery Runtime).

The main advantage of PureQuery in optimizing Java data access and securing access for DB2 data is that it is easy to develop and deploy applications that use static SQL. When you use static SQL, the preparation steps for executing the SQL statement are performed before the application executes, so you can achieve better performance at run time. In addition, static SQL can always be known in advance, so it will not be affected by malicious or accidental SQL injection, which is the security flaw of dynamic SQL.

Previously, in order to use static SQL, the application would need to be written with an annotated PureQuery method style. In version 1.2 of data studio Developer and data Studio PureQuery Runtime, whether existing JDBC applications are directly implemented for persistence or using popular frameworks such as Hibernate and JPA to implement persistence), you can use static SQL without having to rewrite the code. This feature is sometimes referred to as client optimization, because the process of enabling static SQL occurs at the JDBC client level. We call this feature the PureQuery for JDBC applications.

The general process of enabling application PureQuery for JDBC applications features is an iterative process consisting of the following phases:

Plan. The purpose of the planning phase is to determine whether the application is suitable for use with pureQuery. While it is conceptually possible to enable this feature for any Java application that accesses data from any DB2 data server, the application must meet certain criteria, such as prerequisites (for example, the application runs in JRE 1.5 or above), security aspects (for example, DBA Or the Data Administrator agrees to use the security based on the database package rather than the security of the table/view, and so on.

Capture. When PureQuery for JDBC applications statically processes SQL statements, it relies on information about application SQL statements and their associated metadata. The capture phase gathers the necessary information and stores it in a metadata file.

Configuration. In order to execute SQL statically, you must first bind it to a database package. Before that, you can configure the captured SQL files to specify settings, such as how many SQL statements are stored in each package.

Binding. After defining the characteristics of a package, you need to bind the captured SQL and related metadata to one or more databases (or you can store it in DBRM and deploy it later).

Execution and validation. After successfully completing the above phase, the PureQuery for JDBC applications is configured so that the SQL statement can be executed statically. During the validation process, the quality assurance expert verifies that the application executes as expected and returns the expected results.

Note: In a common usage scenario, the Enable (enablement) process is an iterative process.

About this tutorial

In this tutorial, learn how to use the Data Studio Developer to allow a simple custom-developed JDBC application to use the PureQuery for JDBC applications feature. However, you can also learn this tutorial using your own selection of applications that access DB2 data on any platform. Client optimizations do not require access to the source code, but the steps in this tutorial assume that you have that access right.

Goal

The goal of this tutorial is to introduce steps to enable existing JDBC applications to support PureQuery. To make this tutorial simple, we provide a small application that uses JDBC directly to implement persistence, which meets all the requirements for enabling pureQuery and is easy to verify.

In this tutorial, you will learn how to:

Create a sample database and import the sample JDBC application into Data Studio Developer 1.2.

Run the application with a code path and capture its SQL.

In the Java source code, view the SQL of the sample application and its relationship to the Java source code (optional).

Configure the captured SQL and bind it to a database package.

Run the sample application in static SQL execution mode and verify that it has the correct behavior.

Run the application with another code path to catch the remaining SQL; then use static SQL to perform the rebind and re-run the application.