Craig S. Mullins 

Return to Home Page

December 2002


DB2 Everyplace: A Mobile DB2.

by Craig S. Mullins


It was a rare day off for Jill, a DBA for one of the local financial institutions. But she was not at home or on vacation. No, she was in the hospital for some outpatient surgery. The lab technician took her blood pressure and then he grabbed what looked like a PalmPilot-style personal digital assistant (PDA) and entered the results. He told Jill that he carried the device all day to record patient vital statistics. Jill was interested to learn that at the end of his shift, he downloaded the data into the hospitals patient information system so doctors, nurses, and anyone with access to the main computer could pull up her updated information. The portable devices made life much easier for hospital personnel, putting an end to using clumsy PCs, reading poor handwriting or losing paper files.

Managing Data on Mobile Information Systems

The medical scenario I just described is one of the ways organizations across many different industries are implementing mobile information systems. A plethora of different names are used to describe this type of system: pervasive computing, wireless computing, handheld systems, and palmtop applications. The hallmark of the system is portability because a device can be carried wherever a user needs to use it. Data is input into a small, occasionally connected handheld device. An occasionally connected device doesn't have to be part of a network, plugged into a wall, or otherwise attached to anything to run. Then, the device is synchronized to a central data store. The most common application that spawned this new computing paradigm is PDA software that stores appointment calendars, contacts, and notes. The PalmPilot by Palm Inc. was the first truly popular PDA device. However, there are many more applications that are viable for handheld computers if you have the right tools.

One of the tools is a system to store, retrieve, organize, and manage handheld data, as well as synchronize the data with a traditional server-based RDBMS. IBM's DB2 Everyplace v.7.1 is one such system. The product was introduced in May 1999 as DB2 Everywhere. It was first available from IBM as a download from its Web site in August 1999. The initial version supported only Windows CE and PalmOS. With this latest version, IBM has changed the name to DB2 Everyplace and the version number to 7.1 to align it with the DB2 version number on other platforms. DB2 Everyplace is designed for low-cost, low-power, small form-factor devices such as PDAs, handheld personal computers (HPCs) or Pocket PCs, and embedded devices. IBM plans to release DB2 Everyplace on PalmOS by the end of July 2000 and for other handheld devices running EPOC, Neutrino, Windows CE, and embedded Linux at the end of September 2000.

One characteristic of handheld database systems is their small size. Instead of a footprint, IBM refers to DB2 Everyplace's fingerprint because its size is too small to be labeled a footprint. DB2 Everyplace is a relational database system with a tiny fingerprint of about 100 to 150K 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. There is also a mechanism for synchronizing the relational data on the handheld device to and from other DB2 data sources such as DB2 Universal Database (UDB) running on Unix, Windows NT, or OS/390 platforms.

DB2 Everyplace Components

There are three basic DB2 Everyplace components: the handheld database engine, the Synchronization Server, and the Personal Application Builder (PAB).

Handheld Database Engine

The first component of DB2 Everyplace is its handheld database engine. The engine is a true, relational database engine delivering persistent storage for record sets and the ability to modify and retrieve records. It also provides the integrity mechanism to guarantee that data is not lost or corrupted if a handheld device is powered off or dropped during processing. Note that DB2 Everyplace's database engine is not nearly as complex as the DB2 engine that runs on OS/390, Unix, or Windows NT. DB2 Everyplace is scaled to fit into about 100 to 150K of memory. Also, DB2 Everyplace is shareable by multiple applications, which means each new application doesn't require a new instance of the DB2 Everyplace database engine.

Data in a DB2 Everyplace database can be accessed using several different methods. One simple way that doesn't require you to know SQL is to use the Query By Example (QBE) interface to issue queries. QBE is provided with the base DB2 Everyplace product. If you are a little more sophisticated, a second way to access data is to use the QBE Command Line Processor (CLP) to issue SQL statements. The CLP is a window accessed using QBE. You simply type a SQL statement in the query field, tap the "Run SQL" button, the SQL is executed, and the results returned. A third method is to write your own applications using ODBC call-level interface functions.

DB2 Everyplace uses SQL to modify and access data. SQL is the API through which applications access relational data. The SQL version supported by DB2 Everyplace is a subset of the SQL supported by DB2 on other platforms. A full SQL implementation is not required on handheld devices because most data accesses are simple data entry and retrieval requests. All basic SQL DML statements (INSERT, UPDATE, DELETE, and SELECT) are supported, as well as many other common operations including DBCS support, joins, and cursors. DB2 Everyplace v.7.1 even delivers scrollable cursors. However, UNION, a standard relational feature, is not supported by DB2 Everyplace.

An intriguing new feature in DB2 Everyplace is indexing support. Some of the most important factors for DB2 performance are proper index design, creation, and management. However, DB2 Everyplace databases are very small, so indexing was not IBM's first priority in earlier versions. In DB2 Everyplace v.7.1, indexes provide a welcome performance boost for medium and large DB2 Everyplace tables. Keep in mind that a medium-sized table for DB2 Everyplace is smaller than traditional DB2 tables residing on midrange and mainframe servers. However, IBM has used DB2 Everyplace to manage databases of up to 120MB.

There are some limitations to DB2 Everyplace. For example, it doesn't support subqueries, and you can't create views. Also, some object/relational features are not available, such as triggers, stored procedures, LOBs, and user-defined functions. DB2 Everyplace is designed to access data from small databases, so currently there are no compelling reasons for such advanced features. Additionally, keep in mind that locking is not required for DB2 Everyplace because a handheld device is intrinsically designed for a single user. Theres no reason to lock the data from other users.

Synchronization Server

The second component of DB2 Everyplace is the Synchronization Server, or Sync Server, which is new to DB2 Everyplace v.7.1. Sync Server takes the place of IBM Mobile Connect, an additional product that used to be required to synchronize handheld device data with a central server. Sync Server is a client/server program that manages the data synchronization process from the handheld DB2 Everyplace database to the source DB2 database. The source DB2 database can be any DB2 UDB server platform. Sync Server requires a client to be installed on the handheld device and a server component to be installed on the platform to which data is to be synchronized. The Sync Server engine requires a midtier DB2 UDB for Windows NT server, regardless of the host server to which the data is to be synchronized. Unix support for the midtier server will be provided in the fourth quarter of 2000.

Sync Server enables two-way data synchronization from the handheld database to a DB2 UDB database, as well as from the DB2 UDB database to the handheld database. To synchronize data, Sync Server initiates a synchronization session. This session is a two-way process during which:

  • Mobile users submit changes that have been made to local copies of source data.
  • Mobile users receive changes that have been made to source data residing on the enterprise server since the last time the data was synchronized.

Personal Application Builder

The third component of DB2 Everyplace is the Personal Application Builder. PAB is an integrated toolkit for developing DB2 Everyplace applications running on handheld devices. It supports building applications for small handheld devices that access DB2. PAB makes it easy to write robust applications on a more powerful development platform (such as a Windows PC) for deployment to handheld devices. It supports visual forms construction for different devices, and it provides scripting capabilities for user-defined logic. PAB also integrates with other tools for application testing and debugging.

IBM will support the creation of PalmOS applications with the initial release of PAB for DB2 Everyplace. Support for other handheld platforms will be added later.

To begin developing applications using PAB for PalmOS, you need to download the supporting GNU Palm tools from IBM. PAB generates GNU-compatible C code.

Another useful tool for testing Palm applications is the PalmOS Emulator (POSE). POSE is not a part of PAB. POSE can be downloaded from the Palm Web site at . POSE emulates the Palm handheld device hardware. Using POSE, you can create a virtual handheld device running on Windows, Mac OS, or Unix machines. To run the emulator, you will 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. PAB lets you develop applications and test them using POSE without ever having to move the application to the handheld device. Only when the development and testing process is complete will you need to move the code to the Palm device. DB2 Everyplace includes a sample project for the PalmOS that features sample code. You can use the sample project as a template for applications and to learn coding techniques using PAB.

Production Application

There are a myriad of potential DB2 Everyplace applications such as eScholar, developed by eScholar LLC ( ), a wholly owned subsidiary of IBM Business Partner Vision Associates. The eScholar application uses DB2 Everyplace to make student performance, class attendance, and other educational profile data accessible on handheld devices for teachers, administrators, and counselors. The application is innovative because most teachers lack the time to sit at a computer and access data while they are teaching students. Teachers can use eScholar and a Palm device to quickly obtain important student information without having to disrupt student interaction.

Database Administration Challenges

So far, so good, but how will DB2 Everyplace impact your IT organization when it's implemented on handheld devices? Although DB2 Everyplace doesn't require the extensive tuning and administration necessary for enterprise databases, it is still relational. Databases should be developed using sound logical and physical design techniques including data modeling and normalization.

The biggest impact of DB2 Everyplace is planning for and managing 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 data reliably and on schedule? Potential problems that could arise from failing to synchronize include:

  • Outdated information on the centralized database
  • Outdated information on the handheld device
  • Large files on the handheld device that could cause slower synchronization speeds when the files are eventually synched
  • Slow application performance on the handheld device.

These are not minor issues. Make sure your DBA staff is prepared for the impact before implementing a large battalion of handheld database users who must synchronize their data. It is important to use forethought to determine which existing application systems in your organization will be the first ported to handheld devices. Possible targets include sales or delivery tracking systems used by remote workers. In particular, consider how system infrastructure will be affected by a large influx of remote connections.


Jill arrived back at her desk the day after her medical appointment only to find a project request for a new application requiring DB2 Everyplace running on handheld Palm devices. The bank's ATM division issued a mandate calling for all bank technicians who care for ATMs to carry Palm computing devices. The project would require a new application for entering details about the maintenance, stocking, and status of each ATM. The information would be collected each day and then synchronized with the enterprise ATM application running on the mainframe under CICS and DB2 for OS/390. Jill was glad she had asked questions at the hospital. She considered telling her boss that she'd been conducting research for the new project so that she wouldn't have to use up a sick day after all. At least Jill knew her project would be in good hands with DB2 Everyplace!


Systems Supported by DB2 Everyplace

DB2 Everyplace runs on PalmOS, Windows CE, EPOC, QNX Neutrino, and embedded Linux.

Palm Inc.'s ( ) PalmOS operating system is designed for the Palm series of devices made by Palm Inc., including the Palm II, V, and VII. Other devices, such as the Handspring Visor, also use the PalmOS.

Microsoft's Windows CE ( ) operating system powers the PocketPC. Numerous companies supply handheld devices that run Windows CE including Hewlett Packard, Casio, and Compaq.

Symbian's ( ) EPOC platform is designed for optimal flexibility, giving consumer electronics manufacturers a broad scope for differentiation and innovation in user interfaces, hardware designs, and connectivity. EPOC is divided into two types of device families: communicators and smartphones. Companies such as Ericsson, Psion, Sony, and Texas Instruments manufacture and market devices that use the EPOC operating system.

QNX Software Systems Ltd.'s ( ) QNX Neutrino is a realtime, extensible, POSIX-certified operating system. Many partner vendors supply embedded systems using the QNX Neutrino operating system. IBM plans to release DB2 Everyplace on PalmOS by the end of July 2000 and for other handheld platforms (EPOC, QNX Neutrino, Windows CE, and embedded Linux) at the end of September 2000.



From DB2 Update (Xephon) 2002.

2002 Craig S. Mullins, All rights reserved.