Luckycanna

Introduction

• Valid Jdbc Driver Type
• Jdbc Driver Jar Download
• Jdbc Driver Download For Oracle
• Db2 Jdbc Type 2 Driver Download

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:

Which JDBC driver (JCC) version. DB2 JDBC Driver Versions and. Download the driver you need by visiting the DB2 Fix Packs by Version page or the IBM Data.

• 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 Related topics.)

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.

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.

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.

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.

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.

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:
  

  Listing 1. Sample code listing using DriverManager.setLogWriter

  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:

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

Valid Jdbc Driver Type

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.

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.

A JDBC driver is a software component enabling a Java application to interact with a database.^[1] JDBC drivers are analogous to ODBC drivers, ADO.NET data providers, and OLE DB providers.

To connect with individual databases, JDBC (the Java Database Connectivity API) requires drivers for each database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.

JDBC technology drivers fit into one of four categories.^[2]

1. JDBC-ODBC bridge
2. Native-API driver
3. Network-Protocol driver (Middleware driver)
4. Database-Protocol driver (Pure Java driver) or thin driver.

• 1Type 1 driver – JDBC-ODBC bridge
• 2Type 2 driver – Native-API driver
• 3Type 3 driver – Network-Protocol driver (middleware driver)
• 4Type 4 driver – Database-Protocol driver/Thin Driver(Pure Java driver)

Type 1 driver – JDBC-ODBC bridge[edit]

The JDBC type 1 driver, also known as the JDBC-ODBC bridge, is a database driver implementation that employs the ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls.

The driver is platform-dependent as it makes use of ODBC which in turn depends on native libraries of the underlying operating system the JVM is running upon. Also, use of this driver leads to other installation dependencies; for example, ODBC must be installed on the computer having the driver and the database must support an ODBC driver. The use of this driver is discouraged if the alternative of a pure-Java driver is available. The other implication is that any application using a type 1 driver is non-portable given the binding between the driver and platform. This technology isn't suitable for a high-transaction environment. Type 1 drivers also don't support the complete Java command set and are limited by the functionality of the ODBC driver.

Sun (now Oracle) provided a JDBC-ODBC Bridge driver: sun.jdbc.odbc.JdbcOdbcDriver. This driver is native code and not Java, and is closed source. Sun's/Oracle's JDBC-ODBC Bridge was removed in Java 8 (other vendors' are available).^[3][4][5][6]

If a driver has been written so that loading it causes an instance to be created and also calls DriverManager.registerDriver with that instance as the parameter (as it should do), then it is in the DriverManager's list of drivers and available for creating a connection.

It may sometimes be the case that more than one JDBC driver is capable of connecting to a given URL. For example, when connecting to a given remote database, it might be possible to use a JDBC-ODBC bridge driver, a JDBC-to-generic-network-protocol driver, or a driver supplied by the database vendor. In such cases, the order in which the drivers are tested is significant because the DriverManager will use the first driver it finds that can successfully connect to the given URL.

First the DriverManager tries to use each driver in the order it was registered. (The drivers listed in jdbc.drivers are always registered first.) It will skip any drivers that are untrusted code unless they have been loaded from the same source as the code that is trying to open the connection.

It tests the drivers by calling the method Driver.connect on each one in turn, passing them the URL that the user originally passed to the method DriverManager.getConnection. The first driver that recognizes the URL makes the connection.

Advantages[edit]

• Almost any database for which an ODBC driver is installed can be accessed, and data can be retrieved.

Disadvantages[edit]

• Performance overhead since the calls have to go through the JDBC( java database connectivity) bridge to the ODBC(open database connectivity)driver, then to the native database connectivity interface (thus may be slower than other types of drivers).
• The ODBC driver needs to be installed on the client machine.
• Not suitable for applets, because the ODBC driver needs to be installed on the client.
• Specific ODBC drivers are not always available on all platforms; hence, portability of this driver is limited.
• No support from JDK 1.8 (Java 8).

Type 2 driver – Native-API driver[edit]

The JDBC type 2 driver, also known as the Native-API driver, is a database driver implementation that uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API. For example: Oracle OCI driver is a type 2 driver.

Advantages[edit]

• As there is no implementation of JDBC-ODBC bridge, it may be considerably faster than a Type 1 driver.

Disadvantages[edit]

• The vendor client library needs to be installed on the client machine.
• Not all databases have a client-side library.
• This driver is platform dependent.
• This driver supports all Java applications except applets.

Type 3 driver – Network-Protocol driver (middleware driver)[edit]

The JDBC type 3 driver, also known as the Pure Java driver for database middleware,^[7] is a database driver implementation which makes use of a middle tier between the calling program and the database. The middle-tier (application server) converts JDBC calls directly or indirectly into a vendor-specific database protocol.

This differs from the type 4 driver in that the protocol conversion logic resides not at the client, but in the middle-tier. Like type 4 drivers, the type 3 driver is written entirely in Java.

The same client-side JDBC driver may be used for multiple databases. It depends on the number of databases the middleware has been configured to support. The type 3 driver is platform-independent as the platform-related differences are taken care of by the middleware. Also, making use of the middleware provides additional advantages of security and firewall access.

Functions[edit]

• Sends JDBC API calls to a middle-tier net server that translates the calls into the DBMS-specific network protocol.The translated calls are then sent to a particular DBMS.
• Follows a three-tier communication approach.
• Can interface to multiple databases – Not vendor specific.
• The JDBC Client driver written in java, communicates with a middleware-net-server using a database independent protocol, and then this net server translates this request into database commands for that database.
• Thus the client driver to middleware communication is database independent.

Advantages[edit]

• Since the communication between client and the middleware server is database independent, there is no need for the database vendor library on the client. The client need not be changed for a new database.
• The middleware server (which can be a full-fledged J2EE Application server) can provide typical middleware services like caching (of connections, query results, etc.), load balancing, logging, and auditing.
• A single driver can handle any database, provided the middleware supports it.
• E.g.: IDA Server

Disadvantages[edit]

• Requires database-specific coding to be done in the middle tier.
• The middle ware layer added may result in additional latency, but is typically overcome by using better middle ware services..

Type 4 driver – Database-Protocol driver/Thin Driver(Pure Java driver)[edit]

The JDBC type 4 driver, also known as the Direct to Database Pure Java Driver, is a database driver implementation that converts JDBC calls directly into a vendor-specific database protocol.

Written completely in Java, type 4 drivers are thus platform independent. They install inside the Java Virtual Machine of the client. This provides better performance than the type 1 and type 2 drivers as it does not have the overhead of conversion of calls into ODBC or database API calls. Unlike the type 3 drivers, it does not need associated software to work.

As the database protocol is vendor specific, the JDBC client requires separate drivers, usually vendor supplied, to connect to different types of databases.

Advantages[edit]

• Completely implemented in Java to achieve platform independence.
• These drivers don't translate the requests into an intermediary format (such as ODBC).
• The client application connects directly to the database server. No translation or middleware layers are used, improving performance.
• The JVM can manage all aspects of the application-to-database connection; this can facilitate debugging.

Disadvantages[edit]

• Drivers are database specific, as different database vendors use widely different (and usually proprietary) network protocols.

List of JDBC drivers[edit]

Jdbc Driver Jar Download

See also[edit]

• Open Database Connectivity (ODBC)
• XQJ (XQuery API for Java)

Jdbc Driver Download For Oracle

References[edit]

1. ^'Java SE Technologies - Database'
2. ^Sun JDBC Overview
3. ^Oracle. 'JDBC-ODBC Bridge'. https://docs.oracle.com/. Retrieved 27 June 2015.External link in |website= (help)
4. ^'Life after sun.jdbc.odbc.JdbcOdbcDriver'. Universal Data Access Technology Blog. OpenLink Software. 2015-06-04. Retrieved 2016-11-18. JDBC-to-ODBC Bridge, in both Type 1 and Type 3 forms, has been available and regularly updated since its original release for JDBC 1.
5. ^https://www.progress.com/connectors/sequelink
6. ^http://www.easysoft.com/blog/java-8.html
7. ^http://docs.oracle.com/cd/E19509-01/820-5069/ggzci/index.html

Db2 Jdbc Type 2 Driver Download