Introduction

The IBM Integration Module for iBATIS and pureQuery helps you accelerate and better manage your iBATIS applications. It does this through the use of DB2 static SQL and pureQuery heterogeneous batching. Static SQL provides both performance and security benefits for applications that access DB2. Heterogeneous batching can help performance for both DB2 and Informix database applications.

The first article in the series focuses on Hibernate applications. The first article also covered the following two topics, which are not repeated in this second article:

• A review of the pureQuery platform, which consists of development tools, a Java® API, a runtime, and monitoring services that you can use to visualize performance metrics and correlate SQL statements with originating Java code
• How you can obtain the benefits of pureQuery without having to use the pureQuery API, through such capabilities as client optimization to capture SQL and other technologies such as those provided by the IBM Integration Module

Review of the integration modules

The first article described two ways you can take advantage of pureQuery for existing applications that are not written with the pureQuery API:

• Use client optimization capture while the application is executing.
• Use integration modules to enable programs to take advantage of the full power of pureQuery and Optim Development Studio to visualize and optimize SQL without performing a runtime capture step. With this approach, the integration module extracts the SQL and its metadata before the Hibernate or iBATIS program is run. This is a new approach and is the focus of this article series.

The IBM Integration Module for iBATIS and pureQuery enables the following benefits for your iBATIS applications:

• Your iBATIS applications can use static SQL without the need for an initial pureQuery client optimization capture step. Instead of using a capture step, the Integration Module extracts the SQL from the iBATIS SQLMap files. The SQL can be bound statically for access to DB2 databases and can be reviewed, shared, optimized, and replaced without changing the application itself. The SQL can also be correlated with application code to trace performance problems back to your iBATIS source code, or it can be correlated with database object changes for impact analysis using Optim Development Studio
• Your iBATIS applications can use the heterogeneous batching feature in pureQuery. This feature enables you to batch multiple INSERT, UPDATE, and DELETE requests even when they reference multiple tables. With heterogeneous batching, these updates across tables can be done in a single trip to the database. Each network round trip to the database server is expensive in terms of performance. So having fewer but larger batches and round-trips is better for performance. This support is available for DB2 and Informix database servers.

This article describes how to set up and use the IBM Integration Module for iBATIS and pureQuery. First it describes the static SQL feature of the integration module and then it describes the heterogeneous batching feature. The article assumes that you have some knowledge of iBATIS V2.3, and the instructions assume you are using iBATIS V2.3.x. You can take advantage of the benefits provided by the Integration Module without making changes to your application code.

Back to top

Creating static SQL using the Integration Module for iBATIS and pureQuery

This section shows you the difference between the default execution path of an iBATIS application using dynamic SQL and one that uses the Integration Module to create and use static SQL. For background on static SQL and its potential performance benefits, refer to the article on static SQL that is linked to from the Resources section of this article.

Dynamic SQL execution using JDBC

Figure 1 shows the high level execution of a typical iBATIS application. All the SQL statements written in the iBATIS SQLMap files are executed against the database using JDBC to prepare and execute the operations.

Static SQL execution using the Integration Module and pureQuery

Figure 2 shows the high level execution of an iBATIS application with the Integration Module. The DB2 packages have been created for static SQL execution. The figure depicts the following:

1. An SQL generation step (iBatisGen) gathers the SQL that the iBATIS application uses into a pureQuery XML file (these files have a file type of .pdqxml). This file can then be used in Optim Development Studio for tuning or impact analysis without requiring a BIND step. The pureQuery XML capture file can be bound into static packages using the Static Binder.
2. During application execution, the pureQuery Runtime redirects the JBDC calls to execute the DB2 static packages that were created in the generation step. The pdq.properties file controls the pureQuery Runtime execution behavior (dynamic or static) and also provides the location of the pureQuery XML file.

Which SQL is extracted by the Integration Module?

The Integration Module extracts all SQL statements defined in the application's iBATIS SQLMap files except for:

• CALL, VALUES, and DDL SQL statements
• SQL statements that contain iBATIS dynamic constructs

Back to top

Overview of heterogeneous batching enablement using the Integration Module

As described earlier, pureQuery heterogeneous batching can help you save on network costs. This section shows you the difference between the default batching used in an iBATIS application and pureQuery heterogeneous batching.

Default batching in iBATIS using JDBC

Figure 3 shows the high level execution flow of a sample iBATIS application without pureQuery heterogeneous batching. The default SqlExecutor in iBATIS uses JDBC homogeneous batching to batch the INSERT, UPDATE, and DELETE statements executed between the iBATIS startBatch and executeBatch calls.

Heterogeneous batching in iBATIS using pureQuery

Figure 4 shows the high level execution flow of a sample iBATIS application with pureQuery heterogeneous batching. The pureQuery SqlExecutor uses heterogeneous batching to batch the INSERT, UPDATE, and DELETE statements executed between the iBATIS startBatch and executeBatch calls.

The IBM Integration Module implements the SqlExecutor interface to take advantage of pureQuery batching and routes the INSERT, UPDATE, and DELETE statements to pureQuery rather than JDBC.

Back to top

Prerequisites to use the IBM Integration Module for iBATIS and pureQuery

The Resources section contains a link you can use to download the IBM Integration Module for iBATIS and PureQuery.

Software

Following are the software requirements for using the pureQuery Integration Module:

• Optim pureQuery Runtime 2.2.0.3 or later. (Refer to the Resources section for a link to download a 30-day trial version of IBM Optim Development Studio and Optim pureQuery Runtime. The runtime must be on the same machine as Optim Development Studio.)
• Ensure that you have pdq.jar and pdqmgmt.jar in your class path.
• IBM Data Server Driver for JDBC and SQLJ 3.58 or 4.7 (available with Optim Development Studio, or refer to the Resources section for a link to download).
• iBATIS V2.3.x.xxx.

Supported databases

The IBM Integration Module for iBATIS and pureQuery works when the application is accessing any of the following database systems:

• DB2 for Linux®, UNIX®, and Windows® V8.2, Fix Pack 11 or later
• DB2 for Linux, UNIX, and Windows V9.1, V9.5, or V9.7
• DB2 for i V5R4 (heterogeneous batching feature only)
• DB2 for z/OS® V8 or later
• Informix Dynamic Server V11.10 or V11.5 (heterogeneous batching feature only)

Sample application used in this article

The DemoIbatisApp.zip file in the Download section of this article contains a sample application you can use to try out this capability.

Back to top

Enabling pureQuery static SQL for iBATIS applications

This section uses the sample iBATIS application to help explain how the IBM Integration Module works for static SQL.

Configure the class path

To compile the sample application, you need to have the following jar files in your class path along with the other jar files required by the iBATIS application:

• iBATIS jar file (for example, iBatis-2.3.x.xxx.jar)
• pdqibatis.jar (shipped with the integration module)

To use the IBM Integration Module, you also need to include the following in your class path:

• pdq.jar
• pdqmgmt.jar
• db2jcc jars

Use the versionvalidator utility to check prerequisites

The Integration Module contains a program named versionValidator that performs a prerequisite check of the class path for the version of iBATIS, JDBC Driver, and pureQuery Runtime. Listing 1 shows the command to run the program.

java com.ibm.pdq.ibatis.utility.VersionValidator

If the jar files that are included in your class path are at the correct level, the versionValidator program displays messages similar to those shown in Listing 2.

> set CLASSPATH=./pdq.jar;./pdqmgmt.jar;./ibatis-2.3.4.726.jar;./db2jcc.jar;
./pdqibatis.jar

> java com.ibm.pdq.ibatis.utility.VersionValidator

IBM integration module version: 2.2.0.3

Found pdqmgmt.jar

PDQ runtime version: 2.18.120

IBM DB2 JDBC Universal Driver: 3.58

java version: 1.6.0

If any of the jar files that are included in your class path are not at the correct level, the versionValidator program displays an appropriate error message. For example, the messages in Listing 3 indicate that the DB2 JDBC driver is not at the correct level to work with the Integration Module and that the required PDQ jar files are not in the class path.

> set CLASSPATH=./ibatis-2.3.4.726.jar;./db2jcc.jar;./pdqibatis.jar

> java com.ibm.pdq.utility.VersionValidator

IBM integration module version: 2.2.0.3 

**** ERROR. pdqmgmt.jar not found in classpath 

**** ERROR. pdq.jar not found in class path

Found IBM DB2 JDBC Universal Driver version: 3.53 **** ERROR. Incorrect level. Requires 
V3.58 or higher

java version: 1.6.0

Prepare the sample application's database

Follow these steps to prepare the sample application's database:

1. Create a database named ibatdemo.
2. Unzip the DemoiBatisApp.zip file and import the sample application as a Java project into your Optim Development Studio workspace. The sample application uses the following Java beans:
   • CustomerInfo with subclass Customer
   • District
   • Item
   • Order
3. Include the same jar files listed in Configure the class path in the project build path. You can add the pureQuery and IBM Data Server for JDBC and SQLJ jar files in the project build path by right-clicking on the project and selecting pureQuery > Add pureQuery support....
4. Edit the SqlMapConfig.properties file to specify the correct database properties for your system. For the sample application, this file is located at src\demo\data\SqlMapConfig.properties and is shown in Listing 4. This file is required for other steps of this process to work correctly.
   
   Listing 4. SqlMapConfig.properties file details
   

   driver=com.ibm.db2.jcc.DB2Driver url=jdbc:db2://localhost:50000/ibatdemo dataSourceType=SIMPLE username=db2admin password=db2admin #sql_executor_class=com.ibm.pdq.ibatis.executor.PDQSqlExecutor

   
   
5. Create the database tables for the sample application. Create the tables in the database by running demo.DatabaseSetup.java. To run demo.DatabaseSetup.java on Optim Development Studio, right-click on the file and select Run As > Java Application. demo.DatabaseSetup.java creates all the required tables for the sample application in the database.

Generate the pureQuery XML file for static SQL execution

Use the iBatisGen command to generate a test.pdqxml file for the sample application. The batch file for the command (iBatisGen.bat) is shipped with the Integration Module. Your class path needs to contain all the jar files required by iBATIS along with the following:

• pdqibatis.jar
• pdq.jar and pdqmgmt.jar
• db2jcc.jar and db2jcc_license_cisuz.jar

Listing 5 shows the commands for setting the class path to include the required jar files and execute the iBatisGen command.

> set CLASSPATH=./iBatis-2.3.4.726.jar;./db2jcc.jar;./db2jcc_license_cisuz.jar;
./pdq.jar;./pdqmgmt.jar;./pdqibatis.jar;

> set CLASSPATH=bin;%CLASSPATH%

> iBatisGen -configFile demo/data/SQLMapConfig.xml

In the above code, bin is the directory which contains all the application class files and the other files for the application (for example, the iBATIS and XML files).

After the iBatisGen command completes, verify that it has created a file named SQLMapConfig.pdqxml in the current working directory (the directory where the iBatisGen.bat file is present).

Note: iBatisGen generates the SQL with the JDBC default for resultsetHoldability. However, WebSphere® Application Server uses a different default for resultsetHoldability. If you are deploying your iBATIS application on WebSphere Application Server, check the resultsetHoldability of the WebSphere Application Server datasource and specify it on the iBatisGen command as shown in Listing 6.

>  iBatisGen -configFile demo/data/SQLMapConfig.xml -resultsetHoldability 2

There are various other options you can use with the iBatisGen command. To obtain a list of all the options that are available, issue the iBatisGen command with the -help option as shown in Listing 7.

> iBatisGen -help

Configure and bind the pureQuery XML file for static SQL execution

In order to run the application statically, you first need to configure and then bind the generated SqlMapConfig.pdqxml. Do this using the standard pureQuery Configure and StaticBinder commands as shown in Listing 8. The StaticBinder command creates the packages on the DB2 server. Make sure that pdq.jar, pdqmgmt.jar, and the corresponding JCC driver jar files are in the class path when you run these commands. For more information on the pureQuery Configure and StaticBinder commands, refer to the Resources section.

> java com.ibm.pdq.tools.Configure 
	
	-rootPkgName test 
	
	-pureQueryXml SqlMapConfig.pdqxml

> java com.ibm.pdq.tools.StaticBinder

	-url jdbc:db2://localhost:50000/ibatdemo
	
	-username xx
	
	-password xx
	
	-pureQueryXml SqlMapConfig.pdqxml
	
	-traceFile demo_trace.txt
	
	-traceLevel ALL

Run the application using static SQL

Listing 9 shows the properties that are used in the pdq.properties file included with the sample application.

pdq.captureMode=OFF
pdq.executionMode=STATIC
pdq.pureQueryXml=SqlMapConfig.pdqxml

The following explains the properties settings:

• captureMode=OFF indicates that capture is turned off. You only need capture turned on when you are using the client optimization process that is described in the first article of this series.
• executionMODE=STATIC indicates that static execution mode should be used (instead of dynamic).
• pureQueryXml=SqlMapConfig.pdqxml provides the name and location of the pureQuery XML file that was created when you ran the iBatisGen command.

For more information on all pdq properties, refer to the Resources section.

In the actual pdq.properties file included with the sample application, all the properties are commented out (each line begins with #). Before proceeding, edit the file, delete the # characters to un-comment the three properties, and save the file. When you are done, the file should look like Listing 9.

Now you can run the sample application by executing MainClient.java. SQL statements from the application are executed statically.

To run MainClient.java on Optim Development Studio, right-click on the file and select Run As > Java Application.

Back to top

Enabling pureQuery heterogeneous batching for iBATIS applications

To take advantage of pureQuery's heterogeneous batching support, you must use iBATIS (or MyBatis) V2.3.5. The Resources section provides a link you can use to download this version.

To turn on heterogeneous batching support for INSERT, UPDATE, and DELETE statements executed between the startBatch and executeBatch calls of the iBATIS API, you register the pureQuery SqlExecutor with iBATIS. To do this, edit the SqlMapConfig properties file and set the value of the sql_executor_class property to com.ibm.pdq.ibatis.executor.PDQSqlExecutor.

The SqlMapConfig.properties file included with the sample application has this property commented out (line begins with #). To turn on heterogeneous batching support for the sample application, you can simply edit the SqlMapConfig.properties file, uncomment the property by deleting the # character, and save the file. With the comment character removed, the property would look similar to what is shown in Listing 10.

sql_executor_class=com.ibm.pdq.ibatis.executor.PDQSqlExecutor

Run the iBATIS application with heterogeneous batching

Now you can run the sample application by executing MainClient.java. The pureQuery SqlExecutor ensures that heterogeneous batching occurs.

Back to top

Using static SQL and heterogeneous batch together

Both the static SQL and heterogeneous batching features are orthogonal. This means that an iBATIS application can use both static SQL and heterogeneous batching together at the same time, or it can use each feature independently. There is no overlap of any steps in setting up each feature:

• To set up static SQL, follow the steps listed in Enabling pureQuery static SQL for iBATIS applications.
• To set up heterogeneous batching, follow the steps listed in Enabling pureQuery heterogeneous batching for iBATIS applications.
• To set up both, simply follow the steps in both sections; the order does not matter.

Back to top

Conclusion

The IBM Integration Module for pureQuery and iBATIS helps you enable static SQL execution support for iBATIS applications on DB2 servers. It enhances problem determination by linking the SQL statements to the iBATIS artifacts from where they originate, that is, the SQLMap files. You can now reap all the benefits of static SQL for IBM DB2 — improve performance by removing the need to do preparation at runtime, optimize the SQL, reduce SQL injection risk, and improve security by granting privileges only to DBMS packages.

The IBM Integration Module for PureQuery and iBATIS can deliver significant elapsed time gains on DB2 and Informix data servers where heterogeneous batching is used with suitable applications. These performance improvements apply both in JEE and J2SE environments.

Back to top

Download

Information about download methods

Resources

Learn

• "Optimize your existing JDBC applications using pureQuery" (developerWorks, August 2008) is a must-read tutorial on the process of client optimization.
  
  
• "Integrating JPA and pureQuery: Leveraging DB2 static execution for the Java Persistence API" (developerWorks, December 2008).
  
  
• "What’s new and cool in IBM Optim Development Studio 2.2" (developerWorks, June 2009) describes the new support for Oracle and other pureQuery features that help developers and DBAs work better together.
  
  
• Visit the pureQuery platform page for an overview of the pureQuery platform, including the value it brings, which products it ships with, and an FAQ.
  
  
• "Database Programming for Java" (IBM Database Magazine, May 2008) explains the benefits of Static SQL and how to exploit those benefits using Java.
  
  
• More information on pureQuery properties, Configure , Static Binder, and Merge Utility.
  
  
• developerWorks Optim family page: Learn more about Optim solutions. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Managing pureQuery client optimization in Web application environments (developerWorks, February 2010). Learn how to optimize applications on a single application server node.
  
  

Get products and technologies

• Download the IBM Integration Module for pureQuery and iBATIS.
  
  
• Download and try IBM Optim Development Studio and pureQuery Runtime for a free 30-day trial.
  
  
• Download the IBM Data Server Driver for JDBC and SQLJ (JCC Driver).
  
  
• Download MyBatis V2.3.5.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out the Managing the data lifecycle blog and get involved in the Integrated Data Management community Space, which has a comprehensive list of resources and downloads.
  
  

About the authors

Ambrish Bhargava is a Staff Software Engineer in the Open Source Team at IBM India Software Labs. He works on providing DB2 Static SQL support for iBATIS and Hibernate applications. Previously, he worked on the development of PHP extensions for IBM DB2 and Informix, and he originally developed the DB2-Django adapter. He graduated with an M.Tech in Information Technology from IIIT, Bangalore.

Mario Briggs leads the Open Source offerings for IBM DB2 and IBM Informix including PHP, Ruby/Rails, Python/Django/SqlAlchemy, Perl, and Java data access frameworks. Mario has about 11 years of experience in Software development with many of those years spent in the area of data access, relational engines, and application-database performance.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Creating static SQL using the Integration Module for iBATIS and pureQuery
• Overview of heterogeneous batching enablement using the Integration Module
• Prerequisites to use the IBM Integration Module for iBATIS and pureQuery
• Enabling pureQuery static SQL for iBATIS applications
• Enabling pureQuery heterogeneous batching for iBATIS applications
• Using static SQL and heterogeneous batch together
• Conclusion
• Download
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers