(ZOS) Data source minimum required settings for DB2 on z/OS and an application server on z/OS

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 running on z/OS and will be accessed by applications in an application server that is also running on z/OS.

We can configure the following types of providers:

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

• 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 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. After installing 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
    

    When specifying the directory path for ${DB2_JCC_DRIVER_PATH}, ${UNIVERSAL_JDBC_DRIVER_PATH}, and ${DB2_JCC_DRIVER_PATH} do not specify a path containing any special characters as identified in section 2.4.3 of the RFC 2396: Uniform Resource Identifiers (URI): Generic Syntax ${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 if the driverType is set to 4, or a locally cataloged database name if the driverType is set to 2.
    • 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.

  Do not use the type 2 JDBC driver on the application server for z/OS.

  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. After installing 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
    

    When specifying the directory path for ${DB2_JCC_DRIVER_PATH}, ${UNIVERSAL_JDBC_DRIVER_PATH}, and ${DB2_JCC_DRIVER_PATH} do not specify a path containing any special characters as identified in section 2.4.3 of the RFC 2396: Uniform Resource Identifiers (URI): Generic Syntax ${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 if the driverType is set to 4, or a locally cataloged database name if the driverType is set to 2.
    • 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 DB2 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. The DB2 Universal JDBC Driver provider supports both JDBC and Structured Query Language in Java (SQLJ) access to DB2 databases, as well as SQLJ use with CMP.

  To use this provider, we must have the DB2 Universal JDBC Driver for DB2 installed and configured for the application server for z/OS. Refer to the topic, Using the DB2 Universal JDBC Driver to access DB2 for z/OS.

  This provider:

  • Supports the following data source:

    com.ibm.db2.jcc.DB2ConnectionPoolDataSource
    

    This data source can support two-phase transactions (for the application server on the z/OS platform only). If we set the DB2 Universal JDBC Driver for type 2 connectivity, the application server running on z/OS uses RRS to process transactions, including two-phase commit transactions. When used with driver type 4 connectivity, this data source implementation supports one-phase transactions only.
  • Requires JDBC driver files:
    • db2jcc.jar - This is the DB2 Universal JDBC Driver .jar file, located in the DB2 installation directory. Specify the fully-qualified path of this .jar file for the value of our variable DB2UNIVERSAL_JDBC_DRIVER_PATH.
    • db2jcc_license_cisuz.jar - This DB2 Universal JDBC Driver license file is included with DB2. The installation program for DB2 places the license file in the same DB2 directory as db2jcc.jar. Therefore your variable DB2UNIVERSAL_JDBC_DRIVER_PATH ultimately points to both files.

      The driver file db2jcc_license_cu.jar ships with the application server in the app_server_root/universalDriver/lib directory. It is automatically set to the UNIVERSAL_JDBC_DRIVER_PATH variable. Although this driver file does not provide access to DB2 for z/OS, we can leave it as part of the variable definition.

    • The native files of .so type are required by the DB2 Universal JDBC Driver in the application server for z/OS. Set the library path variable DB2UNIVERSAL_JDBC_DRIVER_NATIVEPATH to the directory containing the native files. In cases that do not require native files, set the DB2UNIVERSAL_JDBC_DRIVER_NATIVEPATH to null.

  • 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}
    

    When specifying the directory path for ${DB2_JCC_DRIVER_PATH}, ${UNIVERSAL_JDBC_DRIVER_PATH}, and ${DB2_JCC_DRIVER_PATH} do not specify a path containing any special characters as identified in section 2.4.3 of the RFC 2396: Uniform Resource Identifiers (URI): Generic SyntaxRequires the following DataStoreHelper class:

    com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper
    

  • Requires a valid authentication alias if the driverType property is set to 4. If the driverType property is set to 2, specify a component-managed authentication alias to use the data source with res-auth APPLICATION.

    In the case where driverType 2 is specified and the data source is used with res-auth CONTAINER, we can specify a container-managed authentication alias; however, it is not required. If we do not specify a container-managed alias, the user identity associated with a connection created by the data source will be the user identity associated with the current thread at the time the connection is obtained.

  • Requires the following properties:
    • databaseName - This is an actual database name if the driverType is set to 4, or a locally cataloged database name if the driverType is set to 2.
    • driverType - The JDBC connectivity type of a data source. There are two permitted values: 2 and 4. To use Universal JDBC Driver type 2, set this value to 2. To use Universal JDBC Driver 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.

      DB2 supports additional connection properties for the DB2 Universal JDBC Driver; set them as custom properties on the data source. For information about these optional data source properties, consult the Application Programming Guide and Reference for Java for your version of DB2 for z/OS.

• DB2 Universal JDBC Provider (XA)

  The XA DB2 Universal JDBC provider uses the DB2 Universal JDBC Driver to provide access to DB2 databases. The Universal JDBC Driver supports Java communication-based connectivity (driver Type 4). For XA transactions, driver type 2 connectivity is not supported by the DB2 Universal JDBC Driver on the application server for z/OS.

  To use this provider, we must have the DB2 Universal JDBC Driver for DB2 installed and configured for the application server for z/OS, or we must have the z/OS Application Connectivity to DB2 for z/OS feature installed and configured for the application server for z/OS. Refer to the topic, Using the DB2 Universal JDBC Driver to access DB2 for z/OS.

  This provider:

  • Allows applications to use both JDBC and Structured Query Language in Java (SQLJ) access to DB2 databases. SQLJ use with CMP is also supported.
  • Does not support the creation of Version 4.0 data sources.
  • 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, located in the DB2 installation directory. Specify the fully-qualified path of this .jar file for the value of the WebSphere variable DB2UNIVERSAL_JDBC_DRIVER_PATH. The application server uses this value to define the JDBC provider.
    • db2jcc_license_cisuz.jar - This DB2 Universal JDBC Driver license file is included with DB2. The installation program for DB2 places the license file in the same DB2 directory as db2jcc.jar. Therefore your variable DB2UNIVERSAL_JDBC_DRIVER_PATH ultimately points to both files.

      The driver file db2jcc_license_cu.jar ships with the application server in the app_server_root/universalDriver/lib directory. The UNIVERSAL_JDBC_DRIVER_PATH variable is set to this directory location by default. Although this driver file does not provide access to DB2 for z/OS, we can leave it as part of the variable definition.

    • The native files of .so type are not required by the DB2 Universal JDBC Driver in the application server for z/OS if the server only uses type 4 drivers. In this case, we can set the value of DB2UNIVERSAL_JDBC_DRIVER_NATIVEPATH to null.

      If we have servers that run both type 2 and type 4 drivers, or might have both types in the future, we can set this value to a valid file path for the native files. The variable will be used for the type 2 drivers, but the value for DB2UNIVERSAL_JDBC_DRIVER_NATIVEPATH should not cause any adverse effects for cases in which the native files are not required.bprac

  • 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}
    

    When specifying the directory path for ${DB2_JCC_DRIVER_PATH}, ${UNIVERSAL_JDBC_DRIVER_PATH}, and ${DB2_JCC_DRIVER_PATH} do not specify a path containing any special characters as identified in section 2.4.3 of the RFC 2396: Uniform Resource Identifiers (URI): Generic SyntaxRequires the following DataStoreHelper class:

    com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper
    

  • Requires a valid authentication alias.
  • Requires the following properties:
    • databaseName - This is a locally cataloged database name.
    • driverType - This is the JDBC connectivity type of a data source. If we are running a version of DB2 prior to DB2 V8.1 FP6, we are restricted to using only the type 2 driver.
    • 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.

      DB2 supports additional connection properties for the DB2 Universal JDBC Driver; set them as custom properties on the data source. For information about these optional data source properties, consult the Application Programming Guide and Reference for Java for your version of DB2 for z/OS.

Related:

RFC 2396: Uniform Resource Identifiers (URI): Generic Syntax

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

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

Java EE resource provider or connection factory custom properties collection