Introduction

DB2 Universal Database^TM supports the Java programming environment through two programming interfaces (APIs) for database accessibility, JDBC and SQLJ. These implementations are known as Java drivers.

We'll begin by introducing these drivers and describing DB2's support for them. Then we will examine in detail the various forms in which these drivers can be used. These forms are closely tied to the scenarios they are best suited for. Java programmers would also refer to these various forms of implementations as driver types. We will also propose some best practices for each such accessibility scenario in terms of DB2 Java drivers and types specific to accessing Host databases.

We conclude the discussion with a brief look at underlying and target 64-bit DB2 database instance setup and provide some best practices recommendations and performance enhancement tips.

Back to top

Overview of DB2 Java drivers

Java provides two main APIs for database access: JDBC and SQLJ.

JDBC

JDBC is a mandatory component of the Java programming language as defined in the Java 2, Standard Edition (J2SE) specification. JDBC is a vendor-neutral dynamic SQL interface that provides data access to your application through standardized Java methods. These methods and interfaces are packaged as DB2 JDBC drivers and are categorized as Legacy CLI-based drivers and new Universal JDBC drivers.

Legacy CLI-based JDBC driver
The Legacy CLI-based JDBC driver is provided in the file db2java.zip. The implementation class name is com.ibm.db2.jdbc.app.DB2Driver or COM.ibm.db2.jdbc.net.DB2Driver. This driver has been used for the current J2EE certifications. The nickname "app driver" (type 2) comes from the notion that this driver will perform a native connect through a local DB2 UDB client to a remote database, and from its package name. The type 3 driver is known as the net driver.

Universal JDBC driver
The universal JDBC driver is provided in the file db2jcc.jar (type 2 implementation requires a native library). The implementation class name is com.ibm.db2.jcc.DB2Driver. This driver is new to DB2 UDB for Linux, UNIX, and Windows v8.1.

In the initial implementation (v8.1), this driver was for direct Java connectivity to DB2 servers using a type 4 driver architecture. In DB2 v8.1.2, you can use this driver in a type 2 architecture. One of the key reasons for using the driver in a type 2 architecture is for local application performance and for distributed transaction support. The Universal JDBC Type 2 driver supports distributed transactions using com.ibm.db2.jcc.DB2XADataSource, and connection pooling using com.ibm.db2.jcc.DB2ConnectionPoolDataSource.

SQLJ

SQLJ is a standard development model for data access from Java applications. The SQLJ API is defined within the SQL 1999 specification. SQLJ provides the unique ability to develop using static SQL statements and control access at the DB2 package level. The new Universal Driver for JDBC and SQLJ provides support for both JDBC and SQLJ APIs in a single implementation. JDBC and SQLJ can interoperate in the same application.

Figure 1 shows some of the possible uses for these two drivers to access DB2 data from a Java programming perspective.

Packaging information

DB2 UDB Application Development client contain driver code to connect to a DB2 UDB database on UNIX, Linux, Windows, z/OS, or OS/400. If you plan on accessing DB2 UDB for z/OS or DB2 UDB for OS/400, use the DB2 Connect product for your development environment. The DB2 UDB Application Development client contains all of the necessary drivers to develop C, COBOL, and Java applications. If you plan to develop a multi-tier application using a J2EE application sever, such as WebSphere Application Server, each of the application servers will require the DB2 UDB Runtime Client or the DB2 UDB Application Development Client.

DB2 Connect

DB2 Connect makes your host data directly available to your personal computer and LAN-based workstations. Along with connectivity to z/OS and iSeries DB2 databases, various other middle tier functions are provided by DB2 Connect, such as:

• Connection pooling
  
  • 1-1-1 relationship between applications, coordinating agents, and connections into DB2
  • Connection attributes and corresponding host connection returned to the pool on disconnect
• Connection concentrator (transaction pooling):
  
  • N-1 relationship between applications and connections into DB2
  • Connection agent and corresponding host connection returned to the pool on commit/rollback
  • Connection can be used by another application as soon as transaction completes
  • Many different applications can reuse the same DB2 thread (DBAT)

For more detailed information on these drivers and accessibility, see the related content for this article.

Back to top

Typical host accessibility scenarios

Driver type 1

A JDBC type 1 driver is based on a JDBC-ODBC bridge. Therefore an ODBC driver can be used in combination with this JDBC driver. IBM does not provide a Type 1 driver, and it is not a recommended environment.

Driver type 2

A JDBC type 2 driver relies on an OS-specific library (shared library) to communicate with a RDBMS. The application loads the JDBC driver, and the driver uses the shared library to communicate with the DB2 server. Both legacy and universal drivers have Type 2 implementations available. The legacy driver implementation is available in DB2 v8.1, and the universal driver implementation is provided in DB2 v8.1.2 and higher.

Usability
This scenario is best suited for applications where the application server has a DB2 Connect gateway available. If the gateway is locally available, there are performance advantages to using this scenario, as there is minimal overhead and network lag in loading native DB2 libraries. However, not keeping DB2 Connect local to the application server affects availability since there is an inherent risk of system outage at both tiers if the node fails.

Application side settings:

JDBC driver classpath:

Universal driver:

• $DB2_Instance_Path/sqllib/java/db2jcc.jar
• $DB2_Instance_Path/sqllib/java/cisuz_licence.jar (as applicable)

LegacyCLI-based JDBC driver:

• $DB2_Instance_Path/sqllib/java/db2java.zip

Datasource DB URL:

• jdbc:db2:db_alias
• example: jdbc:db2:t2db

Datasource implementation

• Universal driver: COM.ibm.db2.jcc
• Legacy CLI-based driver: COM.ibm.db2.jdbc.app

DB2 Connect settings:
The settings for DB2 Connect are established through the DB2 catalog command.

• db2 catalog tcpip node node_alias remote hostname server portnumber
  example: db2 catalog tcpip node t2node remote hostmachine server 447
• db2 catalog dcs db dcs_alias as host_subsystem
  example: db2 catalog dcs db t2dcs as hostdcs
• db2 catalog db dcs_alias as db_alias at node node_alias authentication dcs
  example: db2 catalog db t2dcs as t2db at node t2node authentication dcs

Hostmachine, server (447 usually), dcs database, and so forth should be modified to suit your settings.

Note: TCP/IP protocol settings for your instance should be configured before preceding any further. The following commands confirm your TCP/IP settings:

• db2set db2comm=tcpip
• db2 update dbm cfg using svcename portnumber

Figure 2 details this scenario graphically. The applications call the DB2 libraries locally, and the DB2 catalog provides access to the host databases.

Driver type 2 with XA support

A JDBC type 2 driver with XA (extended architecture) support conforms to Java Transaction APIs (JTA) which act as a standard interface between a transaction manager and the parties involved in a distributed transaction system: the resource manager, the application server, and the transactional applications. Both legacy and universal drivers have type 2 implementations that support JTA and are available in DB2 v8.1.2 and up.

Usability
This scenario is best suited for applications that contain two-phase commit transactions involving multiple target databases, and where the application server has a DB2 Connect gateway available. There are some specific setup details that pertain to this scenario to set up the transaction manager to enable XA support.

Application side settings:

JDBC driver classpath:

• same as JDBC type 2 driver settings

DB2 Connect settings:

JDBC driver classpath:

• Same as JDBC type 2 driver settings

In addition to these configuration settings, many of the multisite update scenarios that involve host and iSeries database servers require that the Sync Point Manager (SPM) component be configured. When a DB2 instance is created, the DB2 SPM is automatically configured with default settings. The DB2 Connect SPM component acts as a translator between the XA protocol used between the application and the DB2 Connect server, and the native DRDA two-phase commit protocol used between the DB2 Connect server and DB2 z/OS. The SPM maintains its own logs of transactions, and it is these logs that are used during various XA transaction manager activities, typically resynchronization. The DB2 Connect SPM acts as a proxy for DB2 z/OS from the XA transaction manager's perspective.

Also, the DB2 Connect Transaction Processing Monitor Name (tp_mon_name) configuration parameter should be considered. The tp_mon_name database manager configuration parameter identifies the name of the TP monitor product being used (CICS, or ENCINA, for example). Here are the commands that are needed to set up these parameters:

• db2 update dbm cfg using spm_name spm_name
• db2 update dbm cfg using tp_mon_name tp mon

For more details on these parameters please refer to your DB2 Connect Guide.

Driver type 3

The JDBC type 3 driver is a pure Java implementation that must communicate with a DB2 JDBC applet server to access DB2 data. The JDBC type 3 driver is often referred to as the "net driver", named after its package name: COM.ibm.db2.jdbc.net. DB2 Version 8.1 supports the net driver, and you can use it with JDBC applications.

Usability
The type 3 driver was designed to enable Java applets for access to DB2 data sources. Currently, the WebSphere Application Server and other J2EE servers do not support the DB2 type 3 driver, because that driver does not support distributed transactions (JTA). The type 3 driver will not be enhanced in future versions.

We are encouraging the use of our Universal JDBC type 4 driver as a replacement for the Legacy CLI-based type 3 driver. DB2 legacy type 3 drivers have been deprecated as of DB2 v8.1.

DB2 Settings:
The type 3 driver requires that the db2java.zip driver is always at the same maintenance level as the DB2 applet server. This is not a problem if the driver is used within applets because the browser will download the appropriate db2java.zip file during application execution. Many customers use the type 3 driver instead of the type 2 driver to avoid installing the DB2 client and cataloging the database to create the database directory information required for type 2 driver connections using the Legacy CLI-based driver.

Driver type 4

A type 4 driver is a Java-only JDBC driver that connects directly to the database server. DB2 UDB for Linux, UNIX, and Windows v8.1 introduced a type 4 driver known as the Universal JDBC driver.

Usability
DB2 type 4 driver provides all-Java remote connectivity using Distributed Relational Database Architecture (DRDA). This is a natural migration path for any application using the type 3 drivers today.

Application side settings:

JDBC driver classpath:

• $DB2_Instance_Path/sqllib/java/db2jcc.jar
• $DB2_Instance_Path/sqllib/java/db2jcc_license_cisuz.jar (as applicable)

Notice that the universal type 2 and universal type 4 drivers share the same implementation class name. There are two ways to distinguish which driver DB2 will instantiate internally:

• Use a connection property (driverType) to determine if the shared library will be used for connectivity (type 2) or if the driver will initiate a direct connection from the Java application (type 4).
• Use a different connection URL pattern to indicate whether you want type 2 or type 4 behavior.
  An example of a type 4 URL pattern: the string jdbc:db2://server1:447/sample tells the JDBC driver to connect directly from the Java application to the DB2 server (server1) to a database called sample within the DB2 instance configured on the DB2 server (hostname is server1) that is listening on port 447.

Here's an example of a type 2 URL pattern: the string jdbc:db2:sample. Information about the DB2 server (server1) and port (447) is available the DB2 client catalog directory.

Datasource DB URL:

• jdbc:db2://serverName:portNumber/t4db

Figure 4 illustrates a DB2 type 4 driver usage.

Note: You can use a DB2 Connect gateway to provide a stable middle tier and utilize features such as connection pooling and failover to your architecture and still use DB2 type 4 Drivers. You would have to catalog your Host databases on the DB2 Connect gateway. Figure 5 illustrates such a setup.

Driver Type 4 with XA support

The DB2 universal driver does not currently support XA. This support is planned for a coming release.

64-bit target instances

DB2 supports 64-bit instances that provide various performance improvements over 32-bit instances. Applications requiring large amount of IO benefit from the 64-bit instance due to the availability of larger address spaces on the 64-bit architecture operating systems and thus larger buffer pools and so on.

Usability
32-bit applications such as WebSphere Application Server link to 64-bit DB2 native libraries. When you attempt to link 32-bit objects against the 64-bit DB2 application library, an operating system linker error message is returned. The solution is to use a 32-bit DB2 client with 32-bit applications such as WebSphere Application Server, WebSphere MQ, and so on, to access data on 64-bit DB2 instances.

If the two instances are on the same machine, there will be a minor performance overhead compared to using just a single instance, as there is an added TCP/IP lag. However, if the instances you are accessing are on remote machines, there is no additional performance degradation and you may use all the 64-bit features.

DB2 Settings:
Assume DB2INST1 is a 32-bit client instance on host DB2HOST1, and DB2INST2 is a 64-bit instance with TCP/IP listener port defined as 50000, containing a database called TESTDB on host DB2HOST2.
(Note that these two instances could be on the same host as well).
For this scenario, the setup would be as follows:

For both DB2 Universal Type 2 and Legacy CLI-based Type 2 drivers:

From DB2INST1 issue the following command:

• db2 catalog tcpip node DB2HOST2 remote DB2HOST2 server 50000
• db2 catalog db TESTDB as TESTDB at node DB2HOST2

Now use the database connection URL as follows:

• jdbc:db2:TESTDB

For DB2 Universal Type 4 drivers:
There is no need to catalog the remote database
You can use the database URL as follows:

• jdbc:db2://DB2HOST2:50000/TESTDB

WebSphere Settings:
Datasources in WebSphere should be configured with the method described above. Additionally, you would need to configure WebSphere JDBC driver CLASSPATH settings with the location of the DB2 driver file and other necessary files provided with the 32-bit instance.
Example: $DB2INST1/sqllib/java/db2jcc.jar - add to the JDBC driver CLASSPATH.

Resources

• Java documentation updates for DB2 UDB
  
  
• Considering SQLJ for Your DB2 V8 Java Applications
  
  

About the authors

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Overview of DB2 Java drivers
• Typical host accessibility scenarios
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers