14
Thu, Nov
5 New Articles

Client-to-DB2/400 Connectivity with JDBC

Typography
  • 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?

 

Why JDBC?

 

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

whole.

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. ibm.com), 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 http://publib.boulder.ibm.com/pubs/html/as400/java/java.htm. 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 http://www.midrangecomputing.com/98/10).

 

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


 CLIENT

 


 Type 2: Native API

 

SERVER

Database

SERVER

Database

SERVER

Database

SERVER

Database




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




CLIENT


 

CLIENT

CLIENT

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

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: