An overview of DB2 and Java Database Connectivity (JDBC)
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 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).
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 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
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
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
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.