Access AS/400 Data Anywhere Using JDBCMe

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

In the not-so-distant past, there was much talk about a future in which everyone worked from home. The real ability to work from home is gradually coming to pass and is beginning to change the landscape of the workplace. There is also a related trend showing on the horizon. The new trend is not a replacement for the current at-home model, but an augmentation to it. This trend involves being able to work from anywhere. The new trend toward being connected doesn’t just involve work; it involves all aspects of life. It’s truly amazing how accessing data and staying connected can impact almost everything we do.

Tier-0 Devices

The devices that are going to allow this new trend to come to pass are called tier-0 devices, and their sales are booming. An extension of the old model with three tiers of computing (the mainframe at tier 3, the application or Web server at tier 2, and the client desktop or laptop at tier 1), tier 0 refers to the next level down. Tier-0 devices are predominantly thought of as Web-enabled cell phones and personal digital assistants (PDAs). Today you can expect tier-0 devices to include things like automobiles; tomorrow they will include running shoes and soda machines. What tier-0 devices all have in common is that they are relatively small and often cheap enough to embed in practically anything. The ability to network these devices together is what will fan the fires of this revolution.

You can expect this tier-0 revolution to make its way through businesses first. Large companies look to mobilize their employees and free them from tedious tasks like completing multiple copies of paperwork or filling in computer entry screens. A tier-0 device in the hand of a worker on the factory floor, insurance agent at the site of a disaster, or salesman at the customer’s office can enable that freedom. The tier-0 revolution is also driven from the grass roots. Employees wield their tier-0 devices against problems with as much passion as a medieval knight would wield his sword against a foe.

In this article, we’ll outline a new technology called Java Database Connectivity, Micro Edition (JDBCMe). We’ll introduce you to the application nirvana of instant database access anywhere.

The primary goal of JDBCMe is to allow access to AS/400 data from tier-0 devices, but it can easily become much more than that.

Data, Data Everywhere

Our first task in accomplishing the goal of instant access to AS/400 data was to investigate some of the things that were currently going on in the industry. When we did this, we found that the majority of companies are focusing on synchronized data on tier-0 devices instead of live database access.

In a data synchronization model, each user’s tier-0 device has a copy of interesting data from the main database. Periodically, the data on each device is synchronized between the device and the main database. This allows every user’s version of the data to be integrated with other users’ changes. The synchronization model works fairly well for data that is not very dynamic or when the data is not modified or summarized by a large number of people.

The data synchronization model starts to fall apart as the data become more dynamic. For example, if you have an auction application and you place a bid on a product, you don’t want to wait until you are at home that evening to find out whether or not you have the highest bid. You need to know immediately.

Conflicts can also arise if you try to do any form of scheduling. You probably want to know right away that the 1:00 p.m. Tuesday meeting you are trying to set up will overlap the board of directors meeting, so you can take immediate action on the conflict.

Finally, there are situations in which some users need to see summarized data on the fly. For example, decisions might need to be made during the course of the day that require up-to-the-minute information to aid the decision-making. If you have to wait until morning to access the new synchronized data, it may be too late.

Another problem with synchronous data is that it requires additional servers and software configuration complexity to accomplish the task. You have to have software and systems (synchronization servers) in place to handle conflict resolution, or you may even resolve conflicts manually. These ideas may be problematic when thinking about the concept of thousands or hundreds of thousands of connected devices.

The Basics of JDBCMe

What you really want is for everything on your tier-0 device to work exactly as it would if you were sitting at a system in your office. Your application should be able to use all of the same resources provided by the database (stored procedure business logic and data) that you would while in the office. Although some necessary limits are imposed by size requirements, JDBCMe comes much closer to this goal than other data synchronization- based solutions.

JDBCMe focuses its attention on live updates and access to the database while allowing offline data storage. The application can have access to that offline data that is so valuable without sacrificing the ability for live updates to become part of the database immediately. This middle ground approach can provide the benefits of both worlds.

Java is the language of choice to enable tier-0 access to iSeries 400 data because of its natural ability to run on various platforms. JDBCMe currently supports two tier-0 devices (a cell phone emulator and the PalmPilot PDA), but the requirement may exist to support many different devices in the future. The additional amount of unique code required for each tier-0 device should be extremely small (or nonexistent). We also felt that there was great benefit in sticking to the well-known Java Database Connectivity (JDBC) standard for the API. This allows developers to get familiar with coding for the various tier-0 devices very quickly instead of struggling to learn yet another database access API.

Of course, the conflicting goals of being extremely small and still highly functional had to be addressed. The JDBC API is very large. Today’s popular tier-0 devices are very constrained. In order to make a logical fit, we needed to trim many things out of the JDBC API. For example, the JDBC DatabaseMetaData interface has about 150 methods alone. We chose to not support the DatabaseMetaData interface at all. We chose to support very few of the ResultSet getXxx methods. We chose not to support any of the

CallableStatement interface methods at this time, but this seems to be a likely addition in one of the future releases.

Technical Introduction

So, with so many classes, interfaces, and methods missing, what can you do? There is support for ResultSets that are both scrollable and updateable. There is support for using all the transaction isolation levels. There is support for PreparedStatements and stored procedure calls as long as the stored procedure doesn’t try to return output parameters. All of the underlying features of the database are still used such as triggers and user-defined functions. Today, all of this functionality is available in a JDBC driver that fits into about 40 KB of storage on the device itself.

The actual architecture of the JDBCMe driver is client/server-based. The client resides, of course, on the tier-0 device, and the database server resides on the iSeries or AS/400. Because both the client and the server are written in Java, they are both highly movable. Our client remains unchanged when moved to other tier-0 devices except for its offline data persistence mechanism (which we will write about later), and the database server runs on basically any system that has a database, a Java Virtual Machine (JVM), and a JDBC driver. Figure 1 shows conceptually how the parts fit together.

The server was designed to be a generic server first and a JDBC database server second. Conceptually, JDBCMe is simply a service that plugs into the server infrastructure. Other services can be added in the future to provide a host of functions, similar to how the AS/400 Toolbox for Java provides many different functions today.

Being written in 100% pure Java, the server is portable to other back-end databases as well. In our testing, we did some work running against DB2 UDB for Microsoft Windows NT as well as DB2 UDB for OS/400. The server also needed to handle client failures in a robust way. While handling tier-0 devices, you have to plan for thousands of devices, any of which can go offline at anytime for various reasons. The server needed to be able to handle client failures without turning them into server failures. The current design of the server does nothing that would prevent server clustering for increased scalability. Simply starting additional servers listening on additional ports and enabling the JDBCMe client to scan through all the potentially available ports should allow very large numbers of devices to connect concurrently to a system.

From the client perspective, the software functions mostly as a thin network JDBC driver, passing off requests to the back-end server. The data stream between the client and the server is a proprietary design. A primary goal here is to limit line flows and their size. The reasons for this limit are twofold. First, there is a performance penalty to pay anytime you have to hit the wire.

Second, for some network services, connect time charges and pay-per-byte rates for wireless access are not cheap.

The JDBCMe driver provides caching of some ResultSet data during connected processing. At this point, the caching is rudimentary so as not to impact runtime size of the driver. The JDBCMe driver also provides the ability for the application to persist ResultSet data directly to the client device. Once the data is persisted to the client, the data can be manipulated just as any ResultSet data would be manipulated if the application were connected to the back-end database.

JDBCMe in Practice

Let’s talk a little about the technical details relating to development with JDBCMe. First and foremost, the JDBCMe distribution (available at tech/jdbcme) includes detailed instructions and demo applications. We recommend that you use the demo applications to experiment and become familiar with JDBCMe. The demo applications should also serve as a source of code or user interface ideas that can be copied into your

own applications. The JdbcMeRealEstate demo (shown in Figure 2) source code should be a great starting point for your applications.

Standard JDBC Features

As with any JDBC driver, creating a JDBCMe connection requires unique values in the URL:

Connection c1 =


("jdbc:db2:localhost" +


There are only a couple of JDBCMe specific details to note. First, the URL specified is a normal JDBC URL that is ultimately passed directly to whichever JDBC driver is configured on the JDBCMe server that processes the SQL requests. In this example, jdbc:db2:localhost is an AS/400 Native JDBC URL. There is one piece of information that is very specific to JDBCMe in the URL. The JDBC property jdbcme is set to indicate where the JDBCMe database server is running. (The default port of 9090 doesn’t need to be specified.)

Second, if the JDBCMe database server is not running in secure mode and the JDBC driver being used on the JDBCMe server supports connecting using an implicit user ID and password, then your JDBCMe connection can be made without a user ID and password. In the case of the AS/400 native driver, the implicit identity used for the connection, when no user or password is specified, is that of the user that started the JDBCMe server.

We’ve now finished talking about all of the JDBCMe-specific API calls and settings that you need to know about to begin using JDBCMe. After the JDBCMe connection is made, all subsequent JDBC operations act exactly as a JDBC programmer would expect. Be aware that, in order to keep the size and complexity of JDBCMe to a minimum, many JDBC classes and methods have been removed. The rule for JDBCMe is “If it’s there, it works as it should.”

Since a major focus of JDBCMe was stripping the standard JDBC specification to the smallest, most useful subset, its important to talk specifically about the things that are present in the JDBCMe driver. You can use JDBCMe to insert or update data on your AS/400 the same way you would use JDBC on any other platform—Statement.execute(), Statement.executeQuery(), and Statement. executeUpdate(). JDBCMe also provides the advanced JDBC 2.0 features of scrollable and updateable result sets (ResultSet TYPE_ SCROLL_SENSITIVE and ResultSet.

CONCUR_UPDATEABLE), as long as the JDBC driver you’ve configured in the database server supports those features.

The more advanced features of your database can also be accessed using the standard JDBC logic. You can control transactions—Connection.commit() and Connection.rollback()—and you can modify transaction isolation levels if fine-grain control of concurrency issues becomes important to your

SQL work done by JDBCMe can include calling stored procedures and firing triggers written in any language. Stored procedures and triggers allow your PalmPilot or cell phone device to tie into your existing applications with a minimum of change.

Be aware that the JDBC classes PreparedStatement and CallableStatement are not currently provided, so these features are accessed though normal statement objects. CallableStatment support may be forthcoming, depending on user demand.

Since adding many supported data types would increase the size of the JDBCMe driver, your application, and even your virtual machine, JDBCMe allows you to access all SQL data types as Java string values.

With the standard JDBC features just described, you can build very powerful applications.

Enhancements to JDBC

JDBCMe provides several other basic features that will aid you in your development. JDBCMe is unique in that it can easily function as a universal JDBC driver. An application using JDBCMe can target any back-end database without having to load other drivers or change the device configuration. Indeed, a single application can even target two heterogeneous databases at the same time, using the same driver with only a simple URL change. No additional configuration (driver code loading or distribution) is required for the client to access additional heterogeneous databases. Instead, the JDBCMe database server is simply installed and run on the new back-end database. Having a universal JDBC driver allows the majority of the configuration details to be consolidated at a single point on the server side instead of the client. The consolidation allows simplicity of deployment to a large number of heterogeneous clients or even changing database vendors without modifying client deployment and configuration.

JDBCMe also provides a standard mechanism for persisting data to offline storage. In a typical application intended for the tier-0 devices, there are two categories of data. The first category of data we call offline data. Offline data is accessed very frequently for display purposes and changes infrequently. The second category of data is called live data. Live data changes frequently and is typically accessed in a transactional fashion. When accessing live data, it is more important to get the correct values of the live data.

In a real estate application, the offline data is the description of real estate property. The live data would be a calendar reflecting schedules for agents showing the property and any current bids that have been submitted on the property.

In a product catalog application, the offline data may be product listings, features, and SKU numbers. The live data in the same application would likely be current price, quantity on hand, and discount availability.

JDBCMe provides a simple mechanism for storing the results of a live query in an offline data repository—Statement.executeToOfflineData(). The data can later be accessed and updated without making a JDBCMe connection, but by still using normal JDBC query results processing (creating a ResultSet of the JDBCMeOfflineResultSet type).

The following JDBCMe code stores the results from a query in a device-specific way to an offline data store on the device that is running the JDBCMe application. The name, creator, and type parameters of the executeToOfflineData() function are used to uniquely identify the offline data store depending on the device that the JDBCMe application is running on. For a PalmPilot device, the creator and type identify the offline data while the name is simply retained. For a cell phone device, the name uniquely identifies the offline data while the creator and type are ignored.

// Persist the SQL output to the device


("select * from qjdbcme.test",




The following JDBCMe code creates a ResultSet over data that was previously stored offline on the device. The ResultSet functions as a normal JDBC ResultSet and can be used to update the offline data (which, in itself, is a useful feature).

// Build query result set from
// offline data stored on the device

ResultSet rs =

new JdbcMeOfflineResultSet



// Process rs normally as a scrollable,
// updateable result set.
while ( { String s = rs.getString(1);

Environment Weaknesses

We can’t finish without mentioning the fact that there are currently some weaknesses in the Java for tier-0 application space. As the Java Connected Limited Device Configuration (CLDC) spec becomes more heavily used and other JVM implementations become available, we expect that these weaknesses and others like them will disappear.

The application developer will probably find that GUI development for the tier-0 device (especially for the PalmPilot device) is a problematic area in the Java specifications. GUI support differs from JVM to JVM, and the CLDC specification doesn’t explicitly talk about what an appropriate GUI component set should be for the tier-0 device.

We recommend the free kilobyte Abstract Window Toolkit (kAWT) as a mechanism to get up-to-speed on these devices very quickly. The kAWT will let you use normal AWT GUI development and focus as little as possible on the GUI and as much as possible on your application. Later, you can remove or optimize the GUI as you see fit. At the time of the writing of this article, an experimental version of kAWT for Mobile Information Device Profile (MIDP) is available.

In addition to the specification-based GUI issues, there are a couple of issues specifically with the current implementation of the K Vitural Machine (KVM). Java-class verification performance drags during application startup. There is also a requirement that the JVM and Palm OS make a wireless connection at application startup regardless if your networking application chooses not to until later. Both of these issues may impact the usability of your JDBCMe application a bit.

We suspect that both of these issues will be addressed as production-level JVMs that support Java 2 Platform, Micro Edition (J2ME) become available from other vendors. In turn, this will allow Java to take its proper place in the development space of the tier-0 devices.

As a customer, the direction that the JDBCMe product takes now is up to you. As you develop applications for these devices, please feel free to feed back design decisions you make and the enhancements that you expect from JDBCMe to rchjdbc@

Performance optimizations of JDBCMe are now on the top of our list of priorities, but additions outside of JDBC functionality are being considered as well. For example, some minimalist AS/400 Toolbox functionality with specific AS/400 access classes for program call or data queue manipulation may be appropriate. Currently, that functionality can be leveraged by your application using stored procedures.

When considering new functionality, be aware that frequently in tier-0 devices, the focus on size, bandwidth, and optimization drastically reduces the amount of function-ality we can reasonably expect.


We’ve mentioned some of the user scenarios that demand data access on tier-0 devices and some of the problems and solutions related to tier-0 data access in those scenarios. We’ve touched briefly on the design rationale behind JDBCMe and introduced you to the technical


aspects of application development using JDBCMe. We’ve mentioned several weaknesses of the CLDC and a PalmPilot implementation of the CLDC specification.

You now have everything it takes to begin making some amazingly cool and useful AS/400 applications that can travel anywhere you do. The tier-0 environment still has much room for invention and discovery. Hopefully, these devices and JDBCMe will make your job easier, and, more important, much more fun.


• AS/400 Developer Kit for Java Web page:
• AS/400 Toolbox for Java and JTOpen page:
• CLDC and the K Virtual Machine (KVM) Web page:
• Java 2 Platform, Micro Edition Web Page:
• JDBCMe for DB2 page:
• Mobile Information Device Profile (MIDP) Web page:
• Motorola Wireless Resources Web page:
• Palm OS Emulator Web page:
• The kAWT Project Web page:

Client Server

JDBCMe Server JDBC Driver


Application JDBCMe Client


JDBC Driver Specific

JDBCMe Protocol over TCP/IP

Figure 1: JDBCMe is a highly portable client/server architecture.

Access_AS-_400_Data_Anywhere_Using_JDBCMe08-00.png 349x533

Figure 2: A screen shot of the demo JdbcMeRealEstate application looks like this.