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
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
DELETErequests 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.
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.
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)
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:
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
BINDstep. The pureQuery XML capture file can be bound into static packages using the Static Binder.
- 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
The Integration Module extracts all SQL statements defined in the application's iBATIS SQLMap files except for:
- SQL statements that contain iBATIS dynamic constructs
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.
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
statements executed between the iBATIS
Figure 3. Default JDBC homogeneous batching
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
DELETE statements executed
between the iBATIS
The IBM Integration Module implements the SqlExecutor interface
to take advantage of pureQuery batching
and routes the
statements to pureQuery rather than JDBC.
Figure 4. Execution of iBATIS application with pureQuery heterogeneous batching
The Resources section contains a link you can use to download the IBM Integration Module for iBATIS and PureQuery.
Following are the software requirements for using the pureQuery Integration Module:
- Optim pureQuery Runtime 22.214.171.124 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.
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)
The DemoIbatisApp.zip file in the Download section of this article contains a sample application you can use to try out this capability.
This section uses the sample iBATIS application to help explain how the IBM Integration Module works for static SQL.
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:
- db2jcc jars
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
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-126.96.36.1996.jar;./db2jcc.jar; ./pdqibatis.jar > java com.ibm.pdq.ibatis.utility.VersionValidator IBM integration module version: 188.8.131.52 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-184.108.40.2066.jar;./db2jcc.jar;./pdqibatis.jar > java com.ibm.pdq.utility.VersionValidator IBM integration module version: 220.127.116.11 **** 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
Follow these steps to prepare the sample application's database:
- Create a database named ibatdemo.
- 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
- 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....
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
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
- 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.
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:
- 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
Listing 5. Generate PDQXML file using iBatisGen.bat
> set CLASSPATH=./iBatis-18.104.22.1686.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,
is the directory which contains all the application class files and the other files for
the application (for example, the iBATIS and XML files).
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).
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
To obtain a list of all the options that are available, issue the
iBatisGen command with
-help option as shown in Listing 7.
Listing 7. List options for the
> iBatisGen -help
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
as shown in Listing 8.
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
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
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=OFFindicates 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=STATICindicates that static execution mode should be used (instead of dynamic).
pureQueryXml=SqlMapConfig.pdqxmlprovides the name and location of the pureQuery XML file that was created when you ran the
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
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.
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
statements executed between the
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
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
Now you can run the sample application by executing MainClient.java. The pureQuery SqlExecutor ensures that heterogeneous batching occurs.
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.
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.
|Sample iBATIS application for this article||DemoIbatisApp.zip||22KB||HTTP|
"Optimize your existing JDBC applications using pureQuery" (developerWorks, August
2008) is a must-read tutorial on the process of client optimization.
JPA and pureQuery: Leveraging DB2 static execution for the Java Persistence API"
(developerWorks, December 2008).
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
Static Binder, and
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
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.
- 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.
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.