Scenario: Setting custom data source properties in a WebSphere Application Server

You set pureQuery Runtime properties at the data source level. The pureQuery Runtime properties retrieved from repository are also set at the data source level. After you restart the application, pureQuery Runtime captures SQL statements issued by the application.

Before you begin

Ensure that the WebSphere® Application Server is running a dynamic application. The application is using a data source with the JNDI name jdbc/nobleDS available for the DB2® provider.

About this task

You create WebSphere JDBC-provider custom properties for the data source. The properties are used by pureQuery Runtime and the IBM® Data Server Driver for JDBC and SQLJ.

The custom property pdqProperties for pureQuery Runtime points to the repository that you created in the task, Scenario: Creating and configuring a repository for pureQuery Runtime. The property dataSourceName for the DB2 driver specifies the transaction database as the data source. The following table lists the WebSphere properties and their values:
Table 1. WebSphere custom properties
WebSphere custom data source property name Value
pdqProperties finalRepositoryProperties(ds://jdbc/nobleDS), propertiesGroupId(testApp), repositoryRequired(atStartup), propertiesRefreshInterval(2)
dataSourceName nobleDS

Procedure

To set custom data source properties in a WebSphere Application Server:

  1. Set the custom data source property pdqProperties on the data source.

    In the administrative console go to the Custom properties pane.

    JDBC providers > DB2 Universal JDBC Driver Provider > Data sources > DB2 Universal JDBC Driver DataSource > Custom properties.

    Create a property called pdqProperties.

    To specify custom property for the pureQuery Runtime properties, set the following values:
    pdqProperties=finalRepositoryProperties(ds://jdbc/nobleDS), propertiesGroupId(testApp),
      repositoryRequired(atStartup), propertiesRefreshInterval(2)
    The WebSphere custom property specifies the following pureQuery Runtime properties:
    finalRepositoryProperties
    The value specifies the location of the repository that stores the pureQuery Runtime properties that are used by the application. In the scenario, the value is the data source ds://jdbc/nobleDS. The repository database is the same is as the transaction database.
    propertiesGroupId
    The value specifies the runtime group ID that stores the pureQuery Runtime properties and pureQueryXML data for the application. The value of propertiesGroupId must be a defined runtime group ID in the specified repository in a database. In the scenario, the repository is in the database that is specified by the connection jdbc/nobleDS. The value of the propertiesGroupId is the ID of the run time group that you created in Scenario: Creating and configuring a repository for pureQuery Runtime.
    repositoryRequired
    The value specifies the pureQuery Runtime behavior if the repository is not available. For the scenario, the value is atStartup. The repository is required at startup of the application to read the pureQuery Runtime properties and pureQuerXML data from the repository. If the repository is not available or read errors are detected, an exception is thrown to the application. The following are the other allowed values for the repositoryRequired property:
    • The value forOutput specifies that an exception is thrown to the application if the repository that stores the captured SQL data is not available when pureQuery runtime attempts to write to the repository for the first time.
    • The value atStartupAndForOutput specifies that pureQuery Runtime behavior for both atStartup and forOutput are enabled.

    If the repositoryRequired property is not specified and the location specified by the finalRepositoryProperties option is not available, pureQuery Runtime executes SQL statements dynamically until the repository becomes available.

    Tip: If the application is required to run SQL statements statically or with various SQL substitutions, then you should specify the repositoryRequired property with the value atStartup or atStartupAndForOutput.
    propertiesRefreshInterval
    The value specifies the interval, in minutes, to wait between checking for updates to the pureQuery Runtime properties or the pureQueryXML data that are stored in the location that is specified by the finalRepositoryProperties property. pureQuery Runtime checks for updates to the properties only if the propertiesRefreshInterval property is one of retrieved properties, and the value of the property is a positive integer value.
    Note: You can change the refresh interval after the pureQuery Runtime first retrieves the properties. You can set the propertiesRefreshInterval property as a property used at application startup. You can also set it as a property that is retrieved from the location that is specified by the finalRepositoryProperties property.
  2. Set the WebSphere custom data source property dataSourceName.

    In the Custom Properties panel, create a custom DB2 Universal JDBC property dataSourceName. Set the value to an appropriate string that identifies the data source. For the scenario, the value is set to nobleDS.

  3. Restart the WebSphere Application Server.

    The application server restarts with the changes to the provider CLASSPATH and the data source that are specified by the custom properties.

Results

When the WebSphere JDBC provider custom property pdqProperties is recognized by WebSphere for the data source, all the connections created from this data source are enabled with pureQuery client optimization. The pureQuery Runtime property finalRepositoryProperties specifies the location of the repository where the pureQuery Runtime properties are stored. The pureQuery Runtime property propertiesGroupId indicates the runtime group in the repository.

Because the pureQuery properties are retrieved from a repository and the propertiesRefreshInterval property is retrieved from the repository, you can update the pureQuery configuration information, the pureQuery properties, and the pureQueryXML data without restarting the application server. The application will check for updates to the information and will retrieve and use updated information.

Tip: If more than one application shares the data source, you can override the pureQuery Runtime properties at the application level.

What to do next

In the next task, you will run the WebSphere application to execute SQL statements. pureQuery Runtime captures the SQL statements and associated SQL data and stores the data in the repository.


Feedback