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. Use this feature to configure a DB2 data source in the appserver 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 appserver uses by employing one connection pool between resources that connect to the same data source. You must be using a DB2 data source, and the data source must be configured in the appserver 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 appserver, even though each application might have its own unique set of data source properties. There might be only one data source that is 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 appserver. 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 appserver 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 appserver to use one and only one physical connection.

 

1. Update the data source definitions for an application that is 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 apps > application_name > Resource references.

      • For applications that use container-managed persistence, click Applications > WebSphere enterprise apps > 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 to configure two resource reference files on the same data source. This allows you 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) that can be used to exploit the capabilities of the appserver. See Set two resource reference files on the same data source for more information.

        For pureQuery, 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.

      Click Extended properties... in the Target Resource JNDI Name column. If the data source does not support extended data source properties, you will receive an error when you try to apply these changes.

   3. Add a data source definition that is specific to that target resource. Click New, and complete the required fields. 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. Set two res-refs and the isolation level in the ibm-ejb-jar-ext.xml

2. 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. Verify the applications use the get/use/close connection pattern.

   2. Enable the appserver 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 > 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.

Extended data source properties
Set two resource reference files on the same data source

 

Related concepts

Data sources
JDBC providers

 

Related tasks

Use the DB2 Universal JDBC Driver to access DB2 for z/OS
Set a JDBC provider and data source

 

Related

data source properties