Capturing SQL statements from Web applications that share a data source

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

About this task

When you set application-specific properties, modifying the properties and capturing SQL statements for one application do not disrupt the other applications that share a data source. You can also perform 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 properties that are specific to an application, you do not have 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. See How to set properties for capturing and running restricted sets of SQL statements dynamically 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 both to run SQL statements in pureQueryXML files dynamically.

Application1 uses 2 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.

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.pureQueryXml=App1Items.pdqxml

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

    pdq.captureMode=ON
    pdq.pureQueryXml=App1Orders.pdqxml
    The names of the pureQueryXML files can follow any convention you like, as long as their extension is either .pdqxml or .xml.
  4. Capture SQL statements.
  5. In both properties files, set captureMode to OFF.
  6. If you are using the application server's statement cache, purge the connection pools for both data sources.
  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.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. In the pdq.OrdersDS.properties file, set captureMode to OFF.
  5. If you are using the application server's statement cache, purge the connection pool for the data source.
  6. Restart Application2.

Feedback