Configure the JDBC agent

This task describes how to configure the JDBC agent. The JDBC agent is used to virtualize both J2EE and J2SE applications that are using JDBC™ API to access persistent storage.

Service Virtualization provides these two components:

  • JDBC agent. You configure the JDBC agent in Service Virtualization. The agent listens for requests from the JDBC driver.

  • JDBC driver. You install the JDBC driver in your application under test. The driver communicates remotely with the JDBC agent in Service Virtualization. It intercepts each JDBC API call and depending on the current service mode, forwards the traffic accordingly. If the virtual service is in either Standby or Learning mode, the driver forwards the traffic to the actual JDBC driver. If the virtual service is in Simulating mode, the virtual service simulates the API response.

    The JDBC driver is uniquely generated according to the configuration of the JDBC agent and agent host that you define in Service Virtualization. The driver is invoked only if either a virtualization JDBC connection string is used (J2SE), or if the virtual data source is explicitly created and used by the application (J2EE). If a non-virtualization connection string or data source is used, the virtualization JDBC driver is inactive and has no impact on the application under test.

To configure the JDBC agent:

  1. Prerequisites:

    The following types of integration scenarios are supported:

    • A J2SE application using JDBC 3.0 or 4.0/4.1 API.

    • A Java™ based application deployed on a J2EE application server (J2EE version 1.4 or later).

  2. Configure the agent settings:

    From the main Service Virtualization menu, select Tools > Options. On the Agents Page, select JDBC Agent. For user interface details, see the JDBC agent settings.

  3. Reconfigure a standard J2SE or J2EE application:

    To reconfigure a typical Java Standard Edition (J2SE) application for JDBC API virtualization:

    1. Make sure you have configured the agent settings correctly.
    2. Download the JDBC driver. On the Agents Page, click Create JDBC driver for this configuration.

    3. Deploy the driver in the application class path.

    4. In some cases, you may need to override JDBC default type mapping. To override default JDBC type mapping, add the typeMapping property to your connection string. For example: jdbc:hpsv:db2://svdb2.hpswlabs.adapps.hp.com:50000/DEV:typeMapping=META-INF/myTypeMappingProperties

      To learn more, see JDBC type to Java type mapping.

    5. Create an SQL over JDBC virtual service, and use the actual JDBC connection string as the Virtual Service Real Connection String.

    6. Reconfigure the application under test to use virtual endpoint. If you are working with JDBC version 3.0, you must also update the JDBC driver class name to the virtualization driver class name. The virtual driver class name is com.hp.sv.jdbc.agent.jdbc3.VirtualDriver.

    To reconfigure a typical Java Enterprise Edition (J2EE) application using JDBC data sources, for JDBC API virtualization:

    1. Make sure you have configured the agent settings correctly.
    2. Download the JDBC driver. On the Agents Page, click Create JDBC driver for this configuration.

    3. Create an additional J2EE data source using the newly created virtual JDBC driver that you downloaded from the Service Virtualization Agents page. See the J2EE application server documentation for more details.

    4. Reconfigure the application under test to use the newly created data source.
    5. Create an SQL over JDBC virtual service, and use the name of the newly created data source as the Real Connection String.

  4. Configure your application server:

    1. Place the JDBC driver that you downloaded from the Service Virtualization Agents page in WL_HOME\server\lib.
    2. Modify WEBLOGIC_CLASSPATH in WL_HOME\common\bin\commEnv.cmd (or commEnv.sh), as described in the WebLogic documentation.

    3. In the WebLogic Server Administration Console, create a new data source. Provide the JNDI name of the real data source, using the RealDataSourceName property.

      Alternatively, you can set up the JNDI virtual driver to use a direct connection to the database, by specifying the realUrl property. For example: realUrl=jdbc:oracle:thin:@database.host.com:1521:orcl.

    1. In some cases, you may need to override JDBC default type mapping. To override default JDBC type mapping, add the typeMapping property with the location of the mapping file to your data source.

      To learn more, see JDBC type to Java type mapping.

    2. Restart the WebLogic server.

    1. In the WebSphere administrative console, in Resources > JDBC > JDBC Providers, add a new virtual JDBC provider.

      database type: user-defined

      implementation class name: com.hp.sv.jdbc.agent.jdbc4.VirtualDataSource

      classpath: enter the path to the downloaded virtual JDBC driver

    2. Create a new data source using the newly created JDBC Provider and enter custom properties.

      Provide the JNDI name of the real data source, using the realDataSourceName property.

      Alternatively, you can set up the JNDI virtual driver to use a direct connection to the database, by specifying the realUrl property. For example: realUrl=jdbc:oracle:thin:@database.host.com:1521:orcl.

      You do not need to define properties for username and password if you use the JAAS - J2C authentication data alias in the security settings for the virtual data source.

    3. In some cases, you may need to override JDBC default type mapping. To override default JDBC type mapping, add the typeMapping property with the location of the mapping file to your data source.

      To learn more, see JDBC type to Java type mapping.

    4. Run the WebSphere test connection operation on the virtual data source.

    5. Disable the reuse of prepared statements to prevent a closed statement exception, such as, DSRA9110E: Statement is closed.

      1. In the WebSphere administrative console, in Data sources > %DataSourceName% > WebSphere Application Server data source properties, set Statement cache size to 0.

      2. Restart WebSphere.

    6. If you are working with a non-XA datasource, continue with Virtualizing a Non-XA datasource.

    Virtualizing a Non-XA datasource

    You can virtualize a non-XA datasource by explicitly enabling it in WebSphere.

    J2EE supports XA and non-XA datasources.

    • An XA datasource supports a two-phase commit of multiple resources (or databases) that are involved in a single transaction. A transaction manager coordinates the transaction activity.

      A virtualized data source uses a real data source to learn or forward JDBC calls. Since an XA datasource can share its transactions with a real data source, an XA datasource can be virtualized without explicit enablement.

    • A non-XA datasource supports a single transaction with one resource (or database). The transaction cannot be shared with any other transaction resource. Therefore, if you define VirtualDataSource using a real non-XA datasource, application, WebSphere throws an exception, such as:

      WTRN0063E An illegal attempt to commit a one phase capable resource with existing two phase capable resources has occurred

      You can enable virtualization of a non-XA datasource, as described below.

    To enable virtualization of a non-XA datasource:

    1. In the WebSphere administrative console, in Enterprise Applications > %YourApp%, under Detail Properties, select Last participant support extension.

    2. In the Configuration tab, select the Accept heuristic hazard check box.

    3. Restart WebSphere.

    You can now use VirtualDataSource (which is an XA resource) together with your original non-XA datasource.

    1. In the WebSphere administrative console, in Resources > JDBC > JDBC Providers, add a new virtual JDBC provider.

      database type: user-defined

      implementation class name: com.hp.sv.jdbc.agent.jdbc3.VirtualDataSource

      classpath: enter the path to the downloaded virtual JDBC driver

    2. Create a new virtual data source using the newly created JDBC provider.

    3. In some cases, you may need to override JDBC default type mapping. To override default JDBC type mapping, add the typeMapping property with the location of the mapping file to your data source.

      To learn more, see JDBC type to Java type mapping.

    4. Disable statement caching. In the WebSphere Application Server data source properties, set Statement Cache size to 0.

    5. Define custom properties for the virtual data source:

      Provide the JNDI name of the real data source, using the realDataSourceName property.

      Alternatively, you can set up the JNDI virtual driver to use a direct connection to the database, by specifying the realUrl property. For example: realUrl=jdbc:oracle:thin:@database.host.com:1521:orcl.

      You do not need to define properties for username and password if you use the JAAS - J2C authentication data alias in the security settings for the virtual data source.

    6. Run the WebSphere test connection operation on the virtual data source.

    In the Other Properties field of the JDBC Adapter, use the realUrl property in place of realDataSourceName, and enter the connection string to the real database. Use a semicolon (;) to specify multiple properties, such as serverHost and serverPort. The Other Properties field cannot contain spaces.

    realUrl=jdbc:oracle:thin:@database.host.com:1521:svdb2QA;serverHost=virtualization.com;serverPort=12345

    Note: In some cases, you may need to override JDBC default type mapping. To override default JDBC type mapping, add the typeMapping property with the location of the mapping file to your data source. To learn more, see JDBC type to Java type mapping.

    1. Generate a new JDBC driver, that includes the JDBC driver class name, the deployment type, J2SE, and the JDBC version, 4.x.
    2. Add the virtual JDBC driver jar to SAP PI.
    3. In the Integration Builder, open the communication channel that you want to point to a virtual service.
    4. Replace Connection with the virtual connection string, found in the virtual service settings. Append the following string to the end of the virtual connection string ?realDriverClassNames=%REAL_DRIVER_CLASSNAME% where %REAL_DRIVER_CLASSNAME% is the value of the JDBC driver field.
    5. Replace JDBC Driver with com.hp.sv.jdbc.agent.jdbc4.VirtualDriver.
    6. Apply and activate the changes.