Both answers are correct, but only have the links to were the answers are.

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. Install Asterisknow From Usb. 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.

​To find the version of the IBM DB2 JDBC driver and client installed, do the following: To determine the IBM client version installed: Use the DB2level command to know the version of a DB2 Client. This is an example of executing DB2level on a Windows OS with a DB2 Client instance. If you install the IBM Data Server Driver for JDBC and SQLJ on a Windows 64-bit operating system, you cannot use IBM Data Server Driver for JDBC and SQLJ type 2 connectivity to connect to a DB2 Database for Linux, UNIX, and Windows instance from a 32-bit Java application. Follow these steps to install the IBM Data.

• 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.) Table 1. Installation comparison 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. Connection comparison 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. Driver initialization comparison CLI legacy driver Universal driver There are three essential steps needed to load and make this connection: • Import the JDBC core classes (for example, import java.sql*). • Load the JDBC driver Class.forName (COM.ibm.db2.jdbc.app.DB2Driver). • 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.

Features comparison 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.

Error handling comparison 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. Cfm56 7b Maintenance Manual. 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. 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. Java runtime environment. If you look at the diagram in, 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 ), 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.

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.

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.) 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. 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. 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. 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.) 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. 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. Related topics • Now you can use DB2 for free. Download, 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. • Build your next development project with, available for download directly from developerWorks.

• ' (developerWorks, June 2005): This article covers additional information on how to perform a JDBC trace when the CLI layer is missing. • The covers all the details on DB2 UDB, including how to set up your environment for Java. • The contains details about the prerequisite stored procedures for the z/OS platform.

•: Expand your skills on Java and DB2.