![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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 JavaTM.
- Developing Windows,®, Web, 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 Version 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 that is very 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.
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 web 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 a myriad of 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.
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, the versions of Java have their own nicknames and their own Java Development Kits (JDKs).
DB2 Universal Database, for the most part, supports JDBC Versions 1.2, 2.0, and 2.1 – with some operating system restrictions. Currently, work is under way for Sun Microsystems to complete the JDBC Version 3.0 specification which has just recently moved into its final draft.
Though not an official statement from within the DB2 development labs, we have not noticed any changes between JDBC Version 2.0 and JDBC Version 2.1 with respect to a DB2 environment. DB2 Version 7.2 provides certifiable support for different versions of JDBC. However, you can still attain JDBC-specific certification for different versions and not fully implement the standard (minor exceptions are allowed). Refer to www.ibm.com/software/data/db2/java/ for more information on any DB2 JDBC limitations.
JDBC drivers are divided into four types.
A JDBC Type 1 driver is built into Java and basically provides a JDBC-ODBC bridge. DB2 Version 7 does support this type of driver since it is just an ODBC conversion; however, it is typically not used any more.
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
The DB2JDBC Type 2 driver is quite popular and is often referred to as the app driver. DB2 Version 7 supports an app driver. The app driver name comes from the notion that this driver will perform a native connect through a local DB2 client to a remote database, and from its package name (COM.ibm.db2.jdbc.app.*).
In other words, you have to have a DB2 client installed on the machine where the application that is making the JDBC calls runs. The JDBC Type 2 driver is a combination of Java and native code, and will therefore always yield better performance than a Java-only Type 3 or Type 4 implementation.
This driver's implementation uses a Java layer that is 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
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.*). DB2 Version 7 supports the net driver. The JDBC Type 3 driver can be used with JDBC 1.2, JDBC 2.0, and JDBC 2.1.
The JDBC Type 4 driver is also a pure Java implementation that is just called a JDBC Type 4 driver for now, because there is no package for it in DB2 Version 7. DB2 Version 7 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 (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, much more!
Java Development Kits (JDKs) and Java Run Time 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 (an optional package which is used to support connection pooling, distributed transaction, and other such advanced functions).
- JDBC 2.0 and JDBC 2.1 core APIs are supported by J2SE (Java 2 SDK, Standard Edition, implemented in JDK 1.2 and above), and the JDBC 2.0 optional package is supported by J2EE (Java 2 SDK, Enterprise Edition, 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.
Q1: Can I change the JDBC Driver in the Stored Procedure Builder to a third party JDBC Driver?
A1: The Stored Procedure Builder does not support third party JDBC drivers. For example, there are several strings pertaining to connection information that the Stored Procedure Builder uses to determine platform rules. Other JDBC drivers may change the format of this connection metadata.
Q2: Does DB2 V7 support updatable scrollable cursors? If not, what is the workaround for this with the current IBM DB2 JDBC Driver? Are you planning to have this feature incorporated? Is there any third party driver that could help?
A2: Updatable scrollable cursors are not supported in V7, although it is planned to be supported in a later release of DB2. There are no interim drivers available, nor do I know of another JDBC driver that supports this feature. The only workaround is for your application to issue its own fully qualified update on another statement, and then reopen the cursor to see the update.
Q3: Is DB2 likely to continue to support JDBC 1.2?
A3: DB2 Version 5, Version 6, and Version 7 will always support JDBC 1.2. The next version is planned to have a minimum support of JDBC 2.0
Q4: Does DB2 UDB support a just-in-time (JIT) compiler? Can I use a JIT compiler?
A4: DB2 UDB - or any Java application for that matter - does not have to do anything special to support the JIT compiler. The JIT compiler is wholly contained within the JRE and is invisible to Java applications. That being said, the development team does test DB2 UDB with the JIT compiler on because that is the default for IBM's JREs. It many cases, it makes sense to use the JIT compiler since it increases performance. This is not to say that the JIT compiler will work perfectly all the time - there have been problems with it in the past, but these are problems with the JVM not DB2 UDB.
Q5: Where can I find information on the DB2 and supported JDKs, JREs, and JDBC?
A5: Check out the following Web site: www.ibm.com/software/data/db2/java.
Paul 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.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
