Understand the DB2 UDB JDBC Universal Driver

An insider's guide

Discover the latest step in the evolution of Java™ application development using DB2® Universal Database™ for Linux®, UNIX®, and Windows® (DB2 UDB). Get an inside view of the JDBC Universal Driver and examine common debugging techniques that will help you get to the heart of any problem.

Share:

Anson Kokkat (ansonk@ca.ibm.com), DB2 Advanced Support Team lead, IBM

Photo: Anson KokkatAnson Kokkat works as a software development team lead in the DB2 UDB Advanced Support team. He has been working at IBM since 1999 after graduating from the University of Western Ontario with a degree in Electrical and Computer Engineering Science. Anson has been involved with many aspects of DB2 application development using many different platforms. Areas of specialization include WebSphere and DB2 integration, JDBC, stored procedures, and .NET. Most recently he was selected to present information on the new DB2 Universal Driver at the IDUG conference in Europe.



22 December 2005

Introduction

IBM DB2 e-kit for Database Professionals

Learn how easy it is to get trained and certified for DB2 for Linux, UNIX, and Windows with the IBM DB2 e-kit for Database Professionals. Register now, and expand your skills portfolio, or extend your DBMS vendor support to include DB2.

The most recent step in the evolution of Java development in the DB2 environment is the DB2 UDB JDBC Universal Driver. This new driver offers many advantages and improvements that make it the optimal choice for application development. In this article, gain an understanding of the inner workings of the driver and see how it can fit in with your overall application development plan.

Let's start by comparing two types of drivers that are available:

  • The legacy based CLI driver
  • The new JDBC Universal driver

In the first section, the differences between the drivers are highlighted by concentrating on the following topics:

  • Installation
  • Connection
  • Driver initialization
  • Features
  • Error handling
  • Transaction management

The second section will deal with diagnosing problems and analyzing traces. In order to understand how to do this, you need to know the different parts of an SQLException and how it relates to JDBC. With the new JDBC universal driver, we will try to understand how exactly to take the JCC trace and what is needed to take the JCC trace. Once the trace is taken, we will look deep into what makes up this trace and how to use it to your advantage to get to the bottom of a problem.

Compare the legacy JDBC driver with the new universal JDBC driver

In order to understand how we came to the development of the DB2 Universal Driver, you need to understand how the JDBC specification defines the different types of drivers in the Java world.

  1. Type 1 driver:
    • This type of driver code maps directly to a high level native API. JDBC and ODBC are similar APIs, so this type of driver is usually associated with the JDBC-ODBC bridge.
    • This driver does not have too much context with respect to the DB2 UDB product.
  2. Type 2 driver:
    • A T2 driver has a native component that is part of the driver, but separate from the data access API.
    • The native component and the Java component make up this driver.
    • For DB2 UDB, the DB2 CLI libraries comprise the native component.
  3. Type 3 driver:
    • This is a Java client that communicates using a database independent protocol.
    • Since the protocol is database independent, the advantage of this protocol falls to middleware servers that act as gateways to heterogeneous backend servers.
  4. Type 4 driver:
    • This driver is pure Java and implements the network protocol for a specific data source.
    • The client connects directly to the data source.

As far as DB2 UDB is concerned, you only need to worry about the Type 2, 3, and 4 drivers. Now with this knowledge, you can look at specific information with respect to the Type 2 and Type 4 drivers and examine the advantages of using the Type 4 driver in your application development. Let's look at some specific comparisons of the legacy-based CLI Type 2 driver and the Type 4 Universal JDBC Driver.

Installation

DB2 JDBC support is provided as part of the Java enablement option for DB2 UDB clients and servers. No special installation is required; you just need to make sure that you have the appropriate Java developer kit downloaded for your particular platform. The DB2 Information Center contains detailed information on how to set up your environment for Java on both UNIX and Windows. (See Resources.)

Table 1. Installation comparison
CLI legacy driverUniversal driver
The physical representation of the legacy-based CLI driver is the db2java.zip file.The physical representation of the universal JDBC driver is the db2jcc.jar file.
In the UNIX environment, you can use the Type 2 legacy-based driver by making sure that you have sqllib/java/db2java.zip in your CLASSPATH. The same applies to Windows.In the UNIX environment, you can use the Type 4 universal driver by making sure you have both sqllib/java/db2jcc.jar and db2jcc_license_cu.jar in the CLASSPATH. Windows instructions are the same.
The support that is enabled for this driver is JDBC 2.0 and some JDBC 3.0.Support includes most implementations of JDBC 3.0, as long as you have JDK1.4.x installed as part of your Java package.

Connection

The difference between the two JDBC drivers is shown in the way that they make their connections. The essential function of JDBC is to connect to the database and send SQL statements to the server. It has the capability of processing a result set and sending it to the requestor.

Table 2. Connection comparison
CLI legacy driverUniversal driver
The connection to the database occurs through a native database interface; in this case, DB2 uses CLI. The JDBC layer sits on top of CLI, and CLI is the native component that communicates with the database server.Everything is pure Java, and the way it communicates with the database server is though network communication. DB2 UDB uses distributed relational database architecture (DRDA) to communicate to the server and flow requests to the database server.
Since the legacy-based CLI driver requires common client code, it also requires a DLL/shared object. The DB2 product must be installed in order to use this driver.This is a pure Java driver and so can run independently of what product is installed on the machine where it runs. That is, it can be considered as a separate entity on its own and independent of the DB2 product it is shipped with.

Driver initialization

The way you actually write the code to load the driver changes depends on which driver you want to use. There are two ways the connection can be established. As with all JDBC resources, invoke the connections close method when you are done with the connection.

Table 3. Driver initialization comparison
CLI legacy driverUniversal driver
There are three essential steps needed to load and make this connection:
  1. Import the JDBC core classes (for example, import java.sql*).
  2. Load the JDBC driver Class.forName (COM.ibm.db2.jdbc.app.DB2Driver).
  3. Specify the connection URL: DriverManager getConnection jdbc:db2:coffebk.
The universal driver supports both Type 2 and Type 4 connectivity from a single driver.h network communication. DB2 UDB uses distributed relational database architecture (DRDA) to communicate to the server and flow requests to the database server.

The way you tell whether you are using the Type 2 or Type 4 driver is from the form of the connection. The following indicates that a Type 2 or Type 4 driver is being used:

jdbc:db2//server:port/database
jdbc:db2//server/database

The following means that a Type 2 driver is being used:
jdbc:db2:database

You have the ability to use the Type 3 driver if you want, in which case the syntax for the driver initialization will be: COM.ibm.db2.jdbc.net.DB2Driver.You can toggle between the two drivers' material layers with the connection level that you use.

Features

The introduction of DB2 UDB, Version 8 has enabled Java development to become more versatile and programming independent. Most development efforts are now focused on adding new features, improved memory management, and stability in the new JDBC Universal Driver.

Table 4. Features comparison
CLI legacy driverUniversal driver
This driver needs to specifically have the DB2 UDB product installed, as it relies on the native code of the product.This driver can be considered an independent product. It does not require the product to be installed, and it can be shipped with the many DB2 platforms that the product ships with.
Legacy driver versions are in line with DB2 UDB fix packs and, as such, are only shipped when a fix pack is released.The shipping of the JCC driver is independent of the fix pack. JCC drivers have their own versions and are shipped as needed by the release for any DB2 product. For example, DB2 V8.20 fp9 may ship with JCC driver version 2.3.9, whereas DB2 V8.20 OS/390 PTF UQ72081 may ship with JCC driver version 2.3.11.

Error handling

The two types of JDBC drivers handle errors in very different ways. The development of the error messages are still ongoing and in developing phases for the new driver, but newer versions hold more improved error handling for the universal driver. When you look at a typical JDBC exception, it will consist of an SQLErrorCode, SQLState, and SQLMessage.

Table 5. Error handling comparison
CLI legacy driverUniversal driver
The legacy driver gets its error messages from the DB2 product and essentially spits out the entire error message back to the application.The universal driver does not attempt to recreate pre-existing SQL error codes that were issued by the legacy CLI/JDBC products. The universal driver has its own defined error codes in the range +/-4200 and +/-4299.
Undefined error codes issued by the universal driver are given the error code of -99999.
If an error comes from a DB2 subsystem like DB2 server of underlying DB2 client library, then JCC will just echo that error message.

Transaction management

A transaction is a set of one or more statements that are executed together as a unit of work (UOW). Transactions are used to make sure that all transactions that are part of a UOW are executed or that none of them are executed at all. With respect to the drivers, J2EE specifies simple transaction management.

Table 6. Transaction management
CLI legacy driverUniversal driver
XA support has been enabled for this driver for a very long time.As of V8.20, XA support was enabled for the Type 4 JDBC universal driver.

Diagnose problems and analyze traces

Components of a JDBC trace

Whenever you get any type of exception in DB2, the next step is to find out where that error is coming from. In most cases, to find out the cause of the error you need to take some type of trace that will show the sequence of calls that lead up to the error.

Let's look at the sequence in Java that leads up to an error and examine the mechanics of how an error is handled in a typical Java application.

Figure 1. Java runtime environment
Java runtime environment

If you look at the diagram in Figure 1, you see that the Java runtime environment (JRE) contains the error-handling mechanisms in Java. The JRE can be considered as the actual engine, like in a car, that makes all the components run.

The components can be represented by the actual code which, in Java, will always have try( ) and catch( ) blocks. Whenever the actual code encounters any type of error, it throws an exception, which then goes to the call stack. The call stack passes the exception to the catch( ) block, and that is how it gets returned to the user.

The technical details for allowing a JDBC program to throw an SQLException are to make sure the program accesses the com.ibm.db2.jcc.DB2Diagnosable interface and the com.ibm.db2.jcc.DB2Sqlca class. You can fully qualify all references to them, or you can import them:

import com.ibm.db2.jcc.DB2Diagnosable;
import com.ibm.db2.jcc.DB2Sqlca

Parts of an SQLException

Let's get into the details of the SQLException( ) class and show the parts that make up this class. You will always find the following parts:

SQLException(
Description of the error: null, string
SQL State: null, string
Error code: int value
Next SQLException: null or pointer
)

You usually call next SQLException to return the next exception in the chain. It will return null if there no other error messages to return.

Prerequisite stored procedures

If you are using the Universal JDBC driver and are connecting to OS/390, then you need to make sure that you have a number of prerequisite stored procedures on the host, which will ensure that tracing will work:

  • SQLCOLPRIVILEGES
  • SQLCOLUMNS
  • SQLFOREIGNKEYS
  • SQLGETTYPEINFO
  • SQLPRIMARYKEYS
  • SQLPROCEDURECOLS
  • SQLPROCEDURES
  • SQLSPECIALCOLUMNS
  • SQLSTATISTICS
  • SQLTABLEPRIVILEGES
  • SQLTABLES
  • SQLUDTS
  • SQLCAMESSAGE

These stored procedures are shipped as PTFs for Version 6; you need UQ72081 and UQ72082. For Version 7, the PTF number is defined as UQ72083. If you need specific information on how to install these, refer to the DB2 Information Center for z/OS (see Resources), and you can obtain specific details.

The JCC trace: General information

The use of the JCC driver to take a trace and diagnose problems is not meant for deep diagnosis at the moment. The current set of traces are very malleable and mostly used for preliminary parsing. Future versions of the JCC driver will make the tracing more suitable for problem diagnosis and be more problem oriented. However, there are some key points in a JCC trace that we will discuss later that will help you to narrow down a problem.

There are a couple of different ways to implement the JCC trace, which are discussed in detail in the next couple of sections.

If you have ever had the opportunity to read a DRDA-formatted DB2 trace, then the JCC trace will look familiar. We spit out the buffers of a DRDA trace and put them into the actual JCC trace; after all, JCC uses DRDA to communicate with the server.

How to take a DB2 Universal JDBC driver trace

There are two approaches you can take when trying to trace a JCC problem. Depending on the environment, you can either:

  • Trace it as a standalone JCC application
  • Within WebSphere, embed the JCC trace points

Tracing JCC as a standalone application

When tracing the JCC component as a standalone application, you need to consider the type of connection exists with the DB2 Universal JDBC driver.

  • DataSource interface
    There are two ways to enable the tracing when using the datasource interface for connection to JCC:
    • DB2DataSource > setTraceLevel > default TRACE_ALL
    • -javax.sql.DataSource.setLogWriter > TRACE_ALL only available

    For any of the trace options there are other trace parameters besides the TRACE_ALL property that you can use. Depending on what you want to trace, you can enable the JCC trace to only trace the following properties:

    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_NONE
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_CONNECTION_CALLS
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_STATEMENT_CALLS
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_RESULT_SET_CALLS
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRIVER_CONFIGURATION
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_CONNECTS
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_RESULT_SET_META_DATA
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_PARAMETER_META_DATA
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DIAGNOSTICS
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_SQLJ
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_XA_CALLS (Universal Type 2 Connectivity for DB2 UDB for Linux, UNIX and Windows only)
    • com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL

    If you want to trace more than one specific traceLevel property, then you can use bitwise operator ( | ) to separate the different properties. In general, if you do not know which specific component you want to trace, the best thing is to use the default, which is TRACE_ALL. In fact, in most situations, this is all you need to know. But if you need more detailed tracing in certain JDBC universal driver components, then the bitwise operator will enable you to do this.

    Just a side note, there is also a bitwise operator you can use if you want to trace everything except a certain component. The bitwise operator for all except is ( ~ ).

  • DriverManager
    The second method of taking a trace is to use the DriverManager( ) interface for connections, which can be enabled in one of the following ways:
    • DriverManager.getConnection
      Set the traceLevel property in the info parameter or URL parameter.
    • DriverManager.setLogWriter
      When you use this method to turn on the trace, you can specify the trace destination and turn on the trace. Here is a good example of how to do this:
      Listing 1. Sample code listing using DriverManager.setLogWriter
      // The traceLevel property is established through the URL syntax, 
      // and driver tracing is directed to file "/temp/driverLog.txt" 
      String databaseURL = 
      "jdbc:db2://sysmvs1.stl.ibm.com:5021" + 
      "/sample:traceFile=/temp/driverLog.txt;traceLevel=" + 
      "(com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS " + 
      "| com.ibm.db2.jcc.DB2BaseDataSource.TRACE_CONNECTS);";

    There is an alternate way to take a JCC trace without having to modify the application. If you create a plain text file on the client named DB2JccConfiguration.properties with only only one line of text:
    db2.jcc.override.traceFile=c:\jcc.trc
    and add it to the CLASSPATH, it will enable JCC tracing automatically. This is very useful in cases where you cannot change any of the source code or JCC driver properties (for example, when using a third-party product that internally uses the JCC driver). Refer to the following link in the DB2 Information Center for more details:
    http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.rn.doc/rn/r0012130.htm.

Embedding JCC trace points within a WebSphere trace

If you are running into a DB2 Universal JDBC problem in a WebSphere environment, then you have the ability to embed the JCC trace points within the WebSphere trace. This gives you a good perspective of how the JCC component is playing a role with respect to the WebSphere calls and gives you a good overall picture of what is happening with the application.

Here is the procedure to set up the JCC trace points to be spit out into a WebSphere trace:

  1. Set the trace properties for JDBC in WebSphere Application Server.
    Go to Resources > JDBC Provider > Data Sources > Additional Properties > Custom Properties.
    The property that you need to set is:
    traceLevel(-1 means full trace TRACE_ALL)
  2. Turn on the trace.
    Go to Troubleshooting > Logs and Trace > pick the server > Diagnostic Trace > Trace Specification: RRA=all=enabled:WAS.database=all=enabled
    Notice here that you specify two trace strings separated by ':', one for the WebSphere Application Server resource adaptor, and one for the database (JDBC driver).

When you leave the traceFileName property blank, that should be enough to embed the JCC trace points inside the WebSphere trace automatically. You can enable and disable this trace dynamically, which should help when trying to narrow down a problem.

JDBC Universal Driver error codes

There is only a handful of DB2 Universal Driver error codes issued by the JCC driver. If the error code is not yet defined by the Universal Driver, it will echo a -99999 error code. Here is a reference to the currently available error codes for the DB2 Universal JDBC driver:

Table 7. Error codes
CodeDescription
4200An application that was in a global transaction in an XA environment issued an invalid commit or rollback.
4498A failover or failback occurred, and the transaction failed.
4499A fatal error occurred that resulted in a disconnect.
99999The DB2 Universal JDBC Driver issued an error that does not yet have an error code.

There are approximately 2000 generic error codes defined with the -99999 generic error code. The next phase of the JCC product is to define these error codes with an SQLSTATE and SQLCODE.

Components of a JCC trace

Whenever you run into any type of problem using the JCC driver, the typical response for further diagnosis is to take a JCC trace. Instructions for taking the JCC trace are given above. Now let's analyze by picking apart a JCC trace to see how to get to the bottom of a problem by analyzing the trace and figuring out where the error is coming from.

Figure 2. JCC trace
JCC trace

Let's now break up the parts of a trace and figure what will be useful when you need to look at the components of this diagnostic tool. Having a look at the trace header, you can find some vital information that will be useful for understanding the environment. The numbers below indicate the numbers in Figure 2.

1. DB2 Universal JDBC driver version being used
The actual driver version is independent of the fix pack version; however, there is a detailed mapping on the Java application development support page that shows which JCC driver version is shipped with each DB2 UDB fixpack. (See Resources.)

Another way to find out the JCC driver version being used is to issue the command db2jcc -version from the command line, and it will show you the exact version you are currently running.

2. JDK level
This shows you which Java development kit that is being used with this JCC driver. Try to keep this current with the corresponding fix pack that is being used.

Other important information given from the trace header includes:

  • The operating system level
  • Path information

The best way to get the latest version of the DB2 Universal JDBC driver is to download the latest fix pack for DB2 UDB for Linux, Unix, and Windows.

The reason behind having a different versioning system for JDBC drivers and the corresponding fix packs is so that there can be one driver that ships across all DB2 platforms, including zSeries®, iSeries™, and so on. This driver is consistent across all DB2 platforms.

Let us now have a look at the body of a JCC trace and try to piece together some key elements.

3. Trace tags
You can always determine if you are using the Type 4 flavor or the Type 2 flavor of the universal driver if you look at the tags in the JCC trace:

  • [ibm][db2][jcc][t4] = indicates type 4 version of the driver is being used
  • [ibm][db2][jcc][t2] = indicates type 2 version of the driver is being used

4. DRDA buffer
Since the JCC specification is built on top of the DRDA protocol, we embed the DRDA buffers in the JCC trace. The buffers contain items such as the PreparedStatement objects or ResultSet objects. If you are familiar with parsing out DRDA buffers that you commonly see in a DB2 trace, then the look and feel of the DRDA buffers in a JCC trace will look very familiar to you. If you are not comfortable reviewing DRDA information, the key thing to look for is the SQL statement that you are trying to execute. It should be embedded within the buffer exactly the way that the DB2 Universal Driver sends it to the server for processing.

5. Methods being used
If you know the particular Java method that is causing the problem, or if you want to see how a particular method is being used in the trace, then you will find it in the JCC trace.

If you know a particular statement or method that is causing the problem, you can always search for it in the JCC trace and then search above and below it to find out any suspicious behavior or error messages that may lead to a clue as to what is going on.

If you are unsure of the error, a good place to start off is the DB2 UDB Technical Support site. (See Resources.)

Let's now have a look at an example of a problematic trace that shows a -4499 error, one of the error codes defined by the DB2 Universal JDBC driver.

Usually when you run into any type of problem with the Universal JDBC driver, you will report the problem in the form of some type of exception.

Figure 3. Trace example
Trace example

You can see in the above trace the -4499 return code. The communication error is also displayed in the exception, and you can see that in this particular case this is what is being returned back to the application.

A good technique is to search above and below this exception to get a sense of what is going on in the actual application. Search to find out if this is a defect with the driver, and if so, try to use the latest version of the JCC driver, as the problem may most likely already be fixed.

Conclusion

By taking a look at the differences between the CLI-based legacy JDBC driver and the new JDBC Universal driver, we see that there are many advantages to using a pure Java type 4 driver. A further understanding of the traces used with the Universal JDBC driver and what to search for when taking a trace will help you to solve any problems you may encounter when using the JCC driver. Overall, a deeper understanding of the DB2 UDB JDBC Universal Driver will go a long way to expand your capability to work with the next phases of JDBC application development in the DB2 environment.

Resources

Learn

Get products and technologies

  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
  • Download a free trial version of DB2 Universal Database Enterprise Server Edition.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Java technology
ArticleID=101124
ArticleTitle=Understand the DB2 UDB JDBC Universal Driver
publish-date=12222005