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.
The example scenario describes how to do the following:
- Set up the sample application by capturing the initial SQL from the sample application and bind packages
- Set up and populate the repository with the captured SQL and associated runtime properties
- 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.
- Install Optim Development Studio 2.2 Fix Pack 3 (see Resources for a link to the download document for this fix pack).
- 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.
- Download the JDBC application code: TutMod.java and Utils.java (see Downloads).
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.
To create the capture file, follow these steps:
- From the Data Source Explorer, create a connection called CREDIT to the GSDB database.
- Create a Java project called creditCardApp. This holds your credit card application.
- Move the provided source code (TutMod.java, Utils.java) to the src folder of your creditCardApp.
- Right-click the creditCardApp project, and select Add pureQuery support.
- Follow the steps in the wizard to select the connection CREDIT and to provide the default schema value of GOSALESCT.
- Ensure the checkbox for Enable SQL capturing and binding for JDBC applications is checked.
- Enable literal replacement by following these steps:
- Navigate to the src folder in the creditCardApp project, and open the pdq.properties file.
- Add the line
pdq.sqlLiteralSubstitution=ENABLEas shown in Figure 1.
Figure 1. Enabling SQL literal substitution in pdq.properties
Note that, by default, the capture mode is set to ON with
- To capture the SQL, follow these steps.
- Right-click TutMod.java, and select Run As > Java Application.
- Pass in the following parameters when you run the application:
<database> <host> <port> <username> <password>
- 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.
- Prepare the application to run using static SQL. To create the
packages, bind the application by following these steps.
- From the pureQueryFolder, open the Default.bindprops file and
-bindOptions "QUALIFIER (GOSALESCT)"for the defaultOptions, as shown in Figure 2.
- From the pureQueryFolder, open the Default.bindprops file and enter
Figure 2. Providing bind options in Default.bindProps
- Right-click the capture.pdqxml file, and select pureQuery > Bind.
- Select the CREDIT connection.
- 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.
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
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
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
manageRepository -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.
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
To upload the captured file and properties to the repository, follow these steps
- Remove the line
pdq.pureQueryXmlfrom 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
pdq.captureMode=ON pdq.executionMode=STATIC pdq.sqlLiteralSubstitution=ENABLE
- Create a connection to point to the database that contains the
repository by following these steps:
- Click Window > Open Perspective to open the Optim Common Repositories perspective.
- Go to the Common Repository Explorer, and right-click Common Repository Connections.
- Click New.
- 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
- To create a runtime group called CREDIT_RG, follow these steps:
- Right-click the SQL Management node, and select New Runtime Group.
- Enter the runtime group name and version.
- Select the properties files from your project for upload, as shown in Figure 5.
Figure 5. Steps to create a runtime group
- Click Finish.
- 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
When the application is executed, pureQuery runtime uses this captured file (pureQueryXml) and its associated properties from the SQL management repository.
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.
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:
- Uncomment the basicUpdate and basicDelete methods in TutMod.java.
- 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>; pdq.properties=CREDIT_GRP pdq.repositoryRequired=AtStartupAndForOutput
finalRepositoryPropertiesspecifies the location to use when retrieving pureQuery runtime properties and pureQueryXML file information.
propertiesGroupIdspecifies the runtime group ID in an SQL management repository to use when retrieving data or storing data to an SQL management repository.
repositoryRequiredspecifies when the SQL management repository is required.
- Run the application again. The capture file with the new SQL is saved in the SQL management repository.
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:
- Right-click the active version of the runtime group, and select Manage, as shown in Figure 7.
Figure 7. Common Repository Explorer
- Click Run All Steps, as shown in Figure 8.
Figure 8. Running all steps to bind and upload changes
- Enter 2 as the new Runtime Group Version.
- 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.
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.
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:
- Create the CUST_CRDT_CARD table in a new schema GOSALESRT by following
- Select table CUST_CRDT_CARD from the GOSALESCT schema.
- Right-click the selected table, and select Copy.
- Click GOSALESRT.
- Right-click, and select Paste.
- In the wizard, select the radio button for Paste directly into target, and click Next.
- Select the radio button for Copy database objects only.
- 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.
- 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: stmt.executeUpdate("INSERT INTO GOSALESRT.CUST_CRDT_CARD(CUST_CC_ID, CUST_CODE, CUST_CC_NUMBER, CUST_CC_SERV_CODE) "+ " VALUES(999, 999, '10000000009669483', 692)"); Line 171: stmt.executeUpdate("INSERT INTO GOSALESRT.CUST_CRDT_CARD(CUST_CC_ID, CUST_CODE, CUST_CC_NUMBER, CUST_CC_SERV_CODE) "+ " VALUES(888, 888, '10000000009669483', 693)");
- Open the SQL outline, if not already open, by clicking Window > Show View > Data Management, and selecting SQL Outline.
- 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.
- 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
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.
- Merge the new SQL with the already captured SQL in the repository
using the Optim Common Repositories perspective by following these
- 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.
- Provide the .sql file you created in the earlier step, as shown in Figure 10.
Figure 10. Common Repository Explorer
- Click Finish.
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:
- Click the Configure Properties tab.
- Add the properties as shown in Listing 6.
Listing 6. Adding properties
<yourProjectLocation>\CreditCardApp\pureQueryFolder\capture. pdqxml= -rootPkgName Credit -groupSQLByStrings (GOSALESRT)
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:
- Click the Configure Properties tab.
- Add the cleanconfigure, groupSQLByStrings, and removeInactiveForDays properties, as shown in Listing 7.
Listing 7. Adding properties to remove unused SQL
<yourProjectLocation>\CreditCardApp\pureQueryFolder\capture. 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.
- Reduce the number of days to see this feature in action sooner for testing purposes.
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:
- From the Common Repository Explorer view, right-click Version 2, and select Manage.
- Expand Step 1, and click Update pureQueryXML, as shown in Figure 11.
Figure 11. 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.
- 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
- 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
- Expand Step 2, and click Bind pureQueryXML, as shown in Figure 14.
Figure 14. Bind the updated and configured file
- To prepare the configuration properties for upload, remove the cleanConfigure option by clicking the Configure properties tab, and removing -cleanConfigure true.
- 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
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.
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
|yes||yes||Coming soon||Coming soon||Coming soon|
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?|
|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|
|Collect SQL using static source code analysis||yes||no|
|Sample program for this article||Util.java||10KB||HTTP|
|Sample program for this article||TutMod.java||9KB||HTTP|
- Read the first article in this series, "What's new and cool in Optim Development Studio 2.2, Part 1."
- Read the IBM Redbooks publication Using
Integrated Data Management to meet Service Level Objectives to
find out more about using client optimization, SQL literal substitution,
- Read the Integrated Data Management
Information Center topic on client optimization.
- See the pureQuery platform web page for an overview of the pureQuery
platform, including an FAQ.
- Go to the Optim
Development Studio and pureQuery Runtime Architecture page on
developerWorks for the resources you need to advance your Optim
Development Studio skills.
- Learn more about Information Management at
Information Management zone. Find technical documentation, how-to
articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on
Get products and technologies
- Download Optim
Development Studio Fix Pack 3.
- Download the GSDB sample database.
- Download the Optim Development Studio and pureQuery 30-day trial.
- Build your next
development project with IBM trial
software, available for download directly from
- Participate in the discussion forum.
- Read the Integrated Data Management blog
on key enhancements to Fix Pack 3.
- Register for the virtual tech briefing (or replay) on client optimization
- Check out the developerWorks
blogs and get involved in the developerWorks
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.
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 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.