An overview of DB2 and Java Database Connectivity (JDBC)

Are you confused about JDBC support in DB2®? Author Paul Zikopoulos pulls it all together and answers common questions, too.

Paul C. Zikopoulos (paulz_ibm@msn.com), Senior Specialist, Database Competitive Technology, IBM

Paul C. ZikopoulosPaul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than nine years of experience with DB2 and has written numerous magazine articles and books about it. Paul has co-authored the following books: DB2 Version 8: The Official Guide, 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). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: paulz_ibm@msn.com.



Farzana Anwar (fanwar@ca.ibm.com), Information Developer, IBM

Farzana Anwar is a member of the DB2 for Linux, UNIX, and Windows team at the IBM Canada Lab in Markham, Ontario. Since 2004, she has held various roles across IBM, in the areas of application development, system verification test, technical support, and information development. She has a bachelor's degree in computer science from Acadia University and a master's degree in information systems from Athabasca University. In her current role as a technical writer for DB2 for Linux, UNIX, and Windows, she focuses on making DB2 products more consumable for users.



01 March 2002

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.

IBM DB2 e-kit for Database Professionals

Learn how easy it is to get trained and certified for DB2 for Linux®, UNIX®, and Windows® with the IBM DB2 e-kit for Database Professionals. Register now, and expand your skills portfolio, or extend your DBMS vendor support to include DB2.

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 strategy is to provide the best integration with any choice you make.

This article focuses on the particular topic of JDBC support in a DB2 environment.


The big three — Application development environments and DB2

While this may be a bit of oversimplification, the current application development landscape is dominated by three distinct types of programming:

  • Developing applications based on the J2EE programming model using Java™.
  • Developing Windows and .NET-based applications in Microsoft® environments.
  • Developing UNIX, Linux, and Windows applications using the C programming language.

For Java programmers, DB2 offers two application programming interfaces (APIs): JDBC and SQLj. To enable the JDBC programming interface, DB2 7.2 offers different JDBC drivers. SQLj is an API developed by a consortium of companies and provides both a simpler programming model and an ability to create static SQL. While SQLj usage is on the rise, the tools for SQLj programming are not as widespread as those for JDBC.

Both vendors and in-house programmers developing native (as opposed to Web) applications for UNIX, Linux, and Windows platforms commonly use the C programming language and one of the following DB2 APIs:

  • Open Database Connectivity (ODBC)
  • DB2 CLI (Call-Level Interface)
  • Embedded SQL

Embedded SQL is an interface familiar to mainframe programmers, but in the client-server world, both DB2 CLI and ODBC are much more widely used. DB2 CLI and ODBC are syntactically and grammatically almost identical, with DB2 CLI being a slight superset of ODBC.


Java access methods to DB2

DB2 has a rich support for the J2EE programming model, allowing applications developers to access DB2 data from a variety of methods. You can access DB2 data by putting the Java program into a module in one of the following ways:

  • Java stored procedures
  • Java user-defined functions (UDFs)
  • Java applets, executing within a Java-enabled browser

Because of the size of the applets and some functionality restrictions, many application designs cannot be fully implemented as applets. The DB2 Application Development client provides the full JDBC and SQLJ interfaces.

For a large-scale cluster of WebSphere® Application Servers, each of the WebSphere servers could install a DB2 Runtime Client, which includes the JDBC driver.

DB2'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 precompile or bind a JDBC program. As a vendor-neutral standard, JDBC applications offer increased portability — a required benefit in today's heterogeneous business infrastructure. An application written using JDBC uses only dynamic SQL. The JDBC interface imposes additional processing overhead for obvious reasons.

The new trends in application development have created myriad naming schemes and conventions that can often prove to be the most confusing part. Let's look at the basic information you need to know to make sense of it all: versions, drivers, and kits.


Versions, drivers, and kits

A common source of confusion with Java technology is the fact that there are different versions of JDBC and different types of JDBC drivers that can be used by different Java versions. What's more, the Java versions have their own nicknames and their own Java Development Kits (JDKs).

JDBC versions

The various DB2 releases and fixpack levels require different versions of the DB2 JDBC driver. For details, refer to DB2 JDBC Driver Versions.

You can still attain JDBC-specific certification for different versions and not fully implement the standard (minor exceptions are allowed). Refer to Java development with IBM DB2 — Data Server Driver for JDBC and SQLJ for more information on DB2 JDBC limitations.

Required JAR files

db2jcc.jar or db2jcc4.jar

  • Include db2jcc.jar in the CLASSPATH if you plan to use the version of the IBM Data Server Driver for JDBC and SQLJ that includes only JDBC 3.0 or earlier functions.
  • Include db2jcc4.jar in the CLASSPATH if you plan to use the version of the IBM Data Server Driver for JDBC and SQLJ that includes JDBC 4.0 or later functions, and JDBC 3.0 or earlier functions.

sqlj.zip or sqlj4.zip

  • Include sqlj.zip in the CLASSPATH if you plan to prepare SQLJ applications that include only JDBC 3.0 or earlier functions.
  • Include sqlj4.zip in the CLASSPATH if you plan to prepare SQLJ applications that include JDBC 4.0 or later functions, and JDBC 3.0 or earlier functions.

JDBC drivers

JDBC drivers are divided into four types:

JDBC Type 1 driver

A JDBC Type 1 driver is built into Java technology and basically provides a JDBC-ODBC bridge.

A JDBC Type 1 driver can be used by JDBC 1.2 JDBC 2.0, and JDBC 2.1. Figure 1 shows an example of a JDBC Type 1 driver.

Figure 1. JDBC Type 1 driver
Image shows JDBC Type 1 driver

JDBC Type 2 driver

The DB2 JDBC Type 2 driver is quite popular and is often referred to as the app driver. The app driver name comes from the notion that this driver will perform a native connect through a local DB2 database client to a remote database and from its package name (COM.ibm.db2.jdbc.app.*).

This driver's implementation uses a Java layer bound to the native platform C libraries. Programmers using the J2EE programming model (for example, using SQL on a web application server) will gravitate to the Type 2 driver as it provides top performance and complete function (including the JTA interface). It is also certified for use on J2EE servers (for example, IBM WebSphere).

JDBC Type 2 drivers can be used to support JDBC 1.2, JDBC 2.0, and JDBC 2.1. 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
Image shows JDBC Type 2 driver

JDBC Type 3 driver

The JDBC Type 3 driver is a pure Java implementation that must talk to middleware that provides a DB2 JDBC Applet Server. 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
Image shows JDBC Type 3 driver

The JDBC Type 3 driver is often referred to as the net driver, appropriately named after its package name (COM.ibm.db2.jdbc.net.*). The JDBC Type 3 driver can be used with JDBC 1.2, JDBC 2.0, and JDBC 2.1.

JDBC Type 4 driver

The JDBC Type 4 driver is also a pure Java implementation that is just called a JDBC Type 4 driver. DB2 does not support a JDBC Type 4 driver. In an upcoming release of DB2, you can expect to see this support. An application using a JDBC Type 4 driver does not need to interface with a DB2 client for connectivity because this driver comes with Distributed Relational Database Architecture Application Requester (DRDA AR) functionality built into the driver.

Because a JDBC Type 4 driver supports DRDA and can make direct connections to a database, this may give rise to the notion that there will no longer be a need for DB2 Connect (the data management middleware that provides DRDA AR functionality to workstations). This simply is not true for many reasons, which are beyond the scope of this article. DB2 Connect is a comprehensive middleware solution that adds significant business value to an organization.

DB2 Connect adds so much more to a solution, like support for not only JDBC, but ODBC, DB2 CLI, ADO, or embedded SQL, a connection concentrator, and access to non-mainframe resources such as CICS®, IMS®, VSAM, and MQSeries®, and much more.

Java Development Kits (JDKs) and Java Runtime Environments (JREs)

Each version of JDBC has an associated JDK/JRE:

  • The JDBC 1.22 API is supported by JDK 1.1.x.
  • The JDBC 2.0 and JDBC 2.1 APIs are divided into two packages:
    • java.sql package, which includes the core API, it is an enhancement of JDBC 1.22 API
    • javax.sql package, which is an optional package used to support connection pooling, distributed transaction, and other such advanced functions.
  • JDBC 2.0 and JDBC 2.1 core APIs are supported by Java 2 SDK, Standard Edition (J2SE), implemented in JDK 1.2 and higher), and the JDBC 2.0 optional package is supported by Java 2 SDK, Enterprise Edition (J2EE), implemented by a J2EE vendor). JDBC 2.1 and JDBC 2.0 have the same optional package; only their core API package is slightly different.

Sometimes you may hear the terms Java 2 or Java 3. The term Java 2 is used to describe a JDK or JRE 1.2 environment, while Java 3 refers to JDK or JRE 1.3 environment.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=87319
ArticleTitle=An overview of DB2 and Java Database Connectivity (JDBC)
publish-date=03012002