Capturing SQL statements from Web applications that share a data source

If multiple Web applications share a data source, you can set client optimization properties that are specific to each application.

About this task

When you set application-specific properties, modifying the properties, capturing SQL statements, configuring pureQueryXML files, or binding SQL for one application do not disrupt the other applications that share a data source. You can also perform any of these actions on two or more of these applications at the same time without the actions on one application interfering with the actions on another, unless one or more of the applications depends on statement caching performed by the application server.

In fact, if you modify the client optimization properties that are specific to an application, you do not need to restart the application server that is running all of the applications. Instead, you can restart just the one application.

However, if you modify any properties that are in a pdq.properties file or that are set on a DataSource object, you must restart the application server. Properties that you might set in these locations are those that determine how pureQuery logs error messages for client optimization. See How to set properties for client optimization for information about setting properties in these locations.

Procedure

Example

Suppose that two Web applications – Application1 and Application2 – are running under WebSphere® Application Server. You want to configure client optimization for both, so that you can run SQL statements in Application1 dynamically and run SQL in Application2 statically.

Application1 uses two data sources: OrdersDS and ItemsDS. Application2 uses one of these data sources, too: OrdersDS.

The steps that follow show how to set up a simple configuration without logging and without any optional client optimization properties.

To set up and run Application1, you might follow these steps:

  1. In WebSphere Application Server, set the custom IBM® Data Server Driver for JDBC and SQLJ property dataSource for each of the two data sources.
  2. Create one properties file for each data source: pdq.ItemsDS.properties and pdq.OrdersDS.properties.
  3. Set the required properties in each of the properties files.

    For pdq.ItemsDS.properties, you use these settings:

    pdq.captureMode=ON
    pdq.executionMode=DYNAMIC
    pdq.pureQueryXml=App1Items.pdqxml

    For pdq.OrdersDS.properties, you use these settings:

    pdq.captureMode=ON
    pdq.executionMode=DYNAMIC
    pdq.pureQueryXml=App1Orders.pdqxml
    The names of the pureQueryXML files can follow any convention that you want, provided that their extensions are either .pdqxml or .xml.
  4. Capture SQL statements.
  5. In both properties files, set captureMode to OFF and executionMode to DYNAMIC.
  6. If you are using the application server's statement cache, purge the connection pools for both data sources. Whenever you are using this cache and you change the value of executionMode, you must purge the connection pool.
  7. Restart Application1.

To set up and run Application2, you might follow these steps:

  1. Create the properties file pdq.OrdersDS.properties.
  2. Set the required properties in each of the properties files.
    pdq.captureMode=ON
    pdq.executionMode=DYNAMIC
    pdq.pureQueryXml=App2Orders.pdqxml
    Again, the name of the pureQueryXML file can follow your own convention. The extension, however, must be .pdqxml or .xml.
  3. Capture SQL statements.
  4. Run the Configure utility on the pureQueryXML file to set up DB2® packages that you will create.
  5. Run the StaticBinder utility on the pureQueryXML file to create DB2 packages from the captured SQL statements.
  6. In the pdq.OrdersDS.properties file, set captureMode to OFF and executionMode to STATIC.
  7. If you are using the application server's statement cache, purge the connection pool for the data source. Whenever you are using this cache and you change the value of executionMode, you must purge the connection pool.
  8. Restart Application2.

Feedback