Managing pureQuery-enabled applications efficiently, Part 3: Automate client optimization with WebSphere applications

pureQuery client optimization with SQL management repository

In a customer environment, applications often interact with transactional databases from within an application server. pureQuery client optimization can provide useful diagnostic information as well as increase performance for your web application. In this tutorial, you will learn how to automate the pureQuery client optimization process with Apache Ant script technologies.


Tony Sun, Staff Software Engineer, IBM China

Tony Sun is the primary QA engineer for the pureQuery runtime product.

19 May 2011

Before you start

Learn what to expect from this tutorial, and how to get the most out of it.

About this series

This is Part 3 of the "Managing pureQuery-enabled applications efficiently" series. Part 1 introduced the concept of using a repository with client optimization. Also, you learned how to use Ant scripts to facilitate the process of creating a repository. Now you will build upon these concepts so you can develop a fully automated client optimization system to help analyze and improve your applications.

Editor's note: Part 2 of this series is scheduled for publication in June 2011.

About this tutorial

The primary goal of this tutorial is to provide you with an Ant script framework that incorporates the pureQuery ManageRepository utility to automate maintaining a repository. The ManageRepository functions include creating a runtime group inside the repository, uploading and extracting repository artifacts, merging and configuring repository artifacts, and reporting repository information. These tasks will be performed to analyze a web application deployed inside WebSphere® Application Server.

Figure 1 illustrates the architecture of the framework.

Figure 1. Architecture framework showing automated process
WebSphere Application Server application connects to database and client optimization captures executed SQL; client application connects to the server and generates reports

Most importantly, once you have a good grasp of this framework, you can customize it to suit your needs. For instance, if you are experiencing performance issues, you can modify the framework to simulate heavy workloads or long execution time. Or if you want to provide a quick demo, you can modify it to showcase all of client optimization's functionality.

Before diving in too deep, you must have a good understanding of the previous tutorials. If you do not know how to create a repository with Ant scripts, then begin with Part 1.


The objective of this tutorial is to provide a skeleton automation system you can customize to suit your application analysis needs. You will learn to:

  • Deploy your application inside WebSphere Application Server.
  • Configure the environment and property files that will be used by the Ant scripts.
  • Customize your Ant XML file (repository.xml file) to suit the needs of your application.
  • Analyze reports regarding your application.


The following software components must be available:

  • WebSphere Application Server 6.0+
  • pureQuery Runtime 2.18+
  • JCC 3.57+
  • Apache Ant 1.7+
  • Database for Transactions and Repository
  • Eclipse 3.4+ or Optim Development Studio (ODS)

System requirements

You will need to have either ODS installed or Eclipse 3.4 installed on your machine. Also, you must have access to WebSphere Application Server 6.0 or higher, where you can deploy your application. WebSphere must be configured with a data source that connects to a transactional database. This will allow your application to execute against the database as client optimization captures the executed SQL. Also, Apache ANT must be on the classpath of your Eclipse and Optim Development Studio environment so that you execute Ant scripts. You should know this procedure from the previous tutorials.

Set up the framework environment

Before diving into the automation process, you need to correctly set up the framework environment. This involves configuring the WebSphere environment and creating the correct directory structures.

Configuring the WebSphere environment

Since your application will be deployed inside a WebSphere Application Server, the first step is to configure the WebSphere environment. If you have not worked with WebSphere before, these instructions should provide you with enough information to set up your application to run against a database server. The WebSphere instance may be on your own client machine, or it may be on a separate machine. In this example, the WebSphere instance is on the client machine. If it is on a separate machine, ask your WebSphere administrator to help you complete these steps.

Setting up a data source connection

For your web application to connect to the transactional database, it needs to use a JDBC driver. That JDBC driver must be put on the WebSphere classpath. Set up the JDBC driver:

  1. In the WebSphere panel on the left, click JDBC providers.
    Figure 2. Selecting JDBC providers
    JDBC providers listed in Resources on left panel
  2. Click on DB2® Universal JDBC Driver Provider.
    Figure 3. Selecting the DB2 Universal JDBC Driver Provider
    Shows properties for the JDBC
  3. This will bring up the classpath of the driver. Make sure your IBM JCC drivers and pureQuery drivers are on the classpath. In this case, there is a WebSphere variable defined as $DB2UNIVERSAL_JDBC_DRIVER_PATH, which points to a specific path. You can specify any path you want as long as they point to:
    • pdq.jar (pureQuery Jar)
    • pdqmgmt.jar (pureQuery License)
    • db2jcc.jar (IBM JCC Driver to connect to database)
    • db2jcc_license_ciuz.jar (IBM JCC Driver License)
    • pdq_properties.jar (a properties file that will be used by your application)
      Example: /home/users/lib/pdq.jar or C:/jars/pdq.jar. All these JAR files must exist in a directory that WebSphere can access. For more information on creating a directory for this, see the "Directory structure" section of this tutorial. If WebSphere is maintained on another machine, send the above files to the system administrator and have the administrator place those files on the classpath.

Next, you need to use this newly created JBDC Driver to create a data source.

  1. In the left WebSphere Panel, click Data sources under JDBC.
    Figure 4. Configuring the data source in WebSphere
    Left panel shows resources, including JDBC
  2. Click New to create a new data source.
  3. Enter the data source name you want. In this demo, it is called localhost, and the JNDI name is JDBC/localhost. Click Next.
    Figure 5. Configuration screen for new data source
    Screen where you enter basic data source information
  4. Choose the option Select an existing IBM JDBC Driver, then select the DB2 Universal JDBC Driver Provider that you created earlier and click Next.
    Figure 6. Specifying the JDBC driver
    Selecting an existing JDBC provider
  5. Enter the database connection information and click Next.
    Figure 7. Entering the database connection details
    Driver type, database name, server name, and port number
  6. At this point, you need to create a new security alias (a user ID and password) for this data source. Click on the Global J2C authentication alias link.
    Figure 8. Setting up the security alias
    Security alias screen
  7. Click the New button.
    Figure 9. Setting up a new user identity
    Screen for setting up a new user identity
  8. Provide a name for this alias, and also the user ID and password for the data source that will be used to connect to the database and click Apply.
    Figure 10. Entering the authentication data properties
    fields for entering alias, user ID, password, and optional
  9. Now you can select this authentication alias for your new data source.
    Figure 11. Setting up the security alias
    Select the authentication values for this resource
  10. Go to the next page and click Finish. The newly created data source should be in the list of data source. Select it and click Test connection.
    Figure 12. Testing the connection
    Screen lists various data sources with test connection button at top
  11. You should get a success message.
    Figure 13. Successful connection
    Indicates connection operation was successful

The application

Any WebSphere application that utilizes client optimization can take advantage of this automation process. For the purpose of this tutorial, we have provided a sample application. The file is the only application source and is provided to you in the Download section of the tutorial for demonstration purposes. It is packaged in the Test.war file (a web application archive) that will be deployed into WebSphere. As stated above, you are free to use your own web application archive.

The application runs for a specific period of time determined by the parameters set below.

Listing 1. code
int totalIterations= 10;
long timeForEachIteration= 60000;

The totalIterations variable determines how many times the application will run. The timeforEachIteration is in milliseconds, so the above setup runs the application for one hour.

Deploying the application

The following steps deploy the Test.war file that includes the file. If you are familiar with this process or have another application you want to deploy, you can skip this section.

  1. In the WebSphere navigation panel, expand the drop-down menu: Applications > Application Types > WebSphere Enterprise Applications.
    Figure 14. Enterprise applications in WebSphere
    Welcome screen for applications.
  2. Click Install on the following screen and browse to the folder where the Test.war application is located.
    Figure 15. Path to the new application
    Screen where you specify the path
  3. Click Next and choose Fast Path.
    Figure 16. Method for installing the application
    Choice between fast path and detailed
  4. Keep clicking Next until you reach the screen to map reference to resources. Specify the JNDI name for the data source that the application will connect to. The JNDI data source name points to the data source you created earlier: jdbc/localhost. Click Next.
    Figure 17. Map references to resources
    Each resource references that is defined in your application must be mapped to a resource
  5. Enter the Context Root for the application. The example uses /Test and click Next.
    Figure 18. Map context roots for Web modules
    Context root defined in the deployment descriptor can be edited
  6. Click Finish, then click Save.
    Figure 19. Summary screen of options to install
    Summary screen listing

Directory structure

You need to create a home directory where the all the necessary automation files and client optimization files will be stored. In this example, we use C:\repository.

Figure 20. Repository directory
Shows repository folder

The extract folder holds files extracted from the repository.

The upload folder contains pureQuery Runtime property files that will be used by the WebSphere application and also by the repository. It contains:

  • file — This file is uploaded to the repository and will be loaded by the application during automatic pull-down.
  • — This file is copied over and placed in the classpath of the WebSphere application server. It contains the pureQuery pdq.finalRepositoryProperties property that points to the repository. If WebSphere is on a remote machine, you will need to copy this file manually to the remote machine.
  • and — These files will override if you want to switch from dynamic capture to static execution.

The HTTP folder holds files returned by any HTTP requests by the application. The capture folder holds all the captured XML files produced from client optimization. The next directory holds all the WebSphere JAR files.

Figure 21. WebSphere JAR file directory
Image shows listing of JAR files

As mentioned in the WebSphere configuration section, the above-highlighted JAR files need to be included in the WebSphere classpath. Ensure that these files exist. The file pdq_properties.jar is just an archive file that contains The properties file is in a JAR file so WebSphere can pick up the properties. If WebSphere is on your local machine, the automation system will copy over and archive it as pdq_properties.jar. If WebSphere is on a remote machine, you will manually need to issue jar -cvf pdq_properties.jar and place the machine on the remote WebSphere classpath. Ask your WebSphere administrator to do this on the machines that the administrator maintains.

Automation execution

Executing the automation process requires configuring the file and running the Apache Ant script.


In the previous tutorials of this series, you were introduced to the .properties file read by Ant scripts. This file is included in the downloadable ZIP source and is no different from the previous .properties files. However, this file has many properties because our automation process deals with almost all of the remaining pureQuery client optimization tasks. It contains the following properties:

  • jdk.home=C:/eclipse_cmvc/ibm_jdk15
    • This is your JDK home directory.
  • websphere.dir=C\:/Program Files/IBM/WebSphere
    • This is the WebSphere home directory (only if WebSphere is on client machine).
  • websphere.datasource.dir=C\:/DB2_JCC_DRIVER_FOR_WAS
    • This is where the pdq.jar and pdqmgmt.jar, db2jcc.jar files, and files will be placed so WebSphere can access the files with the classpath. These files are required for client optimization (only if WebSphere is on client machine).
  • websphere.webinf.dir=C\:/Program Files/IBM/WebSphere/AppServer/profiles/AppSrv01/installedApps/IBM-L3D7044Node02Cell/Test_war.ear/Test.war/WEB-INF
    • This is the directory where the application (WAR file) is installed. If you want to modify your application, you can modify the application without reinstalling the application through the admin console. You can stop the web server, copy over the .jar files or .class files, and restart the server (only if WebSphere is on client machine).
  • websphere.hostport=localhost:9081
    • This is the WebSphere host URL and port.

The properties below are used by the ManageRepository utility to connect to the repository.

  • repository.url=jdbc:db2://localhost:50000/TONYDB
    • This URL points the client optimization external database repository.
  • repository.user=tonysun
    • This is the user name to connect to the repository.
  • repository.password=******
    • This is the password to connect to the repository.
    • This loads the IBM JCC driver to be used by the application.
  • repository.type=pureQueryOnly
    • This sets up the repository type for the application.
  • repository.uploadFolder=c:/repository/upload
  • repository.extractFolder=c:/repository/extract
  • repository.captureFolder=c:/repository/capture
  • repository.http.request=c:/repository/http
    • These folders specify the home directory and folders where the upload, extraction, captured, and HTTP files will reside.

The following properties specify the runtimeGroup variables

    • This variable specifies the runtime group created for the application that uses the repository.
  • runtimegroup.version=v1
    • The above two variables specifies the version and contact for the repository.
    • This property specifies the base name of capture files created by client optimization during incremental capture. Hence, capture files will be named as IncrementalCapture_XXX.
  • extract.iterations=1,2,3,4,5,6,7,8,9,10
    • The above list specifies the number of executions for the extract, merge, configure, bind, and update process. At this time, due to the limitations of Apache Ant, you need to specify a list and not a number, such as N.
  • extract.hour=0
  • extract.minutes=2
    • The above time specifies the intervals between each extraction process.
  • application.url=http://localhost:9081/Test/TestSPServlet
    • This URL will launch the application. It will be loaded by the build.xml to launch the application within our automated Ant process.

Running Ant

Once the file has been configured, all you have to do is launch the build.xml script. Setting up Apache Ant and running an Ant script was shown in Part 1. The downloadable archive includes the repository.xml and build.xml files. You will run the build.xml, which calls the repository.xml.

During execution, the web application will run while the extract, merge, configure, bind, and update client optimization tasks occur simultaneously at regular intervals. Details about this process will be discussed in the next section.

Launching the build.xml can be done from the command line or inside an Optim Development Studio- or Eclipse-based environment (this was also shown in previous sections).

Figure 22. Running Ant
Pop-up menu shows choosing Run as -- Ant Build

Customizing the automation process

The automated process you have learned to run is a default set of Ant script tasks. As mentioned, the benefit of this framework is its versatility. To customize these scripts for your needs, you need to understand what the default set of tasks are doing. The following tasks are all the tasks that can be configured and reside within the Ant script repository.xml.

Repository setup tasks

These tasks are similar to the setup repository tasks you learned in Part 1. However, they progress a few steps further by creating a runtime group, uploading the necessary runtime group artifacts, and activating the runtime group.

Listing 2. Set up repository
<target name="setup_repository">
	<echo message="Setup the repository." />
	<antcall target="delete_repository"/
	<antcall target="create_repository"/>
	<antcall target="create_runtimegroup"/>
	<copy file="${repository.uploadFolder}/" 
	<antcall target="update_runtimegroup_setup"/>
	<antcall target="activate_runtimegroup"/>

As you can see, the primary tasks comprise smaller subtasks. To customize this task, you can add or remove any of the subtasks within the <target> and </target> headers. For instance, suppose you have already created your repository and need to create only a runtime group. You can remove the delete_repository and create_repository tasks. Your new Setup_Repository task becomes:

Listing 3. Set up creating runtime group only
<target name="setup_repository">
	<echo message="Setup only runtimegroup." />
	<antcall target="create_runtimegroup"/>
	<copy file="${repository.uploadFolder}/" 
	<antcall target="update_runtimegroup_setup"/>
	<antcall target="activate_runtimegroup"/>

Launching application and client optimization

This task launches the application deployed in WebSphere while simultaneously starting the client optimization process for the application.

Listing 4. Run application
<target name="runapp">
	<echo message="Run parallel application while capturing,merging,configure,bind" />
	<get src="${application.url}" dest="${repository.http.request}/save.html}"/>
	<antcall target="emcb"/>

It is best not to modify this task. It simply starts your application and begins the client optimization tasks.

Core client optimization tasks

Following are the core client optimization tasks such as the Extract, Merge, Configure, and Bind tasks. These tasks are placed within a loop structure defined by the ${extract.iterations}. The number of iterations is determined by the number specified in You can change the length of the automated process by changing the value in the file.

Listing 5. Core tasks
<target name="emcb">
	<echo message="Extract,Merge,Configure,Bind Attempts" />
		<for list="${extract.iterations}" param="attempt">
			<sleep minutes="${sleep.minutes}"/>
			<echo>Extract,Merge,Configure,Bind Attempt#
			<antcall target="deactivate_runtimegroup"/>
			<antcall target="extract_runtimegroup"/>
			<copy todir="${repository.captureFolder}/A
			<fileset dir="${repository.extractFolder}"/>
			<antcall target="merge_incremental"/>
			<antcall target="configure_incremental"/>
			<antcall target="update_runtimegroup"/>
			<antcall target="report_repository"/>
			<antcall target="report_diff"/>
			<antcall target="bind"/>
			<antcall target="delete_incremental"/>
			<antcall target="activate_runtimegroup"/>

Any of the above tasks can be configured. For example, if you want to change the pureQuery runtime property to switch from executing in dynamic mode with capture on to static mode, you would use the task copy todir in the previous Core Tasks example, and copy a file to the ${repository.upload} folder. Then you call <antcall target="update_runtimegroup"/> to update the runtime group with the new property. You should familiarize yourself with the client optimization tasks as well as Apache Ant before trying to customize the tasks. Changes to the Ant script require familiarity with the client optimization process. For example, the configure_incremental task usually goes after merge_incremental task. Also, you always need to run the deactivate_runtimegroup task before running the extract_runtimegroup task. These are just some of the conventions that must be followed. If you are still learning about client optimization, you should try simple changes before making complex changes.

Analysis and summary

Congratulations! You have successfully executed a client optimization automated framework with WebSphere. This also means that you have some reports files that have been generated regarding your application. The reports will reside in the home directory you specified under the capture folder.

Figure 23. Home directory with reports
Shows two reports in the directory

These files contain information regarding the SQL statements your application executed, as well as information regarding your repository. You can examine these files to diagnose the current state of the application.

Included in the downloads section are a sample of the report files

Future possibilities

This tutorial was meant to teach you to configure an automated process with one application. However, the framework can be scaled to suit many applications. That scenario is a more complex one and might be added in the future.


Sample application for this articleSourceAndApp.zip9KB



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, WebSphere, Open source
ArticleTitle=Managing pureQuery-enabled applications efficiently, Part 3: Automate client optimization with WebSphere applications