+

Search Tips   |   Advanced Search

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

The z/OS operating system requires that we configure the DB2 Universal JDBC Driver and the database to ensure interoperability. Within WebSphere Application Server, configure a Java Database Connectivity (JDBC) provider object and a data source object to implement the driver capabilities for the applications.

The available versions of the DB2 Universal JDBC Driver to connect with DB2 on z/OS are as follows:

Consult the DB2 service updates for available enhancements on the version that use.

Migration tip: If we are replacing the DB2 for 390 and z/OS Legacy JDBC driver with the DB2 Universal JDBC Driver, we can migrate our existing JDBC provider settings. See the topic Migrating from the JDBC/SQLJ Driver for OS/390 and z/OS to the DB2 Universal JDBC Driver in the Information Management Software for z/OS Solutions Information Center for more information.


Tasks

  1. Install the driver class files and any necessary native files in an available HFS directory.

    Native files are class files that some versions of the DB2 Universal JDBC Driver require for running on the z/OS operating system.

  2. Configure the driver and database for interoperability

    1. Bind the required DB2 packages

      Any application that executes SQL statements in DB2 for z/OS, the Universal JDBC driver must first bind with DB2 the packages that represent the SQL statements to be executed.

      The specific details of the bind utility and bind process are described by the readme file provided with the installed DB2 Universal JDBC Driver. Refer to this readme file for details on how to set up and perform the required binding.

      The utility requires the server name or IP address, the port number, and the database name (the database location on z/OS) for the target DB2. To get this information, issue a DB2 -DISPLAY DDF command on the target DB2 system. This command displays the IPADDR (IP address), the SQL DOMAIN (server name), the TCPPORT number, and the LOCATION (database name/location) for you to use as input to the utility.

      We must perform the bind process for each target DB2 accessed using the DB2 Universal JDBC Driver.

    2. Set up to handle in-doubt transactions

      Perform this setup once for each target DB2 for z/OS v7 location that is accessed using the DB2 Universal JDBC Driver Type 4 XA support.

      DB2 for z/OS v7 does not implement Java EE XA support, therefore, the Type 4 driver XA processing uses DB2 V7 two-phase commit protocol and a table in each location (database) to store a list of global transactions in doubt or finished but not committed.

      This table must be set up at each DB2 V7 location that is accessed. To do the set up, use the In-Doubt Utility, which is included as part of the installed DB2 Universal JDBC Driver. The utility creates the SYSIBM.INDOUBT Table that stores information about In-Doubt Global Transactions. This utility also binds the package T4XAIndbtPkg, which contains the SQL statements to insert and delete from the SYSIBM.INDOUBT Table. The T4XAIndbtPkg package is written with SQLJ.

      This installation process requires that the target DB2 subsystem is configured with DDF enabled for incoming TCP/IP connections.

      1. To enable DDF on the target DB2, issue the DB2 -START DDF command on that system.
      2. This utility requires the server name or IP address and the port number for the target DB2 V7. To obtain this information, issue a DB2 -DISPLAY DDF command on the target DB2 V7 system. The IPADDR (IP address), the SQL DOMAIN (server name), and the TCPPORT number that can be used as input to the utility are displayed.

      To find more detailed information about the In-Doubt utility, refer to the DB2 Universal Databaseā„¢ for z/OS Version 7 Application Programming Guide and Reference for JavaTM publication. We can download it from the Library section of the DB2 Universal Database for z/OS v7 product information web pages. Within this publication, search for discussion about the utility under DB2T4XAIndoubtUtil, which is the official name of the In-Doubt utility.

      The previously described setup for in-doubt transactions is not a requirement for DB2 FOR z/OS v8 servers because DB2 FOR z/OS Version 8 natively supports XA commands over DRDA and manages the In-Doubt Global Transactions internally.

    3. Define a db2.jcc.propertiesFile

      A db2.jcc.propertiesFile for use by DB2 Universal JDBC Driver Type 2 processing under WAS for z/OS can be created and specified as input to the driver. This runtime properties file is for use in specifying various runtime options that the DB2 Universal JDBC Driver uses for Type 2 connectivity. These options are specified as properties in the form of parameter=value. Refer to the README file packaged with the installed DB2 Universal JDBC Driver for a detailed description of each of the properties.

      This file is not required; however, if it is not provided, universal driver default processing is performed.

      If we have multiple local DB2 subsystems, we can specify the db2.jcc.ssid property to indicate the default DB2 subsystem identifier (not location name). When this property is specified, the DB2 Universal JDBC Driver Type 2 connects to this identifier as the local subsystem name. The property overrides the subsystem identifier from the DSNHDECP load module. If we want the server to use the DSNHDECP load module to specify the subsystem identifier, this load module must be included in a steplib data set in the servant region PROCs for each server that uses the DB2 identified by the subsystem ID. For more information about using this load module, see the readme file packaged with the universal driver.

      Although the db2.jcc.propertiesFile is not required, if we choose to define the file, specify the fully qualified HFS file name as a JVM System property as follows:

      • db2.jcc.propertiesFile = <fully-qualified-hfs-filename>

      The driver-general properties are typically specific to a driver load, for example, server, rather than all servers using the JDBC provider, therefore, it is best to set this JVM property at the server level. To define the db2.jcc.propertiesFile= property to the server level using the WAS for z/OS administrative console:

      1. Under the WAS for z/OS administrative console, go to Servers > Application Servers, then click the server to which we want to add the JVM property.

      2. On the selected server page, expand Java and Process Management and click Process Definition > Servant.

      3. On the Servant page, click Additional Properties, then click Java Virtual Machine.

      4. On the Java Virtual Machine page, click Additional Properties, then click Custom Properties.

      5. On the Custom Properties page, scroll down and click New to configure a new JVM property for the selected server. The name of the property is db2.jcc.propertiesFile. The value of the property is the fully qualified HFS file name that we created and initialized with the DB2 Universal JDBC Driver properties. The Type 2 driver uses these properties for the selected server

      6. Click Ok.

      7. Click Save to save the new JVM property.

  3. Define a JDBC provider for the DB2 Universal JDBC Driver. The JDBC provider object encapsulates the driver classes for implementation in WAS.

    1. From the WAS for z/OS administrative console, click Resources > JDBC > JDBC Providers.

    2. Select the scope at which applications can use the JDBC provider. This scope becomes the scope of any data source associated with this provider. We can choose a cell, node, cluster, or server. See topic, administrative console scope settings.

    3. Click New. This action causes the Create a new JDBC Provider wizard to launch.

    4. Use the first drop-down list to select DB2 for z/OS as the database type.

    5. Select the DB2 Universal JDBC Driver provider as the JDBC provider type in the second drop-down list.

    6. From the third drop-down list, select the implementation type necessary for the application.

      If the application does not require that connections support two-phase commit transactions, and we plan to use type 4 connectivity, choose Connection Pool Data Source. If we use the connection pool data source with type 2 connectivity, however, Application Server on z/OS uses RRS to process both one-phase and two-phase transactions.

      Restriction: Do not select Connection Pool Data Source if the installation has the z/OS Application Connectivity to DB2 for z/OS feature defined to WAS for z/OS. Only the XA implementation of the DB2 Universal JDBC Driver supports this feature.

      Choose XA Data Source if we plan to use driver type 4, and the application requires connections that support two-phase commit transactions. Use only driverType 4 connectivity for the XA data source.

      After selecting an implementation type, the wizard fills the name and the description fields for our JDBC provider. We can type different values for these fields; they exist for administrative purposes only.

    7. Click Next after we have defined the database type, provider type, and implementation type. Now we see the wizard page Enter database class path information.

      Typically we do not need to change the class path that already populates the field. (That class path is the value of the WebSphere environment variable displayed on this page, in the form of ${DATABASE_JDBC_DRIVER_PATH}.) ost likely you also do not need to change the native library path or the data source implementation class name.

    8. Click Next to see a summary of our JDBC provider settings.

    9. Click Finish if we are satisfied with the entire JDBC provider configuration. The JDBC provider collection page displays, which shows the new JDBC provider in a table along with other providers configured for the same scope.

  4. Define a data source. WAS uses the data source object to obtain database connections and manage those connections.

    1. From the WAS for z/OS administrative console, access the page for the data source version that the applications require. If we need support for two-phase transactions, use only a data source of the latest standard version. Version 4 data sources do not support connections that participate in two-phase transactions.

      Navigate to the appropriate page in one of two ways:

      • Click Resources > JDBC > Data sources, or Data sources (WAS Version 4).

      • Click Resources > JDBC > JDBC providers > JDBC_provider > Data sources, or Data sources (WAS Version 4).

    2. Select the scope at which applications can use the data source. We can choose a cell, node, cluster, or server. See topic Administrative console scope settings.

      Version 4 only: From this point onward, the steps for creating data sources (WAS Version 4) differ from the steps for creating data sources of the latest standard version. To configure a Version 4 data source complete the following steps:

      • Click New to proceed to the console page for defining required properties.

      • On this properties page specify values for the fields that are grouped under the heading Configuration. Application Server requires these properties to implement the JDBC driver classes; see the topic, Data source minimum required settings, by vendor to learn about acceptable values.

      • Save the configuration by clicking OK. We are now finished with the primary data source configuration tasks.

      • Optional: Define additional properties supported by the DB2 Universal JDBC provider. Application Server calls them custom properties, and requires that we set them on the data source as well. Begin by clicking the Custom Properties link that is now displayed on the administrative console page. We can learn about optional data source properties in the Application Programming Guide and Reference for Java for your version of DB2 for z/OS.

    3. Click New. This action causes the Create a data source wizard to launch and display the Enter basic data source information page. The first field is the scope field, which is read-only. This field displays the previous scope selection.

    4. Type a data source name in the Data source name field. This name identifies the data source for administrative purposes only.

    5. Type a JNDI name in the JNDI name field. WAS uses the JNDI name to bind application resource references to this data source. For more information about JNDI, see the topic, Naming.

    6. Configure a component-managed alias to secure the data source if we plan to implement driverType 4 connectivity with the DB2 Universal JDBC Driver. If we plan to use driverType 2 connectivity, we do not have to set an alias. In this case the connection manager uses a default authentication alias, which is the user identity of a thread when that thread delivers a getConnection request.

      A component-managed alias consists of an ID and password specified in an application for data source authentication. Therefore, the alias that we set on the data source must be identical to the alias in the application code. For more information about Java 2 Connector (J2C) security, see the topic, Managing Java 2 Connector Architecture authentication data entries.

      To set a component-managed alias, either select an existing alias or create a new one.

      • Use the drop-down list to select an existing component-managed authentication alias.

      • To create an alias, click the create a new one link. This action closes the data source wizard and triggers the administrative console to display the J2C authentication data collection page. Click New to define a new alias. Click OK to save our settings and view the new alias on the J2C authentication data collection page. Restart the data source wizard by navigating back to the data source collection page, selecting the appropriate scope, and clicking New.

    7. Click Next to see the wizard page Select JDBC provider.
    8. Either select an existing JDBC provider, or create a provider.

      To select an existing JDBC provider:

      1. Click Select an existing JDBC provider.

      2. Select a JDBC driver from the drop-down list.

      3. Click Next. You now see the page entitled Enter database-specific properties for the data source.

      To create a JDBC provider:

      1. Click Create new JDBC provider.

      2. Click Next to see the Create JDBC provider page.

      3. Use the first drop-down list to select DB2 for z/OS as the database type.

      4. Select the DB2 Universal JDBC Driver provider as the JDBC provider type in the second drop-down list.

      5. From the third drop-down list, select the implementation type necessary for the application.

        If the application does not require that connections support two-phase commit transactions, choose Connection Pool Data Source. Both driverType 2 and driverType 4 connectivity implementations of the DB2 Universal JDBC Driver support connection pool data sources.

        Restriction: Do not select this provider if the installation has the z/OS Application Connectivity to DB2 for z/OS feature defined to WAS for z/OS. Only the XA implementation of the DB2 Universal JDBC Driver supports this feature.

        Choose XA Data Source if the application requires connections that support two-phase commit transactions. Applications that use this data source configuration have the benefit of container-managed transaction recovery. Use only driverType 4 connectivity for the XA implementation.

        After selecting an implementation type, the wizard fills the name and the description fields for our JDBC provider. We can type different values for these fields; they exist for administrative purposes only.

      6. Click Next after we have defined the database type, provider type, and implementation type. Now we see the wizard page Enter database class path information.

        Typically we do not need to change the class path that already populates the field. (That class path is the value of the WebSphere environment variable displayed on this page, in the form of ${DATABASE_JDBC_DRIVER_PATH}.) ost likely you also do not need to change the native library path or the data source implementation class name.

      7. Click Next. You now see the page entitled Enter database-specific properties for the data source.

    9. Click Use this data source in container managed persistence (CMP) if container managed persistence (CMP) enterprise beans must access this data source.

    10. Specify all the remaining properties, as they are required for implementation of the DB2 Universal JDBC Driver. These properties include:

      • Database name, which is the location name of the target database used when establishing connections with this data source
      • driverType, which is the JDBC connectivity type used by the data source
      • Server name, which is the TCP/IP address or host name for the Distributed Relational Database Architectureā„¢ (DRDA) server.

        This property is required only if driverType is set to 4. This property is not used if driverType is set to 2.

      • Port number, which is the TCP/IP port number where the DRDA server resides.

        Provide a value for this property only if driverType is set to 4. Do not set this property if driverType is set to 2.

    11. Click Finish to save the configuration and exit the wizard. The Data source collection page displays, which shows the new configuration in a table along with other data sources configured for the same scope.


What to do next

We can override the default values for some data source properties. Click the new data source link in the table to view the general configuration page for required data source properties. We can also define additional properties supported by the DB2 Universal JDBC Driver. Application Server requires that we set them as custom properties on the data source. Learn about optional data source properties in the Application Programming Guide and Reference for Java for our version of DB2 for z/OS.

If we have multiple local DB2 subsystems, we can specify a DB2 subsystem identifier (not location name) on the ssid custom property on your data source. When this custom property is specified, the data source connects to this identifier as the local subsystem name. The ssid custom property overrides both the db2.jcc.ssid property in the db2.jcc.propertiesFile and the subsystem identifier from the DSNHDECP load module.


Subtopics


Related:

  • Naming
  • Manage Java 2 Connector Architecture authentication data entries for JAAS
  • Data source minimum required settings, by vendor
  • Administrative console scope settings