©  2021  Mullins Consulting, Inc.   All Rights Reserved   Privacy Policy   Contact Us

DB2 Everyplace: You Can Take It With You

by Craig S. Mullins

Fall 2000

It was a rare day off for Jill, a DBA for one of the local banks. But she was not at home or on vacation. No, she was in the hospital for some out patient surgery. The lab technician took the blood pressure cuff from around her arm and placed it back into the receptacle on the wall. Then he grabbed what looked like a Palm PDA and started fiddling around with it. Jill loves gadgets and her curiosity got the better of her, causing her to ask “What are you doing with that?”

“I’m entering your blood pressure readings into this gadget, here,” replied the technician. “We’ve had to do this for the past few weeks or so. It’s all about some new procedure for storing patients vital statistics.”

“But isn’t it only useful for you?  I mean, if you’re just entering it into your gadget, then no one else can use the data, can they?”

“No, it doesn’t work like that. When I’m through entering your temperature, height, and weight, I can send your information to the doctor’s gadget. I carry this with me all day, recording patient information. But before I go home for the day I have to put this gadget in a little gizmo and press a button. The next day doctors, nurses, and I can pull your information up on our central computer system.”

“Sounds cool to me,” Jill said. “Do you have any idea how it works?”

“Nope. But it makes life easier for me. I don’t have to worry about using clumsy PCs, reading poor handwriting, or losing files any more. I love it.”

“Does everyone use them?”

“We all use them now. Even the nursing staff uses them when they make home visits to the elderly and disabled. And we have outfitted the emergency crew in our ambulances with the devices too.”

What Was That System?

The above-described scenario is being implemented at many different organizations across many different industries. A plethora of different names are used to describe this type of system: pervasive computing, wireless computing, handheld systems, and palmtop applications. Whatever you call it, data is collected from a small, disconnected handheld device. The hallmark of the application is portability. Not in the traditional computing sense (software that can run on multiple platforms), but in a real sense because the device can be carried wherever the user has a need to use it. Then, at some point, the device is synchronized to a central data store. The “killer” application that spawned this new computing paradigm is personal digital assistant (PDA) software that stores appointment calendars, contacts, “to do” lists, and notes. The PalmPilot by Palm Computing Corporation was the first truly popular PDA device. But I’m sure we can all envision many more applications that are viable for handheld computers. That is, if we have the right tools.

One of those tools is a system to store, retrieve, and manage handheld data, as well as to synchronize the data with a traditional server-based RDBMS. DB2 Everyplace is just such a system. It is designed for low-cost, low-power, small form-factor devices such as personal digital assistants (PDAs), handheld personal computers (HPCs) or Pocket PCs, and embedded devices. For a complete list of systems supported by DB2 Everyplace, refer to the Sidebar, “Systems Supported by DB2 Everyplace.”

One characteristic of all PDA databases is their size; they are small. Instead of a footprint, IBM talks about the “fingerprint” of DB2 Everyplace – the claim being that its size is just too small to be labeled a footprint. DB2 Everyplace is a relational database system with a tiny 100K "fingerprint" specifically designed for small, handheld devices. The general idea is to store a small amount of critical data on the handheld device that is later synchronized to other, more complete and long-term data stores. DB2 Everyplace provides a local data store on the handheld device and a mechanism for synchronizing the relational data on the handheld device to and from other DB2 data sources such as DB2 running on UNIX, Windows NT, AS/400, or OS/390 platforms.

DB2 Everyplace enables a relational database to reside on your handheld device. Since it is an actual relational database, it uses SQL to modify and access data. The version SQL supported by DB2 Everyplace is a subset of the SQL supported by DB2 on other platforms. One simple way of accessing DB2 Everyplace data is using the Query By Example (QBE) application. Using the simple query by example interface it is very easy to issue queries against data stored in your handheld device. Users need not know SQL to use QBE. However, if you are a little more sophisticated, you can use the QBE Command Line Processor (CLP) to issue SQL statements. QBE is provided with the base DB2 Everyplace product. A final method of accessing DB2 Everyplace data on your handheld device it to write your own applications using Call Level Interface (CLI) functions.

Sidebar. Systems Supported by DB2 Everyplace

The Components of DB2 Everyplace

There are three basic components of DB2 Everyplace: the handheld database engine, the synchronization engine, and the personal application builder. The database engine that runs on the handheld device is not nearly as complex as the DB2 that runs on OS/390, Unix, or Windows NT. It is a scaled down version of DB2 that fits into 100K. However, IBM had done a credible job maintaining compatibility at the level most of us find most important: SQL. SQL is the API through which our applications access relational data. If you know DB2 SQL on any other platform, it should not take you long to come up to speed on the SQL implementation of DB2 Everyplace. All of the basic SQL DML statements (INSERT, UPDATE, DELETE, SELECT) are there, as well as many other of the most-used features including joins, unions, and cursors. And the latest and greatest version of DB2 Everyplace even delivers scrollable cursors.

But there are some limitations. For example, DB2 Everyplace SQL does not support subqueries and you can not create views. But these are minor drawbacks that are usually easy to overcome – especially when accessing data from a small form-factor database. Furthermore, some of the newer object/relational features are not available such as LOBs, triggers, stored procedures, and user-defined functions. Once again, though, DB2 Everyplace was not designed to function with the large amounts of data required to support object/relational features. So there are no compelling reasons for it to support these features.

The sync engine is new – it replaces the Mobile Connect product that used to be required to synchronize data from DB2 on the handheld to a central server.

The third, and final component of DB2 Everyplace is the Personal Application Builder (PAB). The PAB supports building applications for small handheld devices that access DB2 Everyplace databases. The PAB makes it easier to write robust applications on a Windows development platform for deployment to handheld devices. PAB supports visual construction of forms for different devices and provides scripting capabilities for user defined logic. It integrates with other tools for application testing and debugging.

To begin developing applications using the DB2 Everyplace Personal Application Builder for the Palm Computing platform, you will need to download the supporting GNU Palm tools. The PAB will generate GNU-compatible C code. Another useful tool for testing Palm applications is the POSE Palm Emulator which can be downloaded from the Palm web site at http://www.palm.com/devzone/pose/pose.html. The POSE Palm Emulator can be used to emulate running Palm applications on a Windows PC. To run the emulator, you will also need to download the appropriate Palm ROM for the device for which you are developing applications. Palm provides debug ROMs through licensing or you can download a ROM from your Palm Computing Device. Details are available on the Palm web site.

Sample code is provided in the example project that comes with DB2 Everyplace. You can use the example as a template for your applications and to learn coding techniques using the Personal Application Builder.

Impact on Database Administration

Though you will not need a DBA to work on the database on each PDA, the job of the Database Administrator will indeed be impacted at organizations where DB2 Everyplace is implemented. A database of the size being stored on handheld devices will not require the in-depth tuning and administration that is required of enterprise database implementations. So, what will DBAs be required to do in this brave new world of DB2 Everyplace?

One impact will be on the planning and design of the databases being implemented on the handheld devices. The amount of data being stored should be minimal, but it must be understandable and well-designed. Regardless of platform, DB2 Everyplace is still relational. The database still should be designed using sound logical and physical design techniques including data modeling and normalization.

But the big impact will be in planning for and managing the data synchronization from hundreds or thousands of handheld devices. When should synchronization be scheduled? How will it impact applications that use large production databases that are involved in the synchronization? How can you ensure that a mobile user will synchronize his data reliably and on schedule? Potential problems that could arise from failing to synchronize are outdated information on the centralized database, larger files on the handheld that could cause slower synchronization speeds when the files are eventually synched and may impact local performance of the handheld application.

These are not minor issues. Before implementing a large battalion of handheld database users that must synchronize their data, make sure that your DBA staff is prepared for the impact to their databases. Like most anything, failure to prepare is a recipe for sure disaster.

But prepare we must. DB2 Everyplace is here, and so we must be ready to support the inevitable request for this technology by understanding data synchronization technology and the potential need for remote database users at your organization. Be sure to think ahead of the requests that are sure to come. Apply some forethought to determine which application systems in your organization might be impacted first. Those with remote workers such as sales systems or delivery tracking will most likely be the first to be impacted. Take some time to review the data requirements of those applications and how a large influx of remote connections might impact the current systems. Because, mobile workers are here to stay. And we need to be ready to support them with DB2 Everyplace and a valid, shared data infrastructure.

Conclusion

Jill arrived back at her desk the next day only to find a project request for a new application requiring the use of DB2 Everyplace running on handheld Palm devices. The ATM division put out a new mandate so that all of the bank’s technicians who care for and stock the automated teller machines would carry a Palm device. The project required a new application on the handheld for entering details about the maintenance, stocking, and status of each ATM. The information was to be collected each day and then synchronized with the enterprise ATM application running on the mainframe under CICS and DB2 for OS/390.

“Hmmm,” she thought, “I’m glad I asked all those questions yesterday at the doctor’s office. In fact, I should tell my boss I was out conducting research for this new project and maybe I won’t have to use up a sick day after all!”

From DB2 Magazine, Fall 2000.

© 2012 Craig S. Mullins,