We’re giving away 1,500 DJI Tello drones. Enter to win ›
By Anson Kokkat | Published December 22, 2005 - Updated December 21, 2005
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:
In the first section, the differences between the drivers are highlighted by concentrating on the following topics:
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.
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.
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.
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 Related topics.)
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.
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.
DriverManager getConnection jdbc:db2:coffebk
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
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.
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.
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.
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.
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:
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:
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.
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:
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 Related topics), and you can obtain specific details.
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.
There are two approaches you can take when trying to trace a JCC problem. Depending on the environment, you can either:
When tracing the JCC component as a standalone application, you need to consider the type of connection exists with the DB2 Universal JDBC driver.
// The traceLevel property is established through the URL syntax,
// and driver tracing is directed to file "/temp/driverLog.txt"
String databaseURL =
"(com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS " +
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:
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.
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:
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.
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.
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 Related topics.)
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 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:
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 Related topics.)
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.
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.
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.
April 24, 2018
November 3, 2018
Back to top