(Dist) Data source minimum required settings for DB2 with the application server on AIX, HP-UX, Linux, Solaris, or Windows

These properties vary according to the database vendor requirements for JDBC driver implementations. We must set the appropriate properties on every data source that we configure. These settings are for a DB2 data source that is connecting to an application server running on AIX, HP-UX, Linux, Solaris, or Windows systems.

The SafepointPolling technology in the HP Java virtual machine (JVM) for HP-UX might interfere with DB2 transactions. Developed to ensure safepoints for Java threads, SafepointPolling technology generates a signal that can interfere with the signal between the application server and a DB2 database. Database deadlocks often result. Prevent the interference by starting the JVM with the -XX:-SafepointPolling option, which disables SafepointPolling during runtime.

Starting with version 9 of DB2, Linux, HP-UX, and Solaris systems require a 64-bit kernel. In addition, the width of the default instance bit is now determined by the operating system, which is different than with previous versions of DB2. With these changes in operating system requirements, the -w option, which would have provided the ability to specify the bit width of the instance, was removed for the db2icrt, db2ilist and db2iupdt commands.

We can configure one of the following types of providers:

• DB2 Using IBM JCC Driver
• DB2 Using IBM JCC Driver (XA)
• DB2 Universal JDBC Driver provider
• DB2 Universal JDBC Driver provider (XA)

The LIBPATH for a 32-bit application is:

• $INSTHOME/sqllib/lib
• $INSTHOME/sqllib/lib32

The LIBPATH for a 64-bit application is:

• $INSTHOME/sqllib/lib2
• $INSTHOME/sqllib/lib64

• DB2 Using IBM JCC Driver

  This provider is a one-phase commit JCC provider for DB2 that uses the IBM Data Server Driver for JDBC and SQLJ. The IBM Data Server Driver for JDBC and SQLJ is the next generation of the DB2 Universal JDBC driver. Data sources we create under this provider support only 1-phase commit processing, unless we use the type 2 JDBC driver with the application server for z/OS . If we run the application server on z/OS with the type 2 driver, the driver uses RRS and supports 2-phase commit processing.

  This provider:

  • Is configurable only in version 7.0 and later nodes
  • Supports the following one-phase data source:

    com.ibm.db2.jcc.DB2ConnectionPoolDataSource
    

  • Requires the following JDBC driver files:
    • db2jcc4.jar - This is the IBM Data Server Driver for JDBC and SQLJ .jar file. Set the DB2_JCC_DRIVER_PATH path variable to point to the db2jcc4.jar file. Once we install DB2, we can find this .jar file in the DB2 java directory. For Type 4 JDBC driver support from a client machine where DB2 is not installed, copy this file to the local machine. If we install any fixes or upgrades to DB2, we must update this file as well.
    • db2jcc_license_cu.jar - This is the IBM Data Server Driver for JDBC and SQLJ license file that allows access to the DB2 database. Use this .jar file or the next one to gain access to the database. This .jar file ships with the application server in a directory that is defined by the UNIVERSAL_JDBC_DRIVER_PATH environment variable.
    • db2jcc_license_cisuz.jar - This is the IBM Data Server for JDBC and SQLJ license file that allows access to the following databases:
      • DB2 Database for Linux, UNIX and Windows
      • DB2 for iSeries
      • DB2 for z/OS
      • DB2 for VM and VSE

      db2jcc_license_cisuz.jar does not ship with the application server. Place this file in the same directory as the db2jcc4.jar file, so that DB2_JCC_DRIVER_PATH points to both files.

    • The native files required by the IBM Data Server Driver for JDBC and SQLJ in the application server. These are only needed when driverType is set to 2.

  • Uses the following class paths:

    ${DB2_JCC_DRIVER_PATH}/db2jcc4.jar
    ${UNIVERSAL_JDBC_DRIVER_PATH}/db2jcc_license_cu.jar
    ${DB2_JCC_DRIVER_PATH}/db2jcc_license_cisuz.jar
    ${PUREQUERY_PATH}/pdq.jar
    ${PUREQUERY_PATH}/pdqmgmt.jar
    

    ${PUREQUERY_PATH}/pdq.jar and ${PUREQUERY_PATH}/pdqmgmt.jar are added to the class path as a convenience and are only required if the JDBC provider is used by pureQuery applications. If we are using this JDBC provider with pureQuery applications, set the PUREQUERY_PATH variable to the directory where IBM Optim™ pureQuery Runtime JAR files are stored.

  • Uses the following native library path:

    ${DB2_JCC_DRIVER_NATIVEPATH}
    

    If driverType is set to 2, the DB2_JCC_DRIVER_NATIVEPATH variable should be set to the directory in your DB2 client installation containing the native driver libraries. If driverType is set to 4, the native path is not used.
  • Requires the following DataStoreHelper class:

    com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper
    

  • Requires a valid authentication alias.
  • Requires properties:
    • databaseName - This is an actual database name (or a database alias) if the driverType is set to 4, or a locally cataloged database name if the driverType is set to 2.

      A database alias is an alternate name to an actual database name. Use a database alias for the databaseName when driverType is set to 4.

    • driverType - The JDBC connectivity type of a data source. There are two permitted values: 2 and 4. To use the type 2 driver, set this value to 2. To use type 4, set this value to 4.
    • serverName - The TCP/IP address or host name for the Distributed Relational Database Architecture™ (DRDA ) server. Provide a value for this property only if your driverType is set to 4. This property is not required if your driverType is set to 2.
    • portNumber - The TCP/IP port number where the DRDA server resides. Provide a value for this property only if your driverType is set to 4. This property is not required if your driverType is set to 2.
    • useTransactionRedirect Configure this property as a data source custom property if your backend uses the Database Partitioning Feature (DPF) and your partitioning key remains constant throughout a transaction. Activating the property affects how the IBM Data Server Driver for JDBC and SQLJ directs each connection request that begins a transaction with DB2. The JDBC driver is triggered to send those connection requests to the DPF node containing the target data of the first directable statement in the transaction, if such a statement exists. DB2 then directs the SQL statement to different partitions as needed; the transaction proceeds normally from the viewpoint of the application server.

      Use useTransactionRedirect for both driverType 2 and driverType 4 data sources. To configure the property, use either the wsadmin scripting tool or the administrative console page, Java EE resource provider or connection factory custom properties collection. Assign the property the value of true.

• DB2 Using IBM JCC Driver (XA)

  The DB2 Using IBM JCC Driver (XA) is a two-phase commit JCC provider for DB2 that uses the IBM Data Server Driver for JDBC and SQLJ. The IBM Data Server Driver is the next generation of the DB2 Universal JDBC driver. Data sources that we create under this provider support the use of XA to perform two-phase commit processing.

  This provider:

  • Is configurable in version 7.0 and later nodes.
  • Supports the following two-phase data source:

    com.ibm.db2.jcc.DB2XADataSource
    

  • Requires the following JDBC driver files:
    • db2jcc4.jar - This is the IBM Data Server Driver for JDBC and SQLJ .jar file. Set the DB2_JCC_DRIVER_PATH path variable to point to the db2jcc4.jar file. Once we install DB2, we can find this .jar file in the DB2 Java directory. For type 4 JDBC driver support from a client machine where DB2 is not installed, copy this file to the local machine. If we install any fixes or upgrades to DB2, we must update this file as well. We must also specify the fully qualified path of db2jcc4.jar as the value of the DB2_JCC_DRIVER_PATH environment variable.
    • db2jcc_license_cu.jar -This is the license file that allows access to the DB2 database. Use this .jar file or the next one to gain access to the database. This .jar file ships with the application server in a directory that is defined by the UNIVERSAL_JDBC_DRIVER_PATH environment variable.
    • db2jcc_license_cisuz.jar - This is the license file that allows access to the following databases:
      • DB2 Database for Linux, UNIX and Windows
      • DB2 for iSeries
      • DB2 for z/OS
      • DB2 for VM and VSE

      db2jcc_license_cisuz.jar does not ship with the application server. Place this file in the same directory as the db2jcc4.jar file, so that DB2_JCC_DRIVER_PATH points to both files.

    • The native files required by the IBM Data Server Driver for JDBC and SQLJ in the application server. These are only needed when driverType is set to 2.

  • Uses the following class paths:

    ${DB2_JCC_DRIVER_PATH}/db2jcc4.jar
    ${UNIVERSAL_JDBC_DRIVER_PATH}/db2jcc_license_cu.jar
    ${DB2_JCC_DRIVER_PATH}/db2jcc_license_cisuz.jar
    ${PUREQUERY_PATH}/pdq.jar
    ${PUREQUERY_PATH}/pdqmgmt.jar
    

    ${PUREQUERY_PATH}/pdq.jar and ${PUREQUERY_PATH}/pdqmgmt.jar are added to the class path as a convenience and are only required if the JDBC provider is used by pureQuery applications. If we are using this JDBC provider with pureQuery applications, set the PUREQUERY_PATH variable to the directory where IBM Optim pureQuery Runtime JAR files are stored.

  • Uses the following native library path:

    ${DB2_JCC_DRIVER_NATIVEPATH}
    

    If driverType is set to 2, the DB2_JCC_DRIVER_NATIVEPATH variable should be set to the directory in your DB2 client installation which contains the native driver libraries. If driverType is set to 4, the native path is not used.
  • Requires the following DataStoreHelper class:

    com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper
    

  • Requires a valid authentication alias.
  • Requires the following properties:
    • databaseName - This is an actual database name (or a database alias) if the driverType is set to 4, or a locally cataloged database name if the driverType is set to 2.

      A database alias is an alternate name to an actual database name. Use a database alias for the databaseName when driverType is set to 4.

    • driverType - The JDBC connectivity type of a data source. There are two permitted values: 2 and 4. To use the type 2 driver, set this value to 2. To use type 4, set this value to 4.
    • serverName - The TCP/IP address or host name for the Distributed Relational Database Architecture (DRDA) server. Provide a value for this property only if your driverType is set to 4. This property is not required if your driverType is set to 2.
    • portNumber - The TCP/IP port number where the DRDA server resides. Provide a value for this property only if your driverType is set to 4. This property is not required if your driverType is set to 2.
    • useTransactionRedirect Configure this property as a data source custom property if your backend uses the Database Partitioning Feature (DPF) and your partitioning key remains constant throughout a transaction. Activating the property affects how the IBM Data Server Driver for JDBC and SQLJ directs each connection request that begins a transaction with DB2. The JDBC driver is triggered to send those connection requests to the DPF node containing the target data of the first directable statement in the transaction, if such a statement exists. DB2 then directs the SQL statement to different partitions as needed; the transaction proceeds normally from the viewpoint of the application server.

      Use useTransactionRedirect for both driverType 2 and driverType 4 data sources. To configure the property, use either the wsadmin scripting tool or the administrative console page, Java EE resource provider or connection factory custom properties collection. Assign the property the value of true.

• DB2 Universal JDBC Driver provider

  The DB2 Universal JDBC Driver is an architecture-neutral JDBC driver for distributed and local DB2 access. Because the Universal Driver architecture is independent of any particular JDBC driver connectivity or target platform, it allows both Java connectivity (Type 4) or Java Native Interface (JNI) based connectivity (Type 2) in a single driver instance to DB2.

  This provider:

  • Allows applications to use both JDBC and Structured Query Language in Java (SQLJ) access.
  • Supports the following one-phase data source:

    com.ibm.db2.jcc.DB2ConnectionPoolDataSource
    

  • Requires the following JDBC driver files:
    • db2jcc.jar - Once we install DB2, we can find this .jar file in the DB2 java directory. For Type 4 JDBC driver support from a client machine where DB2 is not installed, copy this file to the local machine. If we install any fixes or upgrades to DB2, we must update this file as well. We must also set the DB2UNIVERSAL_JDBC_DRIVER_PATH path variable to point to the db2jcc.jar file.
    • db2jcc_license_cu.jar - This is the DB2 Universal JDBC driver license file that allows access to the DB2 Universal database. Use this .jar file or the next one to gain access to the database. This .jar file ships with the application server in a directory that is defined by the ${UNIVERSAL_JDBC_DRIVER_PATH} environment variable.
    • db2jcc_license_cisuz.jar - This is the DB2 Universal JDBC driver license file that allows access to the following databases:
      • DB2 Universal
      • DB2 for iSeries
      • DB2 for z/OS
      • DB2 for VM and VSE

      db2jcc_license_cisuz.jar does not ship with the application server. Place this file in the same directory as the db2jcc.jar file, so that the DB2UNIVERSAL_JDBC_DRIVER_PATH points to both.

    • Uses the following class path:

      ${DB2UNIVERSAL_JDBC_DRIVER_PATH}/db2jcc.jar
      ${UNIVERSAL_JDBC_DRIVER_PATH}/db2jcc_license_cu.jar
      ${DB2UNIVERSAL_JDBC_DRIVER_PATH}/db2jcc_license_cisuz.jar
      

    • Requires the following DataStoreHelper class:

      com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper
      

    • Requires a valid authentication alias.
    • Requires the following properties:
      • databaseName - This is an actual database name (or a database alias) if the driverType is set to 4, or a locally cataloged database name if the driverType is set to 2.

        A database alias is an alternate name to an actual database name. Use a database alias for the databaseName when driverType is set to 4.

      • driverType - The JDBC connectivity type of a data source. There are two permitted values: 2 and 4. To use Universal JDBC Driver type XA 2, set this value to 2. To use Universal JDBC Driver type XA 4, set this value to 4.
      • serverName - The TCP/IP address or host name for the Distributed Relational Database Architecture (DRDA) server. Provide a value for this property only if your driverType is set to 4. This property is not required if your driverType is set to 2.
      • portNumber - The TCP/IP port number where the DRDA server resides. Provide a value for this property only if your driverType is set to 4. This property is not required if your driverType is set to 2.
      • useTransactionRedirect Configure this property as a data source custom property if your backend uses the Database Partitioning Feature (DPF) of DB2 UDB Version 8.2, fix pack 10, and your partitioning key remains constant throughout a transaction. Activating the property affects how the DB2 Universal JDBC Driver directs each connection request that begins a transaction with DB2. The JDBC driver is triggered to send those connection requests to the DPF node containing the target data of the first directable statement in the transaction, if such a statement exists. DB2 then directs the SQL statement to different partitions as needed; the transaction proceeds normally from the viewpoint of the application server.

        Use useTransactionRedirect for both driverType 2 and driverType 4 data sources. To configure the property, use either the wsadmin scripting tool or the administrative console page Java EE resource provider or connection factory custom properties collection. Assign the property the value of true.

• IBMDB2 Universal JDBC Driver provider (XA)

  The DB2 Universal JDBC Driver provider (XA) is an architecture-neutral JDBC provider for distributed and local DB2 access. The IBM Data Server Driver for JDBC and SQLJ is the next generation of the DB2 Universal JDBC driver.Whether we use this provider for Java connectivity or Java Native Interface (JNI) based connectivity depends on the version of DB2 we are running. v6.0 and later of the application server requires DB2 8.1 Fix Pack 6. This version of DB2 only supports XA connectivity over the Java Native Interface (JNI) based connectivity (Type 2) driver. In order to use XA connectivity with the Type 4 driver, DB2 8.1 Fix Pack 7 or higher is required.

  This provider:

  • Allows applications to use both JDBC and SQLJ access.
  • Supports the two phase data source:

    com.ibm.db2.jcc.DB2XADataSource
    

  • Requires the following JDBC driver files:
    • db2jcc.jar - This is the DB2 Universal JDBC Driver .jar file. Once we install DB2, we can find this .jar file in the DB2 Java directory. For Type 4 JDBC driver support from a client machine where DB2 is not installed, copy this file to the local machine. If we install any fixes or upgrades to DB2, we must update this file as well. We must also specify the fully qualified path of db2jcc.jar as the value of the DB2UNIVERSAL_JDBC_DRIVER_PATH environment variable.
    • db2jcc_license_cu.jar - This is the DB2 Universal JDBC Driver license file that allows access to the DB2 Universal database. Use this .jar file or the next one to gain access to the database. This .jar file ships with the application server in the app_server_root/universalDriver/lib directory.
    • db2jcc_license_cisuz.jar - This is the DB2 Universal JDBC Driver license file that allows access to the following databases:
      • DB2 Universal
      • DB2 for iSeries
      • DB2 for z/OS
      • DB2 for VM and VSE

      We must use the correct license .jar file to access a specific database backend.

    • The native files required by the DB2 Universal JDBC Driver in the application server.

  • Uses the following class path:

    ${DB2UNIVERSAL_JDBC_DRIVER_PATH}/db2jcc.jar
    ${UNIVERSAL_JDBC_DRIVER_PATH}/db2jcc_license_cu.jar
    ${DB2UNIVERSAL_JDBC_DRIVER_PATH}/db2jcc_license_cisuz.jar
    ${DB2UNIVERSAL_JDBC_DRIVER_NATIVEPATH}
    

  • Requires the following DataStoreHelper class:

    com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper
    

  • Requires a valid authentication alias.
  • Requires properties:
    • databaseName - This is an actual database name (or a database alias) if the driverType is set to 4, or a locally cataloged database name if the driverType is set to 2.

      A database alias is an alternate name to an actual database name. Use a database alias for the databaseName when driverType is set to 4.

    • driverType - The JDBC connectivity type of a data source. There are two permitted values: 2 and 4. To use Universal JDBC Driver type 2 XA, set this value to 2. To use Universal JDBC Driver type 4 XA (which requires DB2 8.1 Fix Pack 7 or higher), set this value to 4.
    • serverName - The TCP/IP address or host name for the Distributed Relational Database Architecture (DRDA) server. Provide a value for this property only if your driverType is set to 4. This property is not required if your driverType is set to 2.
    • portNumber - The TCP/IP port number where the DRDA server resides. Provide a value for this property only if your driverType is set to 4. This property is not required if your driverType is set to 2.
    • useTransactionRedirect Configure this property as a data source custom property if your backend uses the Database Partitioning Feature (DPF) of DB2 UDB Version 8.2, fix pack 10, and your partitioning key remains constant throughout a transaction. Activating the property affects how the DB2 Universal JDBC Driver directs each connection request that begins a transaction with DB2. The JDBC driver is triggered to send those connection requests to the DPF node containing the target data of the first directable statement in the transaction, if such a statement exists. DB2 then directs the SQL statement to different partitions as needed; the transaction proceeds normally from the viewpoint of the application server.

      Use useTransactionRedirect for both driverType 2 and driverType 4 data sources. To configure the property, use either the wsadmin scripting tool or the administrative console page Java EE resource provider or connection factory custom properties collection. Assign the property the value of true.

Configure a JDBC provider and data source

Configure a JDBC provider

Configure a data source

Create and configure a JDBC provider and data source using the JMX API

Configure new data source custom properties using wsadmin

Java EE resource provider or connection factory custom properties collection