Level: Introductory Paul ZikopoulosIBM Canada
Grant HutchisonIBM Canada
02 Jul 2003 A lot has changed in DB2 UDB V8 for Java support. This article pulls it all together and answers common questions, too.
© 2003 International Business Machines Corporation.
All rights reserved.
Note: This is a Version 8.1 update to the article An Overview of DB2 and Java Database Connectivity (JDBC).
Introduction
Relational databases play a key role in most applications that need a persistent data store, but they are not the driving force behind the development and selection of various programming models, frameworks and architectures. Application development priorities are usually driven by factors outside of the scope of relational database management systems (RDBMS), such as whether J2EE or .NET is the preferred programming model for a particular shop.
In recognition of the fact that you may make a commitment to one programming architecture and someone else might make a commitment to a different one, the DB2® Universal DatabaseTM strategy is to provide the best integration with any choice you make.
This article focuses on the particular topic of JDBC and SQLJ support in a DB2 UDB environment.
DB2 UDB application development methodologies
While this may be a bit of oversimplification, the current application development landscape is dominated by the following distinct types of programming:
- Developing applications based on the J2EE programming model using Java.
- Developing Windows®, Web, WinForm, and .Net-based applications based on the current Microsoft® API set (ADO and ADO.net).
- Developing applications using the C/C++ programming languages and the ODBC/CLI interface.
- Developing applications in the Borland Delphi/Kylix framework.
- Developing applications in a Linux-focused open-source framework (Perl, Python, PHP, and so on).
- Developing cross-platform business logic using Web services.
The great thing about DB2 UDB is that it supports all of these!
For JavaTM programmers, DB2 offers two application programming interfaces (APIs): JDBC and SQLJ. JDBC is a mandatory component of the Java programming language as defined in the Java 2, Standard Edition (J2SE) specification. To enable JDBC applications for DB2, an implementation of the various Java classes and interfaces, as defined in the standard, are required. This implementation is known as a JDBC driver. DB2 UDB for Linux, UNIX®, and Windows offers a complete set of JDBC drivers for this purpose. The JDBC drivers are categorized as the legacy/CLI drivers and the new Universal JDBC Drivers.
DB2 UDB's Java support includes support for JDBC, a vendor-neutral dynamic SQL interface that provides data access to your application through standardized Java methods. JDBC is similar to DB2 CLI in that you do not have to pre-compile the application code or bind packages to a DB2 database. As a vendor-neutral standard, JDBC applications offer increased portability -- a required benefit in today's heterogeneous business infrastructure. During the execution of a JDBC application the driver will validate SQL statements against the currently connected DB2 database server. Any problems during access will be reported to the application as a Java exception along with the corresponding SQLSTATE and SQLCODE.
SQLJ is a standard development model for data access from Java applications. The SQLJ API is defined within the SQL 1999 specification. The new Universal JDBC Driver provides support for both JDBC and SQLJ APIs in a single implementation. JDBC and SQLJ can interoperate in the same application. SQLJ provides the unique ability to develop using static SQL statements and control access at the DB2 package level.
Java access methods to DB2
DB2 has rich support for the Java programming environment.
You can access DB2 data by putting the Java class into a module in one of the following ways:
- DB2 Server
- Stored procedures (JDBC or SQLJ)
- SQL functions or user-defined functions (JDBC or SQLJ)
- Browser
- Applets based on JDBC (JDBC)
- J2EE Application Servers (such as WebSphere® Application Server)
- Java ServerPages (JSPs) (JDBC)
- Servlets (SQLJ or JDBC)
- Enterprise JavaBeans (EJBs) (SQLJ or JDBC)
You may be asking, "How do I get started?"
First, install the DB2 UDB Application Development client and connect to a DB2 UDB database on UNIX, Linux, Windows, z/OSTM, or OS/400®. If you plan on accessing DB2 UDB for z/OS or DB2 UDB for OS/400, use the DB2 ConnectTM product for your development environment. The DB2 UDB Application Development client contains all of the necessary drivers to develop C, COBOL, and Java applications.
If you plan to develop a multi-tier application using a J2EE application sever, such as WebSphere Application Server, each of the application servers will require the DB2 UDB Runtime Client or the DB2 UDB Application Development Client.
After you have developed and tested your application, the next step is to decide on the deployment topology. There are various JDBC driver architecture types defined within the specification. Let's look at the various types of drivers, Java Development Kit (JDK) versions, and Java Runtime Environments (JRE) that are available.
Demystifying JDBC drivers
A common source of confusion with Java is the fact that there are different versions of JDBC and different types of JDBC drivers that can be used by different versions of Java. What's more, new Java specifications are always being developed as the capabilities are always expanding through the Java Community Process (http://www.jcp.org).
JDBC versions
DB2 Universal Database V8.1 supports the JDBC v2.1 specification and portions of JDBC 3.0. Plans are underway to fully support the JDBC 3.0 requirements with the Universal JDBC Driver.
JDBC drivers are certified as a component of a J2EE solution. The results of driver certifications are always posted on the Sun compatibility Web site.
JDBC driver architectures
JDBC driver architectures are divided into four types.
JDBC Type 1 driver
A JDBC Type 1 driver is based on a JDBC-ODBC bridge. Therefore an ODBC driver can be used in combination with this JDBC driver (provided by Sun). IBM does not provide a Type 1 driver, and it is not a recommended environment. Figure 1 shows an example of a JDBC Type 1 driver:
Figure 1. JDBC Type 1 driver
JDBC Type 2 driver
A JDBC Type 2 driver relies on an OS-specific library (shared library) to communicate with a RDBMS. The application will load the JDBC driver and the driver will use the shared library to communicate with the DB2 server. DB2 UDB for Linux, UNIX®, and Windows® v8.1 provides two different type 2 drivers:
- The legacy/CLI JDBC driver is provided in the file
db2java.zip. The implementation package name is COM.ibm.db2.jdbc.app.DB2Driver. This driver has been used for the current J2EE certifications. The nickname "app driver" comes from the notion that this driver will perform a native connect through a local DB2 UDB client to a remote database, and from its package name.
-
The Universal JDBC driver is provided in the file
db2jcc.jar. The implementation package name is com.ibm.db2.jcc.DB2Driver. This driver is new to DB2 UDB for Linux, UNIX, and Windows v8.1. In the initial implementation (v8.1), this driver was for direct Java connectivity to DB2 servers using a type 4 driver architecture. In DB2 v8.1.2, you can use this driver in a type 2 architecture. One of the key reasons for using the driver in a type 2 architecture is for local application performance and for distributed transaction support. The Universal JDBC Type 2 driver supports distributed transactions using com.ibm.db2.jcc.DB2XADataSource and connection pooling using com.ibm.db2.jcc.DB2ConnectionPoolDataSource.
You can use JDBC Type 2 drivers to support JDBC applications. Both of these type 2 drivers are supported with WebSphere Application Server. Note that the legacy/CLI type 2 driver will not be enhanced in future versions.
Figure 2 shows a typical topology of an application using a JDBC Type 2 driver:
Figure 2. An application that uses a JDBC Type 2 driver
JDBC Type 3 driver
The JDBC Type 3 driver is a pure Java implementation that must communicate with a DB2 JDBC Applet Server to access DB2 data. This driver was designed to enable Java applets for access to DB2 data sources. In this scenario, shown in Figure 3, the application talks to another machine where a DB2 client has been installed.
Figure 3. A typical application scenario using a Type 3 JDBC driver
The JDBC type 3 driver is often referred to as the "net driver", named after its package name: COM.ibm.db2.jdbc.net. DB2 Version 8.1 supports the net driver, and you can use it with JDBC applications.
The type 3 driver requires that the db2java.zip driver is always at the same maintenance level as the DB2 Applet Server. This is not a problem if the driver is used within applets because the browser will download the appropriate db2java.zip file during application execution. Many customers use the type 3 driver instead of the type 2 driver to avoid the required DB2 client installation and the required DB2 CATALOG DATABASE commands to create the database directory information required for type 2 driver connections using the Legacy/CLI driver. Currently, the WebSphere Application Server and other J2EE servers do not support the IBM type 3 driver, because that driver does not support distributed transactions (JTA). The type 3 driver will not be enhanced in future versions.
We are encouraging the use of our Universal JDBC type 4 driver as a replacement for the type 3 driver.
JDBC Type 4 driver
A type 4 driver is a Java-only JDBC driver that connects directly to the database server. DB2 UDB for Linux, UNIX, and Windows v8.1 introduced a type 4 driver known as the "Universal JDBC driver". The Universal JDBC driver is provided in the file db2jcc.jar. The implementation package name is com.ibm.db2.jcc.DB2Driver.
Notice that the Universal type 2 and Universal type 4 drivers share the same implementation class name. There are two ways to distinguish which driver DB2 will instantiate internally:
- Use a connection property to determine if the shared library will be used for connectivity (type 2) or if the driver will initiate a direct connection from the Java application (type 4).
- Use a different connection URL pattern to indicate whether you want type 2 or type 4 behavior.
An example of a type 4 URL pattern: the string "jdbc:db2://server1:50000/sample" tells the JDBC driver to connect directly from the Java application to the DB2 server to a database called "sample" within the DB2 instance configured on the DB2 server (hostname is server1) that is listening on port 50000.
An example of a type 2 URL pattern: the string "jdbc:db2:sample". Information about the DB2 server ("server1") and port ("50000") s available the DB2 client catalog directory.
The Universal JDBC driver is an abstract JDBC processor that is independent of driver-type connectivity or target platform. The universal JDBC drive is an architecture-neutral JDBC driver for distributed and local DB2 UDB access. Because the Universal JDBC driver is independent of any particular JDBC driver-type connectivity or target platform, it supports both all-Java connectivity (type 4 driver) or JNI-based connectivity (type 2 driver) in a single driver instance to DB2 UDB. This driver can be used for standalone Java applications or multi-tier applications.
Important: As of DB2 UDB v8.1.2 the Universal JDBC driver requires a license JAR file to be in the CLASSPATH along with the db2jcc.jar file. Here are the required license JAR files:
- For CloudscapeTM Network Server V5.1:
db2jcc_license_c.jar
- For DB2 UDB V8 for Linux, UNIX, and Windows servers:
db2jcc_license_cu.jar
- For DB2 UDB for iSeries® and z/OS servers (provided with DB2 Connect and DB2 Enterprise Server Edition):
db2jcc_license_cisuz.jar
The Universal Driver can query the database metadata catalog and retrieve server error message text by using the prerequisite stored procedures installed on the target server:
- For UDB v8 on Linux, UNIX, and Windows, these stored procedures are pre-installed.
- For DB2 UDB for OS/390® v6, you must install PTFs UQ72081 and UQ72082.
- For DB2 UDB for OS/390 and z/OS v7, you must install PTF UQ72083. The upcoming DB2 UDB v8 on z/OS will have the required stored procedures pre-installed.
- DB2 UDB for iSeries V5R1 requires PTFS SI06308, SI06300, SI06301, SI06302, SI06305, SI06307, and SI05872.
- DB2 UDB for iSeries V5R2 requires PTFs SI06541, SI06796, SI07557, SI07564, SI07565, SI07566, and SI07567.
DB2 UDB for iSeries V5R3 will have the required stored procedures pre-installed.
A Type 4 JDBC driver implementation is shown in Figure 4. Note that the Universal JDBC/SQLJ driver (Type 4 and Type 2) connections to DB2 for z/OS and OS/390 from Java applications or applets on Linux, UNIX, and Windows require the appropriate license file (db2jcc_license_cisuz.jar). This license file is provided with DB2 Connect products.
Figure 4. Type 4 JDBC driver implementation
Java Development Kits (JDKs) and Java Run Time Environments (JREs)
Each version of JDBC has an associated JDK/JRE.
- The JDBC 2.0 and JDBC 2.1 APIs are divided into two packages:
- JDBC 2.0 and JDBC 2.1 core APIs are supported by J2SE (Java 2, Standard Edition, implemented in JDK 1.2 and above), and the JDBC 2.0 optional package is supported by J2EE 1.3.
- JDBC 3.0 was finalized in May 2002, and incorporated in J2SE 1.4 and J2EE 1.4.
Many customers develop and deploy Java applications using a 1.3.1 environment. This is the currently recommended environment for DB2 Java applications. The Universal JDBC driver requires a 1.3.1 environment. Note that there are plans underway to support the JDBC 3.0 APIs with the Universal JDBC driver within a JDK 1.4 environment.
Frequently asked questions
Q1: Where can I get the DB2 UDB JDBC Type 4 driver?
A1: The driver is provided with the DB2 UDB client, or you can download trial versions of DB2 UDB Express or DB2 UDB Personal Developers Edition.
Q2: Does DB2 UDB V8 support updatable scrollable cursors?
A2: Updatable scrollable cursors are supported using the Universal JDBC drivers in DB2 UDB V8.1. If you are using DB2 UDB for Linux, UNIX, and Windows v7.x, then you can use the legacy/CLI drivers and the setCursorName APIs to simulate updatable scrollable cursors. We recommend that you migrate to the Universal JDBC driver.
Q3: Can I use the DB2 JDBC type 4 driver to connect to DB2 UDB for z/OS and OS/390?
A3: Yes, you will require a DB2 Connect license.
Q4: Which JDBC driver is the fastest?
A4: The answer varies by configuration. If the Java application is executing on the same machine as the DB2 server, then the best performance is with the Universal type 2 driver because TCP/IP overhead can be avoided and therefore throughput improves. When the DB2 server is on a different machine from the Java application, the performance of the Universal type 4 and type 2 drivers is fairly similar. You should always consider writing more efficient SQL statements, or use SQLJ or stored procedures if you are attempting to improve overall Java application performance.
Q5: Why should I consider SQLJ instead of JDBC and how do I get started?
A5: Read the DB2 Developer Domain article entitled Considering SQLJ for Your DB2 v8.1 Java Applications.
This document contains proprietary information of IBM. The information contained in this publication does not include any product warranties, and any statements provided in this document should not be interpreted as such.
About the authors  | 
| |
Paul C. Zikopoulos is an IDUG keynote and award-winning speaker from the IBM Data Management software group. He has more than seven years of experience with DB2 and has written numerous magazine articles about it. Paul co-authored the books: DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at paulz_ibm@msn.com. |
| 
| |
Grant Hutchison leads the DB2 and WebSphere integration efforts within IBM as the technical manager of the DB2 integration center in the Toronto Lab. He has 12 years of experience with DB2 as a developer, service analyst, and consultant. He has recently completed his graduate degree in software engineering at the University of Waterloo. |
Rate this page
| 
