Introduction

IBM WebSphere Application Server V7 delivers an enhanced Java Persistence API (JPA) implementation that supports pureQuery and, therefore, static SQL for DB2. This article describes how you can take advantage of the performance and security of static SQL, offered via IBM Data Studio pureQuery Runtime, while at the same time using JPA for its complete object-relational capabilities. It’s almost like having your cake and eating it, too.

Following some brief introductory information on static SQL and pureQuery. this article compares and contrasts dynamic (JDBC) access versus static (pureQuery) through JPA in more detail. You will see how to use the static generator (wsdb2gen) utility in WebSphere Application Server V7 to generate SQL, and then how to bind the generated SQL into DB2 packages. The article concludes with a brief overview on how to use the pureQuery client optimization capability as a complement to the static generator capability described here.

Prerequisites

This article assumes a basic familiarity with JPA. See Resources for helpful background information. To perform the steps in this article, you will need to have pureQuery Runtime. For the purpose of this article, you can use the pureQuery Runtime contained in Data Studio Developer, which is available for download in a trial version.

Back to top

The basics

What is Static SQL?

Static SQL in DB2 is a powerful capability that can streamline data access at run time by doing some of the work ahead of time, such as determining the database access path. This can make the runtime perform faster and in a more consistent manner.

The security model for static SQL is also different than for dynamic SQL. With static SQL, users only receive privileges to execute the output of the bind process, known as the package, and that package contains the SQL statements. In other words, if all access to a table is static, the DBA would not need to grant access to the entire table or view, just to the package. In addition, the more SQL that can be executed statically means less opportunity for malicious injection, which is a well-known security issue with dynamic SQL.

See Resources for more information on static SQL.

What is pureQuery?

pureQuery is a high-performance data access platform that makes it easier to develop, optimize, secure, and manage data access. It consists of:

• APIs built for ease of use and to simplify the use of best practices.
• Development tools, delivered in Data Studio Developer, for Java and SQL development.
• A runtime, delivered in Data Studio pureQuery Runtime, for optimizing database access and simplifying management tasks.

pureQuery can help you code data access in a way that helps optimize performance. In addition, the ease of switching to static SQL when accessing DB2 data helps with both performance (static generally runs faster) and with security. pureQuery has a number of benefits over JDBC and SQLJ. For example, pureQuery does not require a customizer and does not require the SQL statements to be hardcoded in the source of the application for static execution.

The pureQuery integration delivered with WebSphere Application Server V7 requires the addition of the Data Studio pureQuery Runtime, purchased separately. Data Studio Developer is currently optional.

See Resources for more information about pureQuery.

Back to top

Dynamic execution JPA: JDBC

In the Feature Pack for EJB™ 3 for WebSphere Application Server V6.1, the JPA implementation uses JDBC as the API to the database server. Each JDBC call is dynamic in nature and requires the SQL statement to be parsed and optimized at run time. Figure 2 shows the flow from a JPA application to the database using JDBC.

Back to top

Static execution JPA: pureQuery

In WebSphere Application Server V7, you have the option to choose pureQuery instead of JDBC for accessing DB2 data. The flow from a JPA application to the database using pureQuery is shown in Figure 3.

As Figure 3 shows, the process of enabling static SQL using pureQuery consists of these steps:

• A generation time step to gather the SQL and create the DB2 packages.
• A run time step, in which the JPA database calls are redirected to the pureQuery Runtime and to the appropriate DB2 package that contains the preprocessed SQL statements.

Back to top

Generation step: Collecting SQL statements and creating database packages

The JPA utility used to collect SQL statements is called the static generator and is invoked using wsdb2gen in your %WAS_HOME%\bin directory, as shown in Figure 3. This utility takes as input the persistence unit name along with other parameters, and generates an output file consisting of SQL statements required by all entity operations, including persist, remove, update, and find. It also generates the SQL statements needed in the execution of JPA named queries. The SQL statements from the dynamic queries (as opposed to the named queries) cannot be generated by the static generator. (How you can capture these statements at run time using the pureQuery client optimization capability is discussed later.)

Know that before invoking this tool, you will need to set the classpath to your pdq runtime JARs, your db2jcc JAR, and your entity class JAR (or the directory where your enhanced entity classes reside), and the location of the persistence.xml file.

For the purpose of this article, a JPA_EE_SAMPLE.ear file, included for you to download, is used as an example to walk you through the steps needed to collect statements and create packages. This sample application lets you create, update, and delete customers and retrieve orders. The persistence unit name of this sample is JPASample.

Generating SQL using static binder utility

Listing 1 shows you the syntax and how to invoke the static binder utility wsdb2gen using the command line.

C:\was70\bin>wsdb2gen
Syntax: WSDB2GEN -pu <puName> [Flags]
  Where
               puName           Persistence-unit name defined in the
                                persistence.xml

  -collection  collectionId     Collection id assigned to package names.
                                Default is NULLID.

  -url         type 2 or        The URL is used to validate generated sql.
               type 4 url       If url option is specified, if overrides
                                the url value in the persistence.xml.

                                If the url is type 4, userid and password

                                are required for connection. If the url is
                                type2, userid and password are not required.

  -user        user             The userid to connect to target datebase.
                                If user option is specified, it overrides
                                the user value in the persistence.xml.

  -pw          password         The password to connect to target datebase.
                                If password option is specified, it
                                overrides the value in the persistence.xml.

  -package     packageName      A name of 7 or fewer characters for the
                                package name. If specified, all SQL for
                                the application will be generated into
                                this single package. If package option
                                is not specified, then multiple packages
                                will be created; one for each entity
                                class. If the first 7 characters are not
                                unique, the package name will be changed
                                to ensure uniqueness.

  -help


C:\was70\bin>set classpath=C:\db2jcc.jar;C:\pdq.jar;C:\pdqmgmt.jar;%classpath%

C:\was70\bin>set classpath=C:\test1\bin;%classpath%

C:\was70\bin>wsdb2gen -pu JPASample -url jdbc:db2://localhost:50000/demodb -user
 db2admin -pw passw0rd

As you can see in Listing 1, the command used to invoke the utility for the JPASample is:

wsdb2gen –pu JPASample –url jdbc:db2://localhost:50000/demodb –user db2admin –pw passw0rd

The output of the command is a file consisting of persistence unit name followed by a suffix of .pdqxml. In this case, the file is named JPASample.pdqxml. The pdqxml file is written to the same directory as your persistence.xml file. The pdqxml file needs to be packaged inside your archive file in the same location as the persistence.xml configuration file, usually the META-INF directory.

Binding the packages using the command line

The PureQuery static binder will use the pu.pdqxml file to create DB2 packages. The static binder can be invoked from inside the WebSphere Application Server administrative console or by using administration commands:

1. Start wsadmin from the command line by entering:

   wsadmin -lang jython

2. To invoke the static binder, you need to know the bind files in the application. To list pureQuery bind files in the JPA_EE_SAMPLE application, enter this command:

   print AdminTask.listPureQueryBindFiles('[-appName JPA_EE_SAMPLE]')

3. Once you have the bind files, you can use the command below to invoke static binder on them:
   
   Listing 2. Command to invoke static binder
   

   print AdminTask.processPureQueryBindFiles( '[-appName JPA_EE_SAMPLE -classpath [C:\\pdq.jar C:\\pdqmgmt.jar C:\\db2jcc.jar] -url jdbc:db2://localhost:50000/demodb -user db2admin -password passw0rd -options [-bindoptions "BLOCKING NO"] -files [JPA_W_EJB.jar\\META-INF\\JPASample.pdqxml] ]')

   
   

   Note: the pdqmgmt.jar constains the license needed to run the pureQuery Runtime.

4. You can use this command to display the complete syntax for processPureQueryBindFiles:

   print AdminTask.help('processPureQueryBindFiles')

Listing 3 shows you the commands described above and the output. You can see that, by default, four flavors of each package are created; the numerals 1 through 4 are appended to each package to distinguish the isolation level for that package, one for each database locking isolation level.

C:\was70\bin>wsadmin -lang jython

wsadmin>print AdminTask.listPureQueryBindFiles('[-appName JPA_EE_SAMPLE]')
JPA_W_EJB.jar\META-INF\JPASample.pdqxml


wsadmin>print AdminTask.help('processPureQueryBindFiles')
WASX8006I: Detailed help for command: processPureQueryBindFiles

Description: Process the pureQuery bind files that are in an installed application.  Bind 
static SQL packages in a database.  Refer to the information center documentation for the 
pureQuery bind utility.

Target object:   None

Arguments:
  *appName - The name of an installed application that contains the pureQuery bind files 
to be processed.
  classpath - A list of the paths to the .jar files that contain the pureQuery bind 
utility and its dependencies: pdq.jar, pdqmgmt.jar, and db2jcc4.jar or db2jcc.jar.  
Use / or \\ as a file separator.  Use a blank space to separate the paths for the .jar 
files.
  *url - The URL for connecting to the database.  The format is jdbc:db2://server_name:
port/database_name.
  user - The user name for connecting to the database.
  password - The password for connecting to the database.
  options - Any additional options that are needed by the pureQuery bind utility.  
Provide bind options as: -bindoptions "options-string".  Refer to the information center 
documentation for the pureQuery bind utility.
  *files - A list of the names of the pureQuery bind files to be processed.  The bind 
file path names must be relative to the application .ear file that contains them.  
Use / or \\ as a file separator.  If you specify multiple file paths, use a blank space 
to separate them.

Steps:
  None

wsadmin> wsadmin>print AdminTask.processPureQueryBindFiles('[-appName JPA_EE_SAMPLE
 -classpath [C:\\pdq.jar C:\\pdqmgmt.jar C:\\db2jcc.jar ] -url jdbc:db2://localhost:50000
/demodb -user db2admin
 -password passw0rd -options [-bindoptions "BLOCKING NO"] -files [JPA_W_EJB.jar\\META-INF
\\JPASample
.pdqxml ]]')

IBM Data Studio pureQuery Runtime 1.2 build 1.3.98
Licensed Materials - Property of IBM
5724-U16
(c) Copyright IBM Corp. 2006, 2008 All Rights Reserved.


================================================================================


Package 'Order1' was bound successfully for isolation level UR.
Package 'Order2' was bound successfully for isolation level CS.
Package 'Order3' was bound successfully for isolation level RS.
Package 'Order4' was bound successfully for isolation level RR.
Package 'Custome1' was bound successfully for isolation level UR.
Package 'Custome2' was bound successfully for isolation level CS.
Package 'Custome3' was bound successfully for isolation level RS.
Package 'Custome4' was bound successfully for isolation level RR.
Package 'pkgseq1' was bound successfully for isolation level UR.
Package 'pkgseq2' was bound successfully for isolation level CS.
Package 'pkgseq3' was bound successfully for isolation level RS.
Package 'pkgseq4' was bound successfully for isolation level RR.
Bind completed successfully for the file 'META-INF\JPASample.pdqxml'.

Bind succeeded for <META-INF\JPASample.pdqxml>.

================================================================================



Binding results:

    Number of implementation classes and pureQueryXml files for which bind SUCCEEDED: 1
    Number of implementation classes and pureQueryXml files for which bind FAILED: 0


ADMA0510I: Results from pureQuery bind processing for JPA_W_EJB.jar\META-INF\JPASample.
pdqxml.  Return code: 0  Exception: null
ADMA0509I: The pureQuery bind operation on application JPA_EE_SAMPLE completed 
successfully.  Exit code: 0
0

wsadmin>

Binding the packages using the admin console

You might find it easier to use the WebSphere Application Server admin console to bind your packages:

1. Select and double-click the JPA_EE_SAMPLE application (Figure 4).
   
   Figure 4. Select the sample application
   
   
   
2. Double-click SQLJ profiles and pureQuery bind files under Database Profiles in the bottom right corner (Figure 5).
   
   Figure 5. Select SQLJ profiles and pureQuery bind files from admin console
   
   
   
3. The admin console automatically displays the pdqxml file that is packaged in your application. Select the pdqxml file and click the Add button (Figure 6).
   
   Figure 6. Select the pdqxml file for your application
   
   
   
4. Your pureQuery bind file (JPASample.pdqxml) is selected and ready to be bound. Enter values for Database URL, User ID, password, and set the classpath to pdq.jar; pdqmgmt.jar; db2jcc.jar. Click OK. (Figure 7)
   
   Figure 7. Use admin console to create DB2 packages
   
   
   
5. Figure 8 shows you that the bind succeeded.
   
   Figure 8. Bind succeeds and DB2 packages are generated
   
   
   

Optional: Customizing bind options

You can provide a bindprops file in your EAR file to fine-tune your bind options, or to bind more than one pdqxml file. Listing 4 is a sample bindprops file that lists three pdqxml files and different bind options for each pdqxml file.

# Artifact 1
META-INF\myPureQueryXmlFile1.pdqxml = -rootPkgName myPkg1 -bindOptions "bind options" 
-generateDBRM true -outputDBRMPath C:\myDRMDirectory5\myDBRMSubDirectory1 -isolationLevel
UR

# Artifact 2 
META-INF\myPureQueryXmlFile2.pdqxml = -rootPkgName myPkg2 -pkgVersion 
-ver6 

# Artifact 3 
META-INF\JPASample.pdqxml =

You can package the bindprops file along with your persistence.xml file and the pdqxml (JPASample.pdqxml in this case), either:

• In the META-INF directory of the EJB JAR file (JPA_W_EJB.jar/META-INF), as shown in Figure 9.
• Directly in the root of the EAR file (JPA_EE_SAMPLE.ear), as shown in Figure 10.

Either way, WebSphere Application Server will automatically detect the bind file and display it accordingly.

With the pdqxml files in the bindprops file bound successfully, you are ready to run your application using static SQL.

Back to top

Execution step: Run JPA application with pureQuery

Grant execute authority on the package (optional)

In Listing 3, you bound the SQL statements in JPASample.pdqxml without granting any privileges to the package, which means that the package owner is the only one who has execute privileges on the package. To grant package execution privileges to the public, you can use the grant keyword. For example, the following command grants execute privileges on the package to public:

wsadmin>print AdminTask.processPureQueryBindFiles('[-appName JPA_EE_SAMPLE -classpath
[C:\\openjpadep\\pdq.jar C:\\openjpadep\\pdqmgmt.jar C:\\openjpadep\\db2jcc.jar ] -url 
jdbc:db2://localhost:50000/demodb -user db2admin -password passw0rd -options 
[-bindoptions "BLOCKING NO" -grant "grantees(public)"] -files [JPA_W_EJB.jar\META-INF\
JPASample.pdqxml]]')

To grant to specific users (such as admf002 and admf003), you would enter:

-grant "grantees(admf002, admf003)"

The grant option to the static binder can be added using the WebSphere Application Server admin console. Again, in Figure 7, no additional option is provided, so only the owner has the execution privilege to the package. Figure 11 shows that -grant grantee(public) is added in Additional options, which gives execute privileges on the packages to the public.

How to run the sample application

You can directly deploy the sample application included with this article on to WebSphere Application Server V7, or you can import the .ear file into your IBM Rational® Application Developer 7.5 workspace. After importing the .ear sample file into your workspace, you can run the Web project, JPA_W_EJB_WEB, on WebSphere Application Server V7. You can also type this URL into your browser to run the sample application: http://localhost:9080/JPA_W_EJB_WEB/.

To verify that the SQL ran, turn on WebSphere trace by navigating to Logging and Tracing => server1 => Diagnostic trace service => change log details levels, and set:

*=info: JPA=all: openjpa=all: SystemErr=all: SystemOut=all: com.ibm.pdq=all

After creating a customer, open the WebSphere trace.log file. The following trace indicates that the SQL to create a customer is indeed run statically.

[11/24/08 12:07:30:239 PST] 00000016 OpenJPA 3 wsjpa.pdq: Trace: PDQ static sql: INSERT INTO SALES.CUSTOMER (id, name) VALUES (?, ?)

Back to top

Using pureQuery client optimization to find and bind additional SQL (optional)

Admittedly, not every SQL statement generated from your application will be executed statically in the way described above. For example, dynamic JPQL queries are not gathered by wsdb2gen. Depending on your application, you might be happy with the results you get using wsdb2gen. However, if you have a significant amount of SQL that cannot be gathered using the static generator, you can capture the SQL during execution using the pureQuery client optimization capability in Data Studio Developer and pureQuery Runtime. An overview of the steps are provided here, but you are strongly encouraged to complete the client optimization tutorial.

As mentioned earlier, with client optimization you capture SQL while the application is running, preferably with a good set of tests that exercise most or all use cases. The output of that process is a pdqxml file that contains the successfully executed dynamic SQL statements. You can then bind that file as described above, or by using the Data Studio Developer tools (as described in the tutorial).

Figure 13 shows the flows from application to the database using pureQuery client optimization.

Enabling client optimization using the command line

1. Enable pureQuery capture. The pureQuery capture functionality can be enabled in the connection URL. Listing 6 shows how to turn on the capture functionality in the ConnectionProperties in your persistence.xml file.
   
   Listing 6. Enabling SQL capture in the connection URL
   

   <!-- PDQ dynamic mode --> <property name="openjpa.ConnectionProperties" value="DriverClassName=com.ibm.db2.jcc.DB2Driver,Url='jdbc:db2://localhost: 50000/demodb:pdqProperties=executionMode(DYNAMIC),captureMode(on),pureQueryXml (c://mycapture.pdqxml);',Username=db2admin,Password=passw0rd" />

   
   

   Notice that the pdqProperties are appended to the normal connect URL to enable the capture function and to specify the name of the output file where the captured SQL statements are written.

2. After enabling the capture function, all you have to do is to run your application. During execution of your application, pureQuery will capture all the successfully executed dynamic SQL statements; in other words, since the statements you gathered with static binder have already been converted to static, they will not be captured again. Those statements will be written to the output file as specified in your pdqProperties. As shown in the example above, you will find a file called mycapture.pdqxml created in the c drive (c://mycapture.pdqxml).
3. Set the classpath to your pdq.jar and pdqmgmt.jar files and run the Configure utility to add collection, package, and section number information to the previously captured information, as shown in Listing 7.

   Important: Be sure to use a different package name or collection ID than you used for the static generator bind to ensure that unique packages are created for the captured SQL versus the packages you created for the generated SQL. If you don’t keep these unique, you will likely see SQLCODE -818 when you execute the application.

   
   
   Listing 7. Configuring mycapture.pdqxml
   

   C:\test>java com.ibm.pdq.tools.Configure -pureQueryXml C://mycapture.pdqxml -rootPkgName pkg1 -collection coll1

   
   
4. Bind mycapture.pdqxml at the server where the application is to execute.
   
   Listing 8. Binding mycapture.pdqxml to create DB2 packages
   

   C:\test>java com.ibm.pdq.tools.StaticBinder -pureQueryXml C://mycapture.pdqxml -user db2admin -password passw0rd -url jdbc:db2://localhost:50000/demodb

   
   

   At this point, you should have successfully bound to the database the SQL statements that were missing during the generation time using static generator.

5. Now, when you run your application again, be sure to turn off capture mode, as shown in Listing 9.
   
   Listing 9. Run the application with capture turned off
   

   <!-- PDQ static mode --> <property name="openjpa.ConnectionProperties" value="DriverClassName=com.ibm.db2.jcc.DB2Driver,Url='jdbc:db2://localhost: 50000/demodb:pdqProperties=executionMode(STATIC),captureMode(off),pureQueryXml (c://mycapture.pdqxml);',Username=db2admin,Password=passw0rd" />

   
   
6. Optional. To verify that all SQL statements are executed statically, add allowDynamicSQL(false) to your pdqProperties, as shown in Listing 10. With the property set this way, any SQL statements from the application that are not bound to the database will result in an SQL error. This is an effective way to guard against malicious SQL injection.
   
   Listing 10. Disallow any dynamic execution
   

   <!-- PDQ static mode --> <property name="openjpa.ConnectionProperties" value="DriverClassName=com.ibm.db2.jcc.DB2Driver,Url='jdbc:db2://local host:50000/demodb:pdqProperties=executionMode(STATIC),captureMode(off), allowDynamicSQL(false),pureQueryXml(c://mycapture.pdqxml);',Username=db2admin, Password=passw0rd" />

   
   

Enabling client optimization using the admin console

For J2EE™ applications, you can enable and disable capture mode by setting pdqProperties as a DataSource CustomProperties in WebSphere Application Server. After the SQL statements are captured in the output file, you can then configure and bind them as described in Listings 7 and 8, and run your application in the production environment.

1. To set pdqProperties in WebSphere Application Server, click on Data Sources under JDBC within the Resources category in the left panel.
2. Select the Data source used by your application, which is BasicPdqXADataSource in this example, and double-click (Figure 14).
   
   Figure 14. Select the Data source (BasicPdqXADataSource)
   
   
   
3. To add pdqProperties to the BasicPdqXADataSource, navigate to Additional Properties => Custom Properties on the right side of the console (Figure 15).
   
   Figure 15. Add pdqProperties to the BasicPdqXADataSource
   
   
   
4. Click New to add new Custom Properties, then enter or select these values, as shown in Figure 16:
   • Name: pdqProperties
   • Value: executionMode(DYNAMIC),captureMode(on), pureQueryXml(c://mycapture.pdqxml)
   • Type: java.lang.String
   
   
   Figure 16. Add pdqProperties to custom properties to enable capture of dynamic SQL
   
   
   
5. Click OK to save this property. You will need to restart the server for this property to take effect.
6. At this point, you would run the application, then configure and bind the capture file as described earlier.
7. After you have bound packages and are ready to run the application again, turn off capture mode and specify static execution, as shown in Figure 17.
   
   Figure 17. Turn off capture mode in pdqProperties
   
   
   

Back to top

Summary

pureQuery has emerged as IBM’s new paradigm for Java data access, and includes tools, a runtime, and an API. One key advantage of pureQuery over SQLJ is the ease of enabling static SQL access in a way that is more streamlined.

This article discussed the seamless integration between pureQuery and JPA and presented two approaches to enabling static execution for your JPA applications that access DB2 data servers. The static generator in WebSphere Application Server V7 provided a way to get going quickly with pureQuery, as the wsdb2gen utility can introspect your entity classes, generate appropriate CRUD statements from your entity classes, and also generate SQL for named queries. pureQuery client optimization, on the other hand, captures actively executing SQL statements. Rather than generating all possible SQL, it instead captures only what runs successfully. Ideally, it requires that you have good test cases that exercise all possible code paths.

These two approaches are complementary. As mentioned earlier, some SQL might not be generated from the static generator (for example, dynamic JPQL). You can run wsdb2gen and bind the SQL statements first, and then turn on client optimization to capture any SQL statements missing by the wsdb2gen.

We encourage you to give both approaches a try.

Back to top

Download

Information about download methods

Resources

Learn

• "No Excuses" Database Programming for Java: Make your programs fly with static SQL and pureQuery, IBM Database Magazine, May 2008
  
  
• The Easy Way to Quick Data Access
  
  
• Tutorial: Optimize your existing JDBC applications using pureQuery: Improve security and performance for existing JDBC applications
  
  
• Design enterprise applications with the EJB 3.0 Java Persistence API
  
  
• Leveraging OpenJPA with WebSphere Application Server V6.1
  
  
• Pro EJB 3 Jave Persistence API, Mike Keith and Merrick Schincariol, 2006, APress
  
  

Get products and technologies

• Download IBM Data Studio Developer and pureQuery Runtime trial version
  
  

Discuss

• Blog: WebSphere and Java Peristence
  
  
• Blog: Managing the data lifecycle: from design to deletion
  
  
• Space: IBM Data Studio
  
  
• Forum: IBM Data Studio - Design, Develop, Manage, and Govern your data
  
  
• Forum: WebSphere Application Server
  
  

About the authors

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• The basics
• Dynamic execution JPA: JDBC
• Static execution JPA: pureQuery
• Generation step: Collecting SQL statements and creating database packages
• Execution step: Run JPA application with pureQuery
• Using pureQuery client optimization to find and bind additional SQL (optional)
• Summary
• Download
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into WebSphere on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers