Ibm Db2 Universal Jdbc Driver
At com.ibm.db2.jcc.b.id.d(id.java:1385). For more details regarding the differences between the DB2 Universal JDBC Driver and other DB2 JDBC drivers please see the. Feb 24, 2005 i m using IBM DB2 Universal database but the driver installed with it is not present in the jar file. So i cannot connect with the database. I m using windows XP. And i have to connect this database with IBM Mobile Application Builder. Anyone who can tell me that how can i connect MAB with DB2 UDB. The z/OS operating system requires that you configure the DB2 Universal JDBC Driver and your 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 your applications. Jun 11, 2013 I am posting to this thread because it is mentioned in IBM's list of JDBC driver versions (document reference 1363866). That document is actively maintained by IBM, and I reference it often, but it is missing an important piece of information that would greatly assist DB2 administrators.
Where do I download JDBC drivers for DB2 that are compatible with JDK 1.5? They seem to be very elusive and I hit many dead-ends at IBM's website. I managed to find versions of the driver bundled with some tools such as IBM Data Studio. Unfortunately, these versions either target a JVM too modern (JDK 1.6, leading to class version errors) or too ancient (JDK 1.2, leading to known bugs when run on JDK 1.5).
Adam PaynterAdam Paynter4 Answers
I know its late but i recently ran into this situation. After wasting entire day I finally found the solution. I am suprised that I got this info on oracle's website whereas this seems nowhere to be found on IBM's website.
If you want to use JDBC drivers for DB2 that are compatible with JDK 1.5 or 1.4 , you need to use the jar db2jcc.jar, which is available in SQLLIB/java/ folder of your db2 installation.
Right here: http://jt400.sourceforge.net/
This is what I use for that exact purpose.
EDIT: Usage Examples (minus exceptions):
Hope that helps!
BenColeBenColeyou can download and install db2client and looking for - db2jcc.jar - db2jcc_license_cisuz.jar - db2jcc_license_cu.jar - and etc.at C:Program Files (x86)IBMSQLLIBjava
Fuangwith S.Fuangwith S.Not the answer you're looking for? Browse other questions tagged jdbcdb2 or ask your own question.
Introduction
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.
- 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.
- 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.
- 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.
- 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 Related topics.)
| CLI legacy driver | Universal 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.
| CLI legacy driver | Universal 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.
| CLI legacy driver | Universal driver |
|---|---|
There are three essential steps needed to load and make this connection:
| 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: |
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.
| CLI legacy driver | Universal 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.
| CLI legacy driver | Universal 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.
| CLI legacy driver | Universal 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
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:
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:
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 Related topics), 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 availableFor 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_NONEcom.ibm.db2.jcc.DB2BaseDataSource.TRACE_CONNECTION_CALLScom.ibm.db2.jcc.DB2BaseDataSource.TRACE_STATEMENT_CALLScom.ibm.db2.jcc.DB2BaseDataSource.TRACE_RESULT_SET_CALLScom.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRIVER_CONFIGURATIONcom.ibm.db2.jcc.DB2BaseDataSource.TRACE_CONNECTScom.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWScom.ibm.db2.jcc.DB2BaseDataSource.TRACE_RESULT_SET_META_DATAcom.ibm.db2.jcc.DB2BaseDataSource.TRACE_PARAMETER_META_DATAcom.ibm.db2.jcc.DB2BaseDataSource.TRACE_DIAGNOSTICScom.ibm.db2.jcc.DB2BaseDataSource.TRACE_SQLJcom.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_ALLIf 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: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:Listing 1. Sample code listing using DriverManager.setLogWriter
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:
- 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) - 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:
| Code | Description |
|---|---|
| 4200 | An application that was in a global transaction in an XA environment issued an invalid commit or rollback. |
| 4498 | A failover or failback occurred, and the transaction failed. |
| 4499 | A fatal error occurred that resulted in a disconnect. |
| 99999 | The 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
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 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 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.
Figure 3. 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.
Db2 Jdbc Driver Versions
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.
Ibm Db2 Driver Odbc
Conclusion
Ibm Db2 Universal Jdbc Driver Jar
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.