Extend DB2 data source definitions at the application level

Extend data source definitions, which consist of non-core or custom properties, for DB2 data sources to add a greater level of application flexibility when we are using the DB2 Universal JDBC driver or DB2 Using IBM JCC driver. This capability is sometimes referred to as heterogeneous pooling. Use this feature to configure a DB2 data source in the application server with a core set of data source properties, and defer to individual applications to define any custom or non-core properties, like currentSchema or clientApplicationInformation, to be application specific. We can also use these extended definitions to override any non-core or custom properties that are already defined for the data source. In addition, this feature can reduce the number of physical connections that the application server uses by employing one connection pool between resources that connect to the same data source.

We must be using a DB2 data source, and the data source must be configured in the application server with one of the following JDBC providers:

• DB2 Universal JDBC driver (Version 4.3.81 or higher)
• DB2 Using IBM JCC driver (Version 3.53.65 or higher)

Associate non-core properties with different resource references for a data source, and configure the applications to take advantage of these resource references to extend or override any non-core properties for the data source. We can choose to define new non-core or custom properties, or override any non-core properties that are already defined for the data source.

Also, applications can share the same connection pool in the application server, even though each application might have its own unique set of data source properties. There might be only one data source defined in the application server, and therefore only one connection pool, but to individual applications it would appear that there is more than one data source that is defined. This can result in:

• Reduction in memory consumption by the application server. Data source definitions will correspond to one connection pool, so there are fewer objects in memory when compared with a data source definition that corresponds to its own connection pool.
• Reduction in the number of open connections to the data source, which can reduce the memory consumption by the data source. The application server can reduce the number of idle connections by providing one connection pool that corresponds to multiple data sources, providing a more efficient use of connections.
• Avoidance of two-phase commit (XA) transactions in certain DB2 scenarios, when the applications use the get/use/close connection pattern. The application server can share connections between different resource references that have same set of core properties, even if they have different non-core properties, within the same transaction. This behavior can avoid two-phase commit processing if the connection sharing leads the application server to use one and only one physical connection.

Tasks

1. Update the data source definitions for an application already installed.

   1. Navigate to the panel to manage the resource references for the application.

      • For applications that do not use container-managed persistence, click Applications > WebSphere enterprise applications > application_name > Resource references.

      • For applications that use container-managed persistence, click...

                Applications > WebSphere enterprise applications > application_name > Provide default data source mapping for modules containing 2.x entity beans. We cannot add extended properties for individual CMP beans.

      • We can configure two resource referencefiles on the same data source. This allows us to extend the custom properties on the data source to include two different schema names (currentSQLId on z/OS or currentSchema name in the custom properties) used to exploit the capabilities of the application server. See the topic Configure two resource reference files on the same data source for more information.

        For IBM Optim™ PureQuery Runtime, if this is an XA data source define a new custom property on the data source where property_name = downgradeHoldCursorsUnderXa and boolean value = true.

   2. Navigate to the panel for extended data source properties. Select a resource from the table and click Extended properties.... If the data source does not support extended data source properties, we will receive an error when we try to apply these changes.

   3. Add one or more extended data source custom properties specific to that target resource. Click New, and complete the required fields. Repeat this step for each property for which we want to extend the data source. When finished, click OK to return to the previous screen. We cannot modify the following data source properties, which must be the same for all applications that use this data source:
      • accountingInterval
      • dataSourceName
      • databaseName
      • kerberosServerPrincipal
      • loginTimeout
      • logWriter
      • password
      • pkList
      • planName
      • portNumber
      • readOnly
      • securityMechanism
      • serverName
      • user

   4. Optional: Configure two res-refs and the isolation level in the ibm-ejb-jar-ext.xml

2. Create data source definitions for an application during installation.

   1. Navigate to the panel to map the resource references for the application. Select the install step Map resource references to resources and follow steps b and c from step 1 that describe how to update the data source definitions for an application already installed.

3. Optional: To avoid two-phase commit transactions, employ the get/use/close connection pattern. The get/use/close pattern is when an application gets a connection from a data source or connection factory and completes the current work within a single method, and the application does not call another method until that work is complete.

   1. Ensure that the applications use the get/use/close connection pattern.
   2. Enable the application server to verify use of the get/use/close connection pattern in applications, which will lead to the avoidance of two-phase commit processing if the sharing leads to one and only one physical connection being used.

      1. Click Resources > JDBC > Data sources > data_source > WAS data source properties

      2. Select Optimize for get/use/close connection pattern with heterogenous pooling. You will receive error messages if we select this option and the applications do not use the get/use/close connection pattern.

4. Optional: Heterogeneous pooling is enabled by default. Under certain circumstances it might be desirable to disable heterogeneous pooling for a data source. A data source custom property can be defined to deactivate this pooling support. The data source custom property is called, enableHeterogeneousPooling. If enableHeterogeneousPooling is not defined for a data source or if this property exists for a data source and has the value true, then heterogeneous pooling is enabled. If enableHeterogeneousPooling is defined for a data source and has the value false, then heterogeneous pooling is disabled. To define enableHeterogeneousPooling for a data source and disable heterogeneous pooling...

   1. Click Resources > JDBC provider > JDBC_provider

   2. Under additional properties, click Data sources.

   3. Click the name of the data source.

   4. Create the enableHeterogeneousPooling custom property.

      1. Click New.

      2. Enter enableHeterogeneousPooling for the name field.

      3. Enter false in the value field to specifies to disable heterogeneous pooling.

   5. Click Apply then Save.

Related:

Data sources

JDBC providers

Use the DB2 Universal JDBC Driver to access DB2 for z/OS

Configure two resource referencefiles on the same data source

Install enterprise application files with the console

Extended data source properties

WAS data source properties