Client-to-DB2/400 Connectivity with JDBC

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

If you’re going to write an application in Java to access IBM’s DB2/400, you should consider using JDBC. It can build database portability into your application with very little additional effort. Even if you don’t need portability today, the move toward network computing will increase the chance it could become necessary in the future.

Addressing the subject of Java Database Connectivity (JDBC) client-to-AS/400 connectivity computing would be difficult without mentioning Microsoft’s ODBC API. Anyone who has experience with client/server development has come across ODBC. If you want to connect to DB2/400, or just about any vendor’s RDBMS, ODBC can, for the most part, provide the access. Writing an application interface to an RDBMS using ODBC allows the application to run against different vendors’ databases without modifying the database interface code.

Another benefit of ODBC is the fact that it uses SQL (the de facto standard RDBMS language) to interface with databases. The actual connection occurs through a driver written specifically for a particular database. Database vendors are very interested in providing ODBC drivers to their databases so software developers can gain access to the benefits of their RDBMS. For example, IBM provides an ODBC driver written specifically for DB2/400 as part of its Client Access/400 product. ODBC is often referred to as “middleware” since it physically resides between the client and the server.

You may be wondering why I’m describing ODBC when this article’s focus is JDBC. The reason is simple: If you understand ODBC, then you pretty much have a grasp of the purpose and concepts of JDBC. JDBC is JavaSoft’s (Sun Microsystems’ subsidiary company responsible for Java) Java equivalent to ODBC. In this article, you’ll learn about the different types of JDBC drivers available and which ones to choose for your database connectivity needs.


What Is JDBC?


Similar to ODBC, JDBC is an API for interfacing with a relational database through SQL statements. It allows Java programmers to create applications that can run on almost any RDBMS using essentially the same code. You implement the JDBC API through a driver manager that can support multiple drivers connecting to different databases, allowing your application to communicate with multiple databases from multiple vendors. JDBC drivers can be entirely written in Java so that they can be downloaded as part of an applet, or they can be implemented using native operating system methods to bridge to existing database-access libraries. As you’ll learn here, both types of drivers are available for the AS/400. Similar to ODBC, database vendors also provide drivers that enable JDBC API calls to their particular databases.

The idea behind JDBC (and ODBC) is to allow a software developer to write a single interface that will work with any compatible RDBMS. You can think of JDBC as extending the Java motto, “write once, run anywhere,” to relational databases—write your application once, and run it on any database.

To give you a feel for the type of database interactions you’ll be working with if you choose to use JDBC, here is a list of the defined Java interfaces (not to be confused with the Java language element known as an interface):

• The Driver interface creates the connection and returns information about the driver version.

• The Connection interface represents a connection to a specific database.
• The Statement interface runs SQL statements and obtains the results.
• The PreparedStatement interface runs compiled SQL statements.
• The CallableStatement interface runs SQL stored procedures.
• The ResultSet interface provides access to a table of data that is generated by running an SQL query or DatabaseMetaData
catalog method.
• The ResultSetMetaData interface determines the types and properties of the columns in a ResultSet.

• The DatabaseMetaData interface provides information about the database as a Most of the major RDBMS vendors are offering JDBC drivers—after all, it’s to their advantage to provide Java developers access to their database. IBM provides two for the AS/400: one that comes with the AS/400 Development Kit for Java, and one that comes with the AS/400 Toolbox for Java. JDBC is also a standard part of SunSoft’s Java Development Kit (JDK) 1.1. So, if you are developing in Java, you most likely have some type of JDBC driver available to use. But why should you consider using it?




Here are a few compelling reasons to use JDBC:
1. You want your Java applications and applets to access more than one vendor’s database with essentially the same application code.

2. You want to use a standard RDBMS language, namely SQL, to work with your database.

3. You have no need to learn a proprietary set of commands, operations, or APIs to get to an RDBMS.

There are also good reasons not to use JDBC:
1. JDBC hasn’t matured enough to work similarly across dissimilar RDBMS systems. If your application must support dissimilar databases, don’t expect all drivers to work consistently at this point. You will encounter problems.

2. Your application doesn’t lend itself to the set-at-a-time record retrieval inherent with SQL, the database language you’ll use with JDBC. For example, a real-time transaction-oriented application might be better handled with record-at-a-time processing. For a real-time transaction-oriented application, you would probably find record-at-a-time processing a better choice. Traditionally, on the AS/400, this type of processing was done


with RPG. However, record-at-a-time processing can also be done with Java through a special set of Java classes found in the AS/400 Toolbox for Java.


All JDBC Drivers Are Not Created Equal


If you decide JDBC is right for your application’s database interface, be aware that one size does not fit all. Just as car makers create different vehicle types that contain the same components but that are meant for different applications (a truck and a limousine, for instance, each has a steering column and four wheels), four major types of JDBC drivers exist. Each of these drivers has its strengths in different application environments. (Use the illustration in Figure 1 to help you visualize the basic differences among the four types.) In this section, I’ll briefly describe each type, but more importantly, I’ll define the environment most appropriate for each.

Type 1: JDBC-ODBC bridge. As its name implies, this JDBC driver type forms a bridge to ODBC drivers. If your application already exploits ODBC extensively (e.g., it has a lot of data sources already defined), you might consider Type 1, but understand the shortcomings of this driver type. The bridge can’t be used directly by an applet because all of the code can’t be downloaded. Each of your client systems will need some client code installed: ODBC code, the JDBC driver code, and possibly some database client code. Also, be aware that some browsers may not allow applets to use the client code. The Type 1 driver is probably not a good choice unless it is the only driver type available or you are already using ODBC in a three-tier environment on a LAN or WAN that employs an ODBC server. Even then, however, it defeats the purpose of having an ODBC server that is meant to serve multiple database requests at the same time. Since the ODBC driver that the bridge uses is not thread-safe, it means database requests are serialized (handled one at a time).

Type 2: Native API. The Type 2 driver converts JDBC calls to the native database API calls of specific databases. It uses the existing shared library interface for database access. Type 2 requires at least some code from the shared library to be installed on the client machine. Because Type 2 is dependent on having non-Java database-specific code installed on the client, it is not a good candidate where the front-end is an applet. However, Type 2 is a good choice for delivering host-based applications or applications that deliver client applications (rather than applets). The AS/400 Developer Kit for Java JDBC driver that ships with OS/400 as of V4R2 is a Type 2 driver.

Type 3: Net protocol (all Java). The Type 3 driver translates JDBC calls into an RDBMS-independent net protocol that is then translated to an RDBMS protocol by a server. The translation task requires a middle-tier server. This net server middleware is able to connect all of its Java clients to many different databases. The specific protocol used depends on the vendor. In general, Type 3 is the most flexible of the JDBC alternatives. It can be used with Java applications and applets. It is likely that all vendors of this solution will provide products suitable for intranet use. In order for these products to also support Internet access, they must handle the additional need for security, access through firewalls, and other requirements that the Web imposes. Several vendors are adding JDBC drivers to their existing database middleware products. Type 3 is good for connecting all-Java clients to a variety of databases.

Type 4: Native protocol (all Java). Type 4 converts JDBC calls directly into the network protocol used by the RDBMS. Similar to Type 3, Type 4 drivers are written entirely in Java, so they don’t require any code installation on the client and can be used for applets as well as applications. They allow a direct call from the client machine to the RDBMS server using the network protocol employed by the particular database. Since the network protocol is proprietary, Type 4 is a better solution for intranet rather than Internet access in a multiple-database environment. The AS/400 Toolbox for Java JDBC driver is a Type 4 driver.

If you are using an applet front-end, Type 4 is a good choice. For server-side applications or where your front-end is delivered as an application, consider Type 2. As mentioned earlier, IBM provides both types of drivers for the AS/400.

For the most part, drivers from the database vendor are probably going to be a good choice since it’s in the vendor’s best interest to provide efficient, reliable access to their database from Java. You might want to consider other vendors’ JDBC drivers if they offer additional benefits, such as performance improvements or indirect value through features not available from the database vendor. One such driver is available from HiT Software, Inc.: HiT JDBC/400, a Type 4 driver. If your environment runs a database server and a Web server on different systems, HiT JDBC/400 works with HiT dbProxy+ Server to allow Java applets access to data on the database server without violating Java applet security restrictions. HiT dbProxy+ server is included with each copy of HiT JDBC/400.


The IBM AS/400 JDBC Drivers


The AS/400 Toolbox for Java JDBC driver allows Java programs to access AS/400 database files using the standard Type 4 JDBC interfaces mentioned previously. You use these standard JDBC interfaces to issue SQL statements and process results. Consider using this driver if you want to access DB2/400 from a browser. For example, you would use this driver to create an application allowing your customers to obtain order information over the Web.

The AS/400 Toolbox for Java is included with V4R2. Its licensed program number is 5798JC1. If you have V4R1, V3R7, or V3R2, you can obtain an evaluation copy at the AS/400 Web site (go to http://www.as400., select Java as the AS/400 Hot Topic, and click Go. This will take you to the main AS/400 Java page, where you’ll find a link to the Toolbox for Java. V4R1, V3R7, or V3R2 users who want a permanent copy must order the Toolbox from IBM. If you are in the United States, call 800-426-2255. Ask for an AS/400 representative and order licensed program 5798JC1. There’s also a new beta version of the Toolbox (Modification 1) available as a download from the Web site just mentioned.

The other IBM AS/400 driver is the Type 2 driver included with the AS/400 Development Kit for Java. (The AS/400 Development Kit for Java allows programmers to create and run Java programs in OS/400.) You will want to use this driver if you are building a host-based application in Java, you want to work with DB2/400 using SQL, and you want a degree of portability. In theory, a host-based application written in Java using JDBC could be ported to another server without too much effort. The AS/400 Development Kit for Java is feature number 2586of 5769SS. It is available with OS/400 V4R2 as a no- charge feature, and it is fully compliant with Sun’s JDK 1.1.4. (It is not available for earlier releases.)

A great place to get detailed information about how to use both AS/400 JDBC drivers is at the IBM Web site at Two downloadable electronic books in HTML format that are available at this site provide good information about how to program with each of the JDBC drivers: Accessing AS/400 Data Using the AS/400 Toolbox for Java and Using the AS/400 Developer Kit for Java.

To see an example of Java code that uses the AS/400 Toolbox for Java JDBC driver, see “Getting to Know JDBC” in the June 1998 issue of MC (this code can be downloaded from our Web site at


Use the Right Tool for the Job


For those developers who want universal access to an RDBMS for their Java-based application, JDBC is something you should seriously consider. It provides the highest degree of standardized database portability available. If you decide to use JDBC, choose the driver appropriate for the environment in which your application will run. One size doesn’t fit all when it comes to JDBC drivers. For Java applications, a Type 2 driver from the database vendor is probably the best choice for most. For Java applets, a Type 4 driver from the database vendor is a good choice. Fortunately for AS/400 users, IBM already provides both driver types.

Whether you plan to develop server-based Java applications or client-based Java applets, let JDBC allow you to create a universal database interface. Consider, however, one caveat: JDBC drivers haven’t matured well enough for you to expect them to work consistently across dissimilar databases.

Type 1: JDBC-ODBC Bridge



 Type 2: Native API










Information from DB2/400, Oracle, Sybase, Informix, or other database

Client-_to-_DB2-_400_Connectivity_with_JDBC06-00.png 172x147

JDBC Driver Manager ODBC Driver Native Database API

JDBC Driver





Figure 1: The JDBC driver types

Information from DB2/400, Oracle, Sybase, Informix, or other database

Client-_to-_DB2-_400_Connectivity_with_JDBC06-01.png 172x147

JDBC Driver Manager JDBC Driver

Type 3: Net Protocol

JDBC Driver

 JDBC Driver


Information from DB2/400, Oracle, Sybase, Informix, or other database

Client-_to-_DB2-_400_Connectivity_with_JDBC06-02.png 172x147

Middle Tier

JDBC Server

JDBC Driver Manager JDBC Driver Manager

Type 4: Native Protocol

Information from DB2/400, Oracle, Sybase, Informix, or other database

Client-_to-_DB2-_400_Connectivity_with_JDBC06-03.png 172x147