Mobile application development is a pretty broad category. If you're creating apps for mobile devices, you might follow one of these scenarios:

• A standalone app that doesn't need new data except as part of a revision (for example, reference material, or any app with a static data set).
• An app with read-only or uni-directional data that requires infrequent updates (a restaurant guide).
• A field app with bi-directional data, requiring frequent synchronizations, in which data downloads are updated on the client, then uploaded back to a central server (a tool for insurance adjustors).
• A real-time app requiring an always-on database connection (stock management).

You can probably think of other types of applications as well. But for now I'm going to focus on the third type of app, the one requiring data synchronization. In this scenario, you have the benefit of fresh data and remote updating, but without the uncertainties of a live connection, which can be problematic and expensive. A synchronized app also happens to be one of the specialties of DB2 Everyplace.

Here's a typical example of distributed clients that require frequent synchronizations. You're on a team of biologists investigating genetic mutations as a result of untested radioactive fertilizers being used in cornfields. You and your peers are out in the field (literally) and your job is to document mutations on your PDA. You'll be adding new findings and updating existing ones. But you don't want to duplicate the efforts of your co-workers, so at the end of the day you sync to the central database.

Here's how you might architect a solution:

• Suppose each of you are using a Palm device, with Palm OS of course. Onto each of these devices has been installed DB2e (DB2 Everyplace) Database, DB2e Sync Client, and a small app written in C.
• On one database server, you have a DB2 Universal Database with known species of bugs (actual bugs) and animals. This is a scientific database used by many people, so you don't want to add new tables to it.
• On a separate database server, you have an Oracle database set up to house all of the freaky, mutated species that you find.
• On a middle tier, you've installed DB2e Sync Server. You've also gone ahead and replicated the two source databases, so you have two mirror databases on this server, as well. These are used by the Sync Server to organize the data from the field, keeping the burden off of the source databases.

The actual operation might play out something like this:

• You're in the field. You open up a corn husk and out pops a three-headed grasshopper. Scrambling to avoid its projectile tobacco, you grab your PDA and look up grasshoppers. Finding only the two-headed variety, you add the discovery to your database.
• That night in the trailer, you use a land-line to sync up to the central database. It's an archaic modem, the connection is slow. But fortunately your subscription has been limited by column filters (a SQL statement limiting the columns to which you have access), so you'll be able to sync up pretty quickly.
• The Sync Engine on your PDA detects changes made since the last synchronization. When the client app calls the Sync API, the engine goes through your subscriptions and tables, collecting changed rows. Tonight, this includes the new grasshopper and a couple of updates on previously recorded mutations. These rows are sent to the Sync Server.
• The Sync Server receives these messages and temporarily stores them to a message database. This database, DSYMSGDB, acts as an input/output queue for binary messages received from and sent to remote devices.
• After being validated, the data is sent to another holding place, the staging tables on the mirror database. There it will remain until the next replication cycle.
• Similarly, the Sync Server reviews changes made to relevant data in the mirror databases since the last synchronization and sends those changes to the client. Here, "relevant" is defined by those subscriptions.
• Meanwhile, it finds a conflict for you from your prior sync. It seems that while you tried to update a record on a four-eyed lizard, your compatriot in the next field over was updating the same record. As part of the conflict resolution process, since you both tried to change the same record at the same time, her version was chosen at random and posted to the source while yours was rejected. Bummer.
• At this point, your Palm is synced up. Later that night, after the soothing sounds of foot-long crickets have lulled you to sleep, the Sync Server initiates a scheduled replication. First, it reviews all of the synced data waiting in staging tables on the mirror databases. It also reviews server history tables.
• Replication happens. The source tables receive all the client changes that have been waiting in the staging tables. At this point, your new grasshopper is added to the source Oracle database. Meanwhile, the mirror databases receive both client changes and source changes.
• During this process, any conflicts that exist between the source and client rows are resolved. Basic conflict resolution deems the source database to be correct, though this can be overridden with custom Java classes. Any losers are stored in the mirror databases to be sent as a data set to the offending scientist next time he or she syncs up. Because your custom app recognizes and processes these conflicts, you'll be notified if a change didn't go through, in which case your database will contain the same data as the source. If that programming hadn't been custom written, the conflicts would just be ignored.
• At this point, the source databases are current and the mirror databases reflect the latest data. If you synced up right this moment, then you would have the absolute most current information. But you won't. In fact, you won't ever sync again.
• The next morning, you are awakened by a sharp pain in your left big toe. As you see something scurry into the shadows, you suddenly fall dizzy. When you awake, you've changed in a most extraordinary way.

As I said, a pretty routine scenario. Yours may vary. If your app doesn't require synchronization, then development is pretty straightforward. You download the DB2e Database [http://www-306.ibm.com/software/data/db2/everyplace/edition-db.html], develop the app, test it, and create downloadable files for your clients. In fact, IBM gives you a trial version, so feel free to download it now and try it out.

For more complex applications, it might help to understand more about the different components…

In the DB2 pantheon, DB2 Everyplace would be the God of Mobile Databases. The name DB2 Everyplace, or DB2e, refers to a family of products, each of which include the DB2 Everyplace Database, a small footprint database that fits on your PDA. Below are the different products available.

DB2 Everyplace Database Edition
Includes DB2e Database, a 200-300K database that fits on a variety of mobile devices. Use this when you have a mobile app with a database back-end that doesn't need synchronization or frequent updates.

DB2 Everyplace Enterprise Edition
Includes DB2e Database and the DB2e Synchronization Server and Client. Use this when you have applications in the field that need to be synced with a central source. This architecture is described in the previous scenario. The Enterprise Edition includes a license for unlimited clients.

DB2 Everyplace Express Edition
Essentially the same as the Enterprise Edition except with limited licensing. This package is meant for small- to mid-size companies and is priced accordingly.

DB2 Everyplace Software Development Kit
A full complement of tools for developing DB2e Database and Sync Server apps. Includes tools for .NET, C/C++, WebSphere, Java, Eclipse and more. This is a free download.

The Cloudscape Option You might be surprised to know that in addition to using a DB2e database as your client database, nowadays you can also use a Cloudscape database. Its footprint is a bit larger, 2MB, and it requires 4MB of Java heap memory. So this would be more suited to laptops or other high-end mobile equipment. However, it does give you important features over and above those found in the DB2e database, primarily triggers and stored procedures. In addition to the DB2e features mentioned already, Cloudscape offers: More JOINs (RIGHT OUTER, LEFT OUTER, INNER) HAVING clause UNION, UNION ALL Triggers Stored Procedures FOREIGN KEYs and cascading delete CREATE and DROP schema CREATE and DROP view Database-level encryption Multiple connections with row-level locking

As mentioned, the DB2e database is the client-side database. It can be used on many different PDA platforms:

• Windows CE / Pocket PC
• Palm OS
• Symbian OS
• Embedded Linux
• MIDP devices

With such a small footprint, one might think the DB2e Database would be pretty limited. But it actually gives you a solid set of database operations:

• SELECT, INSERT, DELETE, UPDATE
• Subselect statements and JOINs
• CREATE, ALTER, and DROP TABLE
• CREATE and DROP INDEX
• Multiple column indices
• GROUP BY, ORDER BY, LENGTH, RTRIM, IN
• Aggregate functions like MAX, MIN, AVG, SUM, and COUNT
• Scrollable cursors (especially handy for optimal performance in your handheld app)
• Transactional processing with isolation levels UR, CS, RS, and RR
• Table-level encryption

In a DB2e architecture, the Sync Server sits on the middle tier. Essentially, its purpose is to serve as the intermediary between all those PDAs in the field and the source database. In addition to the DB2e database, each handheld also has a DB2e Sync Client, which negotiates the synchronization with the Sync Server.

In the past, clients would sync directly with the source database. But this limits scalability—obviously, having dozens, or even hundreds, of clients hitting the source database at the same time it's in general use would start to impact its performance.

So the middle tier was introduced. Using a Sync Server model prevents heavy sync jobs from slowing down your source database. But it also gives you a bonus: In addition to multiple device clients, you can use multiple source databases, in fact any database that is JDBC-compliant. And with DB2e V8.2, these databases are available at all times, even during replication with the mirror databases.

Thanks to JDBC operability, you can use a variety of enterprise databases as your source:

• DB2 Universal Database (for z/OS, iSeries, Linux, UNIX, Windows)
• Informix
• Sybase
• Oracle
• Microsoft SQL Server
• Cloudscape

Synchronization works by setting up subscriptions and assigning one or more to various clients. These subscriptions define to what data the user has access. Filters further limit available data via SQL statements stored with the subscriptions, boosting not only security but also performance since less data to sync means less packets over the wire (or through the ethers). (You can also filter columns from table sources, which also reduces data synchronization needs.)

Synchronization happens on demand from the client API. Replication, on the other hand, takes place according to a previously defined schedule, or manually by an administrator. During replication, rows collected during synchronization are applied to the source and mirror databases while changes made to the source since the last replication are applied to the mirror databases, as well. Obviously, these new changes to the source are applied to remote clients during the next synchronization.

To clarify the difference, in DB2e terminology, synchronization means comparing client and mirror databases and moving changes from one to the other. Replication is where changes to the source are moved to its mirror database and changes to the client database are moved to the source.

Conflict Resolution
As previously mentioned, any conflicts between client data and source data, or between two sets of client data, are resolved according to basic rules:

• If the same row has been changed both in the source database and the client database, the source always wins.
• If one client has changed an earlier version of the data and another client has changed a later version, the one which has changed the later version wins.
• If two clients have changed the same version of the data, then one is chosen at random.

These rules may be overridden by a custom Java class. In all cases, winning rows are applied throughout the system while rows that were rejected are stored in a staging table in the mirror database. During the next synchronization, rejected rows are sent back to the client that originally sent them. The device receives this result set via a callback API. But when writing your client application, you must add the code to recognize and handle this data, or else rejects will be ignored.

For a detailed description of the Sync Server set up, read the "IBM DB2 Everyplace Enterprise Edition Installation Notes".

To begin developing a data-driven mobile application, first decide:

• what mobile platform you'll be developing for,
• what development language you'll use for coding,
• and whether you just need a standalone mobile database on the client or a full-fledged synchronization architecture.

Most mobile platforms have developer sites to help you get started. For example, if you want to develop an app for the Palm OS, be sure to read "Introduction to Palm OS Programming" and "Creating a Mobile Application for Palm Devices Using DB2 Everyplace".

An excellent general overview for all devices can be found in "How to set up and execute a mobile project with DB2 Everyplace V8.2".

And lastly, to see what other companies have done with DB2 Everyplace, check out "DB2 Everyplace Success Stories". Perhaps you'll be able to join the list with your own mutant bug-tracking application. But if you find yourself testing it in a radioactive cornfield, do be careful. Trust me on that.

DB2 Everyplace

DB2 Everyplace developerWorks

DB2 Everyplace Forum

DB2 Everyplace Training Modules

Info Center for DB2 Everyplace

Synch mobile apps with DB2 Everyplace and .NET

Horizontal Filtering

Evaluating Replication Engines: A Closer Look at Data Propagator and JDBC Replication

Justin Whitney is a regular contributor to DevX.com and Jupitermedia. He currently lives in San Francisco, where he consults for leading high-tech firms and writes about emerging technologies.