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
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.
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)
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:
- 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
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:
- 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
DELETE statements executed between the iBATIS
Figure 3. Default JDBC homogeneous batching
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
DELETE statements executed between the iBATIS
The IBM Integration Module implements the SqlExecutor interface to take advantage of
pureQuery batching and routes the
DELETE statements to pureQuery rather than JDBC.
Figure 4. Execution of iBATIS application with pureQuery heterogeneous batching
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.
Following are the software requirements for using the pureQuery Integration Module:
- Optim pureQuery Runtime 184.108.40.206 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)
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:
- 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
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-220.127.116.116.jar;./db2jcc.jar; ./pdqibatis.jar > java com.ibm.pdq.ibatis.utility.VersionValidator IBM integration module version: 18.104.22.168 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-22.214.171.1246.jar;./db2jcc.jar;./pdqibatis.jar > java com.ibm.pdq.utility.VersionValidator IBM integration module version: 126.96.36.199 **** 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:
- 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 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
- 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
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:
- 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-188.8.131.526.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).
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
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 the
-help option as shown in
Listing 7. List options for the
> 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
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
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=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
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
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
DELETE 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
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:
- 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|
- "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.
- 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.