What's new and cool in Optim Development Studio, Part 2: Exploring Optim Development Studio and pureQuery Runtime Version 2.2 Fix Pack 3

Using enhanced administrative control for client optimization

New capabilities in Fix Pack 3 of IBM Optim™(TM) Development Studio and pureQuery Runtime 2.2 provide significant enhancements in security, control, and maintenance for database administrators who use pureQuery client optimization to improve security, stability, and performance of Java database applications. The enhancements are based on a new central repository for storing pureQuery metadata and properties. This article uses a scenario to illustrate some of the new capabilities made possible with Fix Pack 3.

Sonali Surange (ssurange@us.ibm.com), Software Architect, IBM

Sonali Surange is a Software Architect for Optim Development Studio at the IBM Silicon Valley Lab. Previously Sonali led the development of several key components of the Visual Studio .Net tools for IBM databases.

developerWorks Contributing author

Adekunle O Adio (aoadio@us.ibm.com), Software Engineer, IBM

Adekunle Adio is a Software Engineer at the IBM Lenexa lab. He has worked on Data Facility Storage Management Subsystem (DFSMS), IBM Migration Toolkit (MTK), and pureQuery tools. Currently, Adekunle is working on Tivoli products.

Zeus Courtois (zocourto@us.ibm.com), Software Engineer, IBM

Zeus Courtois is a Software Engineer in the Data Studio Development team working on the pureQuery tools at IBM Silicon Valley Lab in San Jose, California. Before joining this team, Zeus was an intern in the Data Studio Enablement team. Zeus holds a Masters degree in Information Systems from Texas A&M International University.

24 June 2010


Develop skills on this topic

This content is part of a progressive knowledge path for advancing your skills. See Introduction to IBM Optim Development Studio and pureQuery Runtime

pureQuery client optimization enables you to capture SQL and related metadata from a running Java application so you can use that captured SQL to help improve problem diagnosis, dependency analysis, and security by being able to specify that only captured and reviewed SQL be allowed to execute. When the target data source is DB2, the captured SQL can be bound into database packages for static SQL execution, which can help provide more consistent (or even better) performance, improved manageability of application changes, application traceability, and better security by using the static execution security model. For more details about the benefits of client optimization, see the Resources section of this article.

pureQuery client optimization is being evaluated and implemented in more and more enterprises. This article reflects the initial set of changes implemented and delivered in Fix Pack 3 of the Optim pureQuery Runtime and Optim Development Studio. The changes support complex enterprise environments to bring the process more under the control of the database administrator (DBA), thereby reducing the need to rely on an application server administrator. Continuous maintenance is also easier.

This article introduces a scenario that uses and maintains applications in production that use static SQL with pureQuery client optimization. Although this article focuses on use of Optim Development Studio to do this, all of the features introduced in this release are also available from command line to enable scripts for automation.

Overview of the scenario

The example scenario describes how to do the following:

  1. Set up the sample application by capturing the initial SQL from the sample application and bind packages
  2. Set up and populate the repository with the captured SQL and associated runtime properties
  3. Handle a change to the application and then put the changes into production

Finally this article describes new features in package management, including the following:

  • Grouping statements into separate packages based on a string or special register
  • Cleaning out old SQL from the capture file
  • Reporting on changes between a copy of a working (development) capture file and one currently in the repository


To follow the example scenario, follow these steps before you begin.

  1. Install Optim Development Studio 2.2 Fix Pack 3 (see Resources for a link to the download document for this fix pack).
  2. Connect to the GSDB sample database on DB2®. This scenario uses DB2 for Linux®, UNIX®, and Windows®. See Resources for a link to the GSDB database.
  3. Download the JDBC application code: TutMod.java and Utils.java (see Downloads).

Setting up the sample application

This section describes the steps to simulate an environment that has an application in production using static SQL. The steps instruct you to run the existing creditCard JDBC application using the client optimization capture process, replacing literals in the application with parameter markers. For more information on client optimization and the literal substitution capability, see Resources for a link to the IBM Redbooks® publication Using Integrated Data Management to meet Service Level Objectives.

Capture and bind SQL from the sample application

To create the capture file, follow these steps:

  1. From the Data Source Explorer, create a connection called CREDIT to the GSDB database.
  2. Create a Java project called creditCardApp. This holds your credit card application.
  3. Move the provided source code (TutMod.java, Utils.java) to the src folder of your creditCardApp.
  4. Right-click the creditCardApp project, and select Add pureQuery support.
  5. Follow the steps in the wizard to select the connection CREDIT and to provide the default schema value of GOSALESCT.
  6. Ensure the checkbox for Enable SQL capturing and binding for JDBC applications is checked.
  7. Enable literal replacement by following these steps:
    1. Navigate to the src folder in the creditCardApp project, and open the pdq.properties file.
    2. Add the line pdq.sqlLiteralSubstitution=ENABLE as shown in Figure 1.
Figure 1. Enabling SQL literal substitution in pdq.properties
Shows pdq.sqlLiteralSubstitution=ENABLE

Note that, by default, the capture mode is set to ON with captureMODE=ON.

  1. To capture the SQL, follow these steps.
    1. Right-click TutMod.java, and select Run As > Java Application.
    2. Pass in the following parameters when you run the application: <database> <host> <port> <username> <password>
    3. Right-click the project and select Refresh when the application is done running. The captured SQL from that run is put into a file named capture.pdqxml, which you can find in the pureQueryFolder folder.
  2. Prepare the application to run using static SQL. To create the packages, bind the application by following these steps.
    1. From the pureQueryFolder, open the Default.bindprops file and enter -bindOptions "QUALIFIER (GOSALESCT)" for the defaultOptions, as shown in Figure 2.
Figure 2. Providing bind options in Default.bindProps
Providing bind options in Default.bindProps
    1. Right-click the capture.pdqxml file, and select pureQuery > Bind.
    2. Select the CREDIT connection.
    3. Take the defaults in the wizard, and click Finish.

The SQL in the application is now collected, and packages have been created for static SQL execution of the application.

Setting up the SQL management repository

The captured SQL and associated execution properties can now be stored in a database SQL management repository, which improves security and provides a centralized control of the captured information to the DBA. Keeping the captured SQL together with the execution properties reduces errors during the bind and execution processes. The SQL management repository contains the PQ runtime configuration, PDQXML, and incremental PDQXML, as shown in Figure 3. These artifacts can be managed by Optim Development Studio or by command line tools to access the database, to create packages on the database, and so on. pureQuery Runtime also uses the artifacts to access the appropriate properties when the application executes.

Figure 3. Introducing the SQL management repository
SQL mgmt repository contains PQ runtime config, PDQXML, and Incremental PDQXML

Following are some of the advantages of using an SQL management repository:

  • Centrally managed information can be extracted and used by applications that use pureQuery client optimization.
  • The backup strategy for the information can be aligned with database backup strategies.
  • The database can enforce access control.
  • The derived data from captured SQL can be shared with the Extended Insight capability of Optim Performance Manager Extended Edition, which enables an end-to-end monitoring solution that is controlled by database administrators.

You can continue to work with your capture files in the file system during development, which was supported in the previous releases. You can then choose to upload to the SQL management repository when you are ready for deployment.

You can create the SQL management repository along with your transaction database used in production. This ensures high availability for your SQL management repository. Alternatively, you can create the SQL management repository in a separate database. For more information about flexibility in supported configurations, see the Integrated Data Management Information Center topic on client optimization listed in Resources.

The scenario in this article creates the SQL management repository in the same database that the application uses in production.

Optim Development Studio makes it easy to run the command line tools by providing a manageRepository.bat file. You can use the examples provided in the bat files as a starting point to learn the tool.

To create the repository, go to <ODSInstallLocation>\dsdev\bin and use the code in Listing 1, which is further explained below the listing.

Listing 1. Creating the repository
manageRepository -create repository
	  -repositoryType pureQueryOnly
	  -repositoryURL <url> 
	  -repositoryUsername <userid> 
	  -repositoryPassword <pwd> 
	  -repositoryDriverClass com.ibm.db2.jcc.DB2Driver

-repositoryType pureQueryOnly (the default) creates a repository that contains everything the pureQuery runtime requires and additional information about the SQL needed for problem determination and impact analysis, including SQL dependencies.

-repositoryType pureQueryRuntimeOnly creates a light repository with information needed for pureQuery client optimization runtime only.

If your repository coexists with your application's database, consider a light repository to reduce performance impact.

To optionally use static SQL when accessing the repository, use the bind command to create the database packages, as shown in Listing 2.

Listing 2. Using bind to create database packages
	-bind packages
	-repositoryDriverClass com.ibm.db2.jcc.DB2Driver 
	-repositoryURL <url> 
	-repositoryUsername <userid> 
	-repositoryPassword <pwd>

Finally, you might want to control and grant the privileges to specific users to provide specific access to the packages. For example, if you choose not to access the repository using static SQL, you need to grant privileges to the repository tables themselves. Refer to the manageRepository -grant command to perform these actions as needed. Or see the Integrated Data Management Information Center topic on client optimization listed in Resources.

You have now captured the SQL in your application and bound the SQL to create database packages in preparation to execute it using static SQL. You have also created the SQL management repository that you want to use to store your captured SQL and runtime properties.

Upload application and properties to the SQL management repository

A new Eclipse perspective, the Common Repositories perspective, enables DBAs to work with the captured SQL using a step-by-step approach presented by the user interface. You will be using this new perspective in this step of the process.

Runtime group is another new concept. A runtime group is the combination of the capture file and the associated properties as stored in the management repository. You can create multiple runtime groups to match the execution property requirements of your application. For example, for one application, you can turn on capture for a few runtime groups and keep it off on others, based on your needs.

You can also create many versions of a runtime group and mark the version that you want to use at execution time as active.

To upload the captured file and properties to the repository, follow these steps

  1. Remove the line pdq.pureQueryXml from pdq.properties. By default, when Optim Development Studio sets up the properties for static SQL execution, it points to the capture file location from your workspace. Because this location is incorrect when the capture file is uploaded to the SQL management repository, this removes the property that points to the workspace. As a result, your pdq.properties should have the contents shown in Listing 3.
Listing 3. pdq.properties file with capture file location removed
  1. Create a connection to point to the database that contains the repository by following these steps:
    1. Click Window > Open Perspective to open the Optim Common Repositories perspective.
    2. Go to the Common Repository Explorer, and right-click Common Repository Connections.
    3. Click New.
    4. Create a connection to the repository that you created in the Set up the SQL management repository section, and click Finish, as shown in Figure 4.
Figure 4. Create a connection to the SQL management repository
select connections previous defined, or define new ones
  1. To create a runtime group called CREDIT_RG, follow these steps:
    1. Right-click the SQL Management node, and select New Runtime Group.
    2. Enter the runtime group name and version.
    3. Select the properties files from your project for upload, as shown in Figure 5.
Figure 5. Steps to create a runtime group
shows selecting properties files
    1. Click Finish.
    The captured SQL and associated runtime properties are now uploaded to the repository.
  1. Right-click the new runtime group version, and select Mark Active to mark the runtime group version as active, as shown in Figure 6.
Figure 6. Mark runtime group version Active
Mark runtime group version Active

When the application is executed, pureQuery runtime uses this captured file (pureQueryXml) and its associated properties from the SQL management repository.

Handling subsequent changes to an application in production

This section describes how to make a change to the application in production and how such changes can now be managed easily. For the example scenario, a few months after the application has been in production, new function (including new SQL) is added to the application, which now needs to be deployed. This section describes how to replicate that environment here and how to use the new fix pack features to deploy the changes to the application in production.

Create a change in the production application

In this section, you uncomment the update and delete methods to create additional SQL. You then run the application in capture mode to collect the newly added SQL. You have the option to save the newly captured SQL directly in the SQL management repository runtime group. Follow these steps:

For more information

A lot of flexibility is provided to control when and how changes are updated to the database. For an in-depth list of options, see the client optimization topic in the IDM Information Center listed in Resources.

  1. Uncomment the basicUpdate and basicDelete methods in TutMod.java.
  2. Add the values to pdq.properties in the creditCardApp as shown in Listing 4.
Listing 4. pdq.properties repository values
pdq.finalRepositoryProperties = <url>;user=<user>;password=<pwd>;
  • finalRepositoryProperties specifies the location to use when retrieving pureQuery runtime properties and pureQueryXML file information.
  • propertiesGroupId specifies the runtime group ID in an SQL management repository to use when retrieving data or storing data to an SQL management repository.
  • repositoryRequired specifies when the SQL management repository is required.
  1. Run the application again. The capture file with the new SQL is saved in the SQL management repository.

Handle the change in the production application

This section describes how to look at all the incrementally collected SQL in the repository and how to set up the configuration and bind properties required for the newly captured SQL. You also learn how to update (merge the changes and configure) and run the bind operation on the new changes. You can choose to run these steps separately, or you can run them all in one step, making ongoing maintenance easier. Follow these steps:

  1. Right-click the active version of the runtime group, and select Manage, as shown in Figure 7.
Figure 7. Common Repository Explorer
From Common Repository Explorer view, select Manage
  1. Click Run All Steps, as shown in Figure 8.
Figure 8. Running all steps to bind and upload changes
Running all steps to bind and upload changes
  1. Enter 2 as the new Runtime Group Version.
  2. Select CREDIT as the connection for bind.

Note that you can bind delta changes to new package versions as needed. Package versions can be created by providing the pkgVersion option configuration properties.

The new changes are now deployed, and the application is ready to run in production again.

Managing SQL and packages

Database schemas often change after applications are deployed into production. This triggers changes in SQL and can cause changes in the application consuming the SQL. Depending on the change in schema, the application might now access more than one schema, creating the need to arrange the SQL contained in the static SQL packages properly by schema, so that they can be bound using the right qualifier per package.

In Fix Pack 1, IBM introduced the capability to enable pureQuery annotated style (now called DAO) applications to access multiple schemas. In Fix Pack 3, you can better handle existing applications that access multiple schemas by automatically arranging packages in the captured file by schemas or tables. You can perform this from the command line or from Optim Development Studio tools.

You need to automatically keep the SQL file current so you can collect new SQL without running the application again and to remove old unused SQL from previously captured SQL, as part of regular maintenance.

This section describes how to take advantage of these package management capabilities.

Capture SQL without running the application

Optim Development Studio uses source code analysis to identify SQL statements that the application uses to show it in the SQL outline. This feature is available for JDBC, Hibernate, or JPA applications. You can use this feature to collect the new SQL to be merged into the existing capture file without running the application again in capture mode.

Follow these steps:

  1. Create the CUST_CRDT_CARD table in a new schema GOSALESRT by following these steps:
    1. Select table CUST_CRDT_CARD from the GOSALESCT schema.
    2. Right-click the selected table, and select Copy.
    3. Click GOSALESRT.
    4. Right-click, and select Paste.
    5. In the wizard, select the radio button for Paste directly into target, and click Next.
    6. Select the radio button for Copy database objects only.
    7. Click the Copy dependent database objects option, and click Finish. The CUST_CRDT_CARD table and its dependent tables are now created in the GOSALESRT schema.
  2. Change your application so the basicInsert method points to the table from the new schema (GOSALESRT instead of GOSALESCT), as shown in Listing 5.
Listing 5. Editing the basicInsert method
Line 166:
        VALUES(999, 999, '10000000009669483', 692)");
Line 171:
        VALUES(888, 888, '10000000009669483', 693)");
  1. Open the SQL outline, if not already open, by clicking Window > Show View > Data Management, and selecting SQL Outline.
  2. Expand GOSALESRT from the Database tab in the SQL outline. You can see the two new insert statements that now point to GOSALESRT schema instead of GOSALESCT.
  3. Export the new SQL statements into to an existing captured SQL file by selecting the SQL statements, right-clicking them, and selecting Export to SQL File, as shown in Figure 9.
Figure 9. SQL Outline Database tab
Export new SQL to a file from SQL Outline Database tab

Hint: You can also manually create an .sql file from your dynamic statement cache or using other preferred mechanisms. Once you have the .sql file, you can combine the SQL with the existing capture file, using the same mechanism described in the following steps.

  1. Merge the new SQL with the already captured SQL in the repository using the Optim Common Repositories perspective by following these steps:
    1. From the active version node, right-click and select Add SQL. Note that you can instead use the Add SQL to Active Version menu from the Runtime group node to perform the same function.
    2. Provide the .sql file you created in the earlier step, as shown in Figure 10.
Figure 10. Common Repository Explorer
Providing an SQL file to the active runtime group
    1. Click Finish.

Automatically group SQL in packages by schema or table

To ensure that multiple schemas are supported correctly, a new option on the configuration utility enables grouping SQLs by a string. By providing a schema or a table name in the string value, you can easily group all SQLs using a certain schema or table into their own packages. This new option is called groupSQLByStrings. For the example scenario, you use it to group by the GOSALESRT schema. Follow these steps:

  1. Click the Configure Properties tab.
  2. Add the properties as shown in Listing 6.
Listing 6. Adding properties
pdqxml= -rootPkgName Credit -groupSQLByStrings (GOSALESRT)

Automatically removing older unused SQL

To ensure that older SQL statements referring to an older schema eventually get deleted, you can use a new option in the configuration utility called removeInactiveForDays to remove SQL that is not executed for certain number of days. Having this option in a script that runs regularly can ensure automatic cleanup of the capture file to remove unused SQL. This section describes how to use the removeInactiveForDays option.

Follow these steps:

  1. Click the Configure Properties tab.
  2. Add the cleanconfigure, groupSQLByStrings, and removeInactiveForDays properties, as shown in Listing 7.
Listing 7. Adding properties to remove unused SQL
pdqxml= -rootPkgName Credit 
         -groupSQLByStrings (GOSALESRT)-cleanConfigure TRUE
         -removeInactiveForDays 60

The cleanConfigure option triggers a re-configure for the file. The inactive SQL is removed after 60 days.

  1. Reduce the number of days to see this feature in action sooner for testing purposes.

Review and perform the changes

A DBA does not always have visibility into the type of changes in the application and SQL, but having this information available can help you understand and manage the change. You can now use the change report feature to see the differences in the captured file with the new SQL compared to the file already in the repository. Once the changes are approved, you can upload the file to the repository. Follow these steps:

  1. From the Common Repository Explorer view, right-click Version 2, and select Manage.
  2. Expand Step 1, and click Update pureQueryXML, as shown in Figure 11.
Figure 11. Merging and configuring capture file with the new SQL.
Merging and configuring capture file with the new SQL.

This merges and then configures the file using the properties for re-arranging packages and removes older SQL.

  1. Click the pureQueryXML tab to see that the SQL got arranged in packages by schema, as shown in Figure 12.
Figure 12. New grouping of statements by schema
New grouping of statements by schema
  1. Click the Change Report feature to view the changes between the working copy of the capture file and the one existing in the repository, as shown in Figure 13.
Figure 13. Change Report
Change Report

Click to see larger image

Figure 13. Change Report

Change Report
  1. Expand Step 2, and click Bind pureQueryXML, as shown in Figure 14.
Figure 14. Bind the updated and configured file
Bind the updated and configured file
  1. To prepare the configuration properties for upload, remove the cleanConfigure option by clicking the Configure properties tab, and removing -cleanConfigure true.
  2. Expand Step 3, indicate a version number, and click Upload to Repository to upload the new runtime group to the repository, as shown in Figure 15. Note that you can click Delete the processed captured SQL if you want.
Figure 15. Uploading new runtime group
Uploading new runtime group

You have now finished the example scenario.


In this article, you learned how the new features in Fix Pack 3 of Optim Development Studio 2.2 and Optim pureQuery Runtime 2.2 can improve the ongoing maintenance of applications in production. The features provide the capability to store the SQL capture files (pureQueryXML) and properties in a database repository. The up-and-running experience is improved with the capability to update the pureQueryXML files without having to run applications in capture mode. You can keep the pureQueryXML files up-to-date. More automation is provided to arrange SQLs by packages. You can perform all the new features using command line tools or Optim Development Studio.

Appendix A. Platform support matrix

This table indicates which features are supported on which platforms.

Feature DB2 for z/OS DB2 for LUW DB2 for i Informix Oracle
Database to host the repository yes yes Coming soon Coming soon Coming soon
Client optimization features yes yes yes yes yes
Static SQL yes yes yes no no

Appendix B. Command line and GUI support by feature

This table indicates which features are supported by the GUI in Optim Development Studio and which features are supported by the command line interface.

Feature GUI support? Command line support?
Static binder yes yes
Client optimization: configure yes yes
Client optimization: merge yes yes
Repository: create repository no yes
Repository: bind on repository no yes
Repository: grant privileges no yes
Repository: all other function yes yes
Change report yes no
Collect SQL using static source code analysis yes no


Sample program for this articleUtil.java10KB
Sample program for this articleTutMod.java9KB



Get products and technologies



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

Zone=Information Management
ArticleTitle=What's new and cool in Optim Development Studio, Part 2: Exploring Optim Development Studio and pureQuery Runtime Version 2.2 Fix Pack 3