Integrating JPA and Purequery: Let the Java Persistence API take advantage of DB2 static execution

Ibm®websphere®application Server V7 delivers enhancements to its java™persistence API (JPA), supports optimization of data access to IBM®DB2 data servers to improve security, and has significant data-reduction visits Ask for the potential of overhead. The way to achieve optimization is to support static SQL access by using the built-in integration of WebSphere JPA with the IBM Data Studio pureQuery Runtime-all without changing the application code or running a wide range of test cases.

Introduction

IBM WebSphere Application Server V7 delivers an enhanced Java persistence API (JPA) implementation that supports PureQuery, which can support static SQL for DB2. This article describes how to leverage the performance and security of static SQL provided by the IBM Data Studio pureQuery Runtime, while using JPA to implement its complete object-relational functionality. It's almost as easy as picking up your cake and eating it.

After briefly introducing some information about SQL and pureQuery, this article compares and contrasts dynamic (JDBC) access to static (PureQuery) access through JPA. You will learn how to use the Static Builder (Wsdb2gen) utility in the WebSphere application Server V7 to generate SQL, and then learn how to bind the generated SQL to the DB2 package. This article concludes with a brief overview of how to use the PureQuery client optimization feature as a complement to the static generator functionality described here.

Prerequisite

This article assumes that the reader is basically familiar with JPA.

Basis

What is static SQL?

Static SQL in DB2 is a powerful feature that allows you to simplify run-time data access by doing some work beforehand, such as determining the database access path. This allows the runtime to execute faster and more consistently.

Figure 1. Static execution is more efficient than dynamic execution

The security model for static SQL is also different from the security model of dynamic SQL. With static SQL, the user receives only the permission to execute the bound process output, called a package, and the package contains the SQL statement. In other words, if all access to a table is static, the DBA will not need to grant access to the entire table or view, but to grant access to the package only. In addition, the more statically executed SQL means less chance of malicious injection, a well-known security issue for dynamic SQL.