Accelerate Hibernate and iBATIS applications using pureQuery, Part 2: Using the IBM Integration Module for iBATIS and pureQuery

Enable static SQL and heterogeneous batching for iBATIS applications using a new integration module

When extended with the downloadable IBM® Integration Module, the IBM Optim™ pureQuery Runtime simplifies the process of generating DB2® static SQL for Hibernate and iBATIS applications. It does this without requiring you to make changes to your application code or to gather SQL from production workloads. The Optim pureQuery Runtime also enables Hibernate and iBATIS applications that access DB2 or Informix® to benefit from the heterogeneous batching feature in pureQuery. With the heterogeneous batching feature, you can batch multiple INSERT, UPDATE, and DELETE requests before sending them across the network, even when the requests reference multiple tables. This article is part two of a two-part series. It describes using the IBM Integration Module with iBATIS applications. This article includes a downloadable sample application that illustrates how you can easily enable static SQL and heterogeneous batch functions with iBATIS applications. Part one of the series focuses on Hibernate applications.

Ambrish Bhargava (abhargav@in.ibm.com), Staff Software Engineer, IBM

Ambrish Bhargava photoAmbrish 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 (mario.briggs@in.ibm.com), Senior Software Engineer, IBM

Mario  Briggs photoMario Briggs leads the open source offerings for IBM DB2 and IBM Informix, including PHP, Ruby/Rails, Python/Django, Perl, and Java data access frameworks. He also leads the RDF support in DB2. He has about 14 years of experience in software development with many of those years spent in the area of data access, relational engines, and application-database performance.



16 September 2010

Also available in Chinese Russian

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.

Note on the iBATIS project

In early 2010, the iBATIS project moved away from Apache. It is now known as MyBatis. You can use the Integration Module with iBATIS v2.3.x, either from MyBatis or the older iBATIS.

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.


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.

Figure 1. Typical execution of an iBATIS application (dynamic)
iBATIS reads SQL strings from SQLMap files, goes to JDBC driver, and then the database.

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.
Figure 2. Static SQL execution of a iBATIS application with the integration module
iBatisGen creates PDQXML file, which creates static packages in DB2. pureQuery redirects JDBC calls to execute the static packages.

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

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.

Figure 3. Default JDBC homogeneous batching
iBATIS creates batch from statements in SQLMAP files. Then goes to JDBC driver and then the database.

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.

Figure 4. Execution of iBATIS application with pureQuery heterogeneous batching
Operations go through custom Batcher; to pureQuery runtime; to data server driver for sqlj and jdbc; to the database.

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.


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.

Listing 1. Command to run versionValidator 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.

Listing 2. Output of versionValidator program when all prerequisites are correct
> 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.

Listing 3. Output of versionValidator program with incorrect prerequisites
> 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.

Listing 5. Generate PDQXML file using iBatisGen.bat
> 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.

Listing 6. iBatisGen WebSphere Application Service default setting
>  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.

Listing 7. List options for the iBatisGen command
> 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.

Listing 8. Configure and bind the pureQuery XML file for static SQL execution
> 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.

Listing 9. pdq.properties file for 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.


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.

Listing 10. Required property in SqlMapConfig.properties to enable pureQuery batching in iBATIS
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.


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:


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.


Download

DescriptionNameSize
Sample iBATIS application for this articleDemoIbatisApp.zip22KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Java technology, Open source, IBM i
ArticleID=521835
ArticleTitle=Accelerate Hibernate and iBATIS applications using pureQuery, Part 2: Using the IBM Integration Module for iBATIS and pureQuery
publish-date=09162010