Sun, Sep
3 New Articles

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 www.alphaworks.ibm.com/ 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 (rs.next()) { 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@ us.ibm.com.

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: www.iseries.ibm.com/developer/jdbc/
• AS/400 Toolbox for Java and JTOpen page: www.iSeries.ibm.com/toolbox
• CLDC and the K Virtual Machine (KVM) Web page: http://java.sun.com/products/cldc
• Java 2 Platform, Micro Edition Web Page: http://java.sun.com/j2me/index.html
• JDBCMe for DB2 page: www.alphaworks.ibm.com/tech/jdbcme
• Mobile Information Device Profile (MIDP) Web page: http://java.sun.com/products/midp
• Motorola Wireless Resources Web page: www.motorola.com/spin/j2me
• Palm OS Emulator Web page: www.palmos.com/dev/tech/tools/emulator
• The kAWT Project Web page: www.trantor.de/kawt

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.



Support MC Press Online

$0.00 Raised:

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: