1. technology and computing
2. software
3. databases

How to Set Up and Execute a
Mobile Project with DB2 Everyplace
Part 2: DB2 Everyplace V8.2
Martin Oberhofer
SAP DB2 Everyplace Enablement, Germany
Martino@de.ibm.com
Joachim G. Stumpf
DB2 Technical Presales Support, Germany
Stumpfj@de.ibm.com
December, 2004
Notices and Trademarks
The following terms are trademarks or registered trademarks of International Business Machines
Corporation in the United States, other countries, or both:
DataPropagator
DB2
DB2 Universal Database
Everyplace
Informix
Mobile Notes
Notes
Tivoli
WebSphere
WorkPad
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United
States, other countries, or both.
Microsoft, Windows, Windows NT, Windows 2000 and Windows XP, Visual C#, Visual Basic
and Visual Studio are registered trademarks of Microsoft Corporation in the United States, other
countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Other company, product, and service names may be trademarks or service marks of others.
The furnishing of this document does not imply giving license to any IBM patents.
References in this document to IBM products, Programs, or Services do not imply that IBM
intends to make these available in all countries in which IBM operates.
The information contained in this publication does not include any product warranties, and any
statements provided in this document should not be interpreted as such.
© Copyright International Business Machines Corporation 2004. All rights reserved.
2
About the authors
Martin Oberhofer joined IBM in 2002 as a member of the DB2 Everyplace performance team at
the IBM Silicon Valley lab. Since 2003 he has worked as a DB2 Everyplace consultant in mobile
projects with SAP. He is interested in mobile technologies, Java and Linux. You can contact him
at martino@de.ibm.com.
Joachim Stumpf joined IBM Software group in 1994 first in marketing, and later in presales
technical support for database technology. He was member of teams which wrote the redbooks
DB2 meets Windows NT and Replication in a pervasive computing environment. Since 1999 he
has worked with DB2 Everyplace and was coauthor of the previous article on this topic. He has
developed and taught IBM Learning Services course material on DB2 Everyplace. You can
contact him at stumpfj@de.ibm.com.
3
Table of Contents
1 Introduction .......................................................................... 6
2 Overview of DB2 Everyplace ................................................ 7
2.1 DB2 Everyplace Sync Server.................................................................................. 8
2.2 DB2 Everyplace Synchronization Client................................................................ 9
2.3 DB2 Everyplace mobile database ........................................................................ 10
2.4 IBM Cloudscape database .................................................................................... 11
3 Data flow between backend database and mobile client . 12
3.1 Overview................................................................................................................. 12
3.2 Types of subscriptions ......................................................................................... 14
3.2.1 File subscription ..................................................................................................................14
3.2.2 Custom subscription ...........................................................................................................14
3.2.3 Table subscriptions .............................................................................................................15
3.3 Filtering .................................................................................................................. 18
3.3.1 Vertical filtering ..................................................................................................................19
3.3.2 Horizontal filtering..............................................................................................................20
3.3.3 Join filtering.........................................................................................................................21
3.4 Conflict handling ................................................................................................... 22
3.4.1 Basic conflict resolution ......................................................................................................22
3.4.2 Custom logic.........................................................................................................................22
4 Security questions .............................................................. 23
4.1 Device ..................................................................................................................... 23
4.2 Transport layer ...................................................................................................... 24
5 Programming....................................................................... 24
5.1 C/C++ Applications................................................................................................ 25
5.2 Java Applications .................................................................................................. 26
5.3 .NET applications................................................................................................... 28
5.4 Sync Client – programming considerations........................................................ 28
6 Administration and Monitoring ........................................... 29
6.1 Administration tools and setup ............................................................................ 29
6.2 Regular administrative tasks................................................................................ 30
6.3 Monitoring .............................................................................................................. 31
6.4 Troubleshooting .................................................................................................... 32
6.5 Conflict information............................................................................................... 34
6.6 Duplicating a DB2 Everyplace installation .......................................................... 34
6.7 Disaster recovery/High availability ...................................................................... 35
7 Software distribution .......................................................... 36
7.1 Server ..................................................................................................................... 36
7.2 Clients..................................................................................................................... 36
8 Scalability and availability ................................................. 37
8.1 WebSphere............................................................................................................. 37
8.2 Sync Server servlet ............................................................................................... 37
8.3 First level of scalability ......................................................................................... 38
8.4 Second level of scalability .................................................................................... 38
4
8.5 Availability.............................................................................................................. 39
9 Performance ....................................................................... 40
9.1 Data design ............................................................................................................ 40
9.2 DB2 Everyplace database performance .............................................................. 42
9.3 Sync client performance ....................................................................................... 43
9.4 Sync Server performance ..................................................................................... 43
10 Summary ........................................................................... 44
Bibliography ........................................................................... 45
5
1 Introduction
This article is intended for people who are involved in planning and executing a mobile project
with DB2® Everyplace®. It describes the functional scope of DB2 Everyplace V8.2 and details
how the product works. DB2 Everyplace is a relational synchronization product allowing fast
data synchronization between backend databases and a mobile database running on an
occasionally-connected mobile device, as shown in Figure 1.
Figure 1: Simplified overview of the involved processes
There are many mobile scenarios where such a solution is useful, including sales force
automation, insurance, health care, retail and financial services. The functionality required for
these mobile scenarios includes the following:
•
•
•
•
•
•
Ability to stay current with office (e-mail, calendar)
Ability to automate tasks
Ability to receive alerts
Ability to process fulfillments (orders, service requests, delivery)
Access to business information (orders, inventory, shipments, rates)
Access to peers and experts (help, info, sharing)
The first part of this article is a technical overview of DB2 Everyplace V8.2 covering
6
•
•
•
•
•
•
Features (introducing in a new section on Cloudscape as an option for a client database)
The replication process
The synchronization process
Filtering
Conflict resolution (introducing the new Custom Logic feature)
Security (introducing new options like SSL)
In the next section we give an overview of the available programming options for the client
application development, introducing the new .NET support which was added in DB2 Everyplace
V8.1.4 and was not available in the DB2 Everyplace V7 release.
The second part of this article covers administrative tasks, software distribution considerations
for mobile projects, and troubleshooting hints. Here, we introduce some options for crash
recovery, suggest ideas for duplicating DB2 Everyplace installations for migration testing, and
provide additional hints for problem solving.
The final sections of this article cover scalability options and important performance tuning hints.
2 Overview of DB2 Everyplace
This section describes the main components of DB2 Everyplace and explains how these
components work together.
A full DB2 Everyplace solution consists of a DB2 Everyplace mobile database on the mobile
device, DB2 Everyplace Sync Server with DB2 UDB as the engine for the mirror databases, and
a backend database. DB2 Everyplace Sync Server is responsible for synchronizing the data
between the backend database and the DB2 Everyplace mobile database on the device. The DB2
Everyplace Synchronization Client on the mobile device communicates with the DB2 Everyplace
Sync Server through an HTTP-based protocol.
Below we consider the following DB2 Everyplace components in more detail:
y DB2 Everyplace mobile database
y IBM Cloudscape database (called Derby with V10)
y DB2 Everyplace Synchronization Client
y DB2 Everyplace Synchronization Server with the Mobile Device Administration Center
(MDAC) on the mid-tier system
7
2.1 DB2 Everyplace Sync Server
The Mobile Devices Administration Center (MDAC) is a graphical administration tool for the
Sync Server that allows centralized administration. With MDAC, you manage all mobile devices
and users centrally, so that you can support large numbers of individual devices simply and
easily. It enables you to set up and administer users, user groups and data access, and allows you
to make some adjustments to the replication definition for individual users as necessary. The
MDAC allows you to define subsets of data and files to be accessed by groups of users. Using
MDAC, you can create and maintain the following four important types of objects (details on
objects like subscriptions will follow later):
•
•
•
•
Mobile users (and their associated mobile devices)
Groups of users
Subscriptions (define what data needs to be synchronized)
Subscription sets (grouping container of subscriptions which are assigned to user groups)
The Sync Server accesses this administration information each time a client requests data
synchronization.
The DB2 Everyplace Sync Server (Sync Server for short) enables bidirectional synchronization
between any supported device and backend data sources.
A mobile user can decide which data (based on subscription sets) should be synchronized. Once
the synchronization is triggered, the Sync Client extracts the changed data from the mobile
database based on the configuration information, and calls the transport API to send the data. On
the server side, the Sync Server gets the request, authenticates the client, puts changed data from
the client into the mirror database, and sends any changes which were replicated from the
backend database to the mirror database to the client.
The replication process driven by Sync Server exchanges the data according to the definitions in
the subscriptions between the backend database and the mirror database. A replication is one
process per mirror database. The replication process is asynchronous to the synchronization
processes, meaning it runs independently from synchronization.
The HTTP-based synchronization is well-suited for both wired and wireless networks. DB2
Everyplace synchronization can be described with the following two main steps:
1. First, the clients synchronize their data with the mirror database on the mid-tier server. The
synchronization is triggered from the mobile device whenever the mobile user has a need to
synchronize the data.
2. Second, on scheduled cycles (or on demand) Sync Server performs a replication between its
mirror database and the backend database. The cycle can be defined during subscription creation
(and adjusted later on if the need arises).
Below is a summary of the DB2 Everyplace Sync Server functionality (for a full list see Sync
Server documentation):
8
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Efficient two-way synchronization
o Synchronizes on all platforms with source databases which support JDBC and
triggers
o Uses remote stored procedure support to allow mobile applications to directly
interact with data sources in real time
Support for the following backend databases: DB2 (on all supported platforms),
Informix®, Cloudscape, Sybase, Microsoft SQL Server, Oracle
Support for the following client databases: DB2 Everyplace mobile database, Cloudscape
Sync Server is supported on AIX, Solaris, Linux and Windows platforms
Supports data partitioning for higher scalability and better performance using the mirror
database concept on the mid-tier servers
Centralized administration
o Facilitates grouping and management of users and grouping of data subscriptions
o Manages database definitions, constraints, and indexes and requires no
administration at the device
Supports powerful (horizontal, vertical, join) filter mechanism
Handles distribution of data, files, and applications
Supports access control for device users based on insert, update, and delete privileges
Supports user exit extensions for managing conflicts
Supports 56 bit and 128 bit encryption of all messages exchanged with sync client
Supports SSL as communication channel to exchange messages with sync client on an
encrypted communication layer
Support of table spaces in the DB2 UDB mirror database for better performance
Supports HALB (High availability/Load Balancing) scenarios by using such capabilities
from WebSphere Application Server
These are some of the features introduced in DB2 Everyplace V8.2 for Sync Server:
•
•
•
•
DB2 UDB support for V8.1 and V8.2 was added.
Join filtering functionality was redesigned to support more customer scenarios and higher
scalability in DB2 Everyplace V8.2.
Delete device and reset functionality were separated to allow customers to refresh the
device without losing data changes which were not yet synchronized.
Concurrent synchronization and replication against a mirror database is now supported,
significantly increasing the availability of Sync Server to mobile clients
2.2 DB2 Everyplace Synchronization Client
The DB2 Everyplace Synchronization Client on the mobile device is built around a
synchronization engine that communicates with the DB2 Everyplace Synchronization Server. The
synchronization API is available for C, Java and .NET. Currently the DB2 Everyplace
Synchronization Client uses HTTP over TCP/IP to communicate with the Sync Server. The
Synchronization Client supports DB2 Everyplace or Cloudscape as the client side database. On
the communication layer, it also supports HTTPS over TCP/IP (SSL).
9
The DB2 Everyplace Synchronization Client (Sync Client for short) is available on all platforms
where the DB2 Everyplace database is available.
For usage with Cloudscape there is also a 100% pure Java Sync Client available.
Sync Client offers the following features:
1. Row level based synchronization, always triggered from the Sync Client
2. Synchronization to different target directories (for example, sync to memory cards)
3. Enabling or disabling of subscription sets for synchronization
4. Message encryption/decryption (in addition to using SSL on the communication layer)
5. Resumable sync
6. API for handling rejected records
The concept behind 3. is to allow mobile users to decide which data should be synchronized. By
enabling or disabling subscriptions sets, only the data where the user has a need for
synchronization will be synchronized on the next synchronization. This also contributes to shorter
synchronization time (especially important when you need to synchronize over telephone
networks where you are paying for the connection time).
2.3 DB2 Everyplace mobile database
The DB2 Everyplace mobile database is specifically designed for the mobile and embedded
application environment and has minimal storage requirements. In order to maximize
functionality using the least resources, only functionality most relevant for mobile databases has
been included. For high capacity data storage DB2 Everyplace supports memory cards and micro
drives.
The user is not required to perform any administration for DB2 Everyplace on the mobile device.
The mobile database is available for Palm OS V4 and V5, Windows® CE/Pocket PC, Symbian
OS, Java™ 2 Micro Edition, Linux, embedded Linux, QNX Neutrino and Windows 32-bits
platforms.
DB2 Everyplace implements a subset of the SQL 92 standard and supports the following
interfaces: DB2 Call Level Interface (CLI), Open Database Connectivity (ODBC), .NET and
Java Database Connectivity (JDBC). JDBC is supported on all platforms that have Java
technology support.
The mobile database supports relational operations such as JOIN, GROUP BY, and ORDER BY.
It supports multiple column primary keys, expression functions, aggregate functions, and
constraints. Advanced indexing provides a noticeable query performance boost for medium to
large tables. Scrollable cursors enable easier application data manipulation. With V8.2, DB2
Everyplace database supports transactional processing with the new multiple connection feature
introducing support for isolation levels UR, CS, RS and RR. If multiple connections are used,
locking happens on table level.
10
In addition, the database engine supports Single Byte Character Sets (SBCS), Double Byte
Character Sets (DBCS), and UNICODE where available. It is globalized for the major languages
including English, Spanish, French, German, Italian, Czech, Polish, Brazilian Portugese, Slovak,
Hungarian, Japanese, Korean, Traditional Chinese and Simplified Chinese, Hebrew and Arabic.
For data security reasons, DB2 Everyplace supports encryption on a per table basis.
Below is a short summary of the most important database functionality (the list is not complete):
y
y
y
y
y
y
y
y
y
y
y
y
y
y
y
CREATE, ALTER, DROP TABLE,
CREATE INDEX, DROP INDEX
CHECK Constraints, DEFAULT VALUE, auto increment
Multiple-column primary key and indexes (bidirectional, prefix scanning)
Transactional processing with isolation levels UR, CS, RS, RR (new in V8.2)
INSERT, INSERT with subselect, DELETE, and UPDATE (records)
SELECT, JOIN, LENGTH, RTRIM, IN list, GROUP BY, ORDER BY, scrollable cursor
REORG, LOCK
Aggregate functions (MAX, MIN, AVG, SUM, COUNT)
Read-only media support on 32-bit Windows platforms and Linux
Memory card and micro drive support (maximum database size now 4 GB)
DBCS support on many platforms
Import/Export functionality for all platforms
UNICODE support also for Win32® (Windows 2000, Windows XP, Windows 2003)
Encryption on per table level
2.4 IBM Cloudscape database
If you are intending to use laptop devices or high-end set-top boxes for your mobile solution and
you intend to write the client side application in Java, you might want to consider the IBM
Cloudscape database. Cloudscape is a lightweight, SQL92-compliant database (also supporting a
large subset of the SQL99 features) written in Java. The database engine provides parallel
transactional processing, recovery and is threadsafe. Starting with V10 (Derby), the object
extensions are limited to the level DB2 UDB supports. The database has a footprint of 2 MB and
is able to run with as little as 4 MB of Java heap memory.
In addition to the features listed for DB2 Everyplace, Cloudscape also supports the following (for
a full list see the product documentation):
• CREATE/DROP schema
• CREATE/DROP view
• Constraints and referential integrity using FOREIGN KEYs with cascading delete option
• UNION, UNION ALL
• HAVING clauses
• Joins (RIGHT OUTER, LEFT OUTER, INNER)
• Aggregate functions: MAX, MIN, AVG, SUM, COUNT
• Triggers
• Database encryption
11
•
•
Stored procedures
Multiple connections with row level locking mechanism
If you need any of these features for your mobile application, you should consider using the IBM
Cloudscape database.
3 Data flow between backend database and
mobile client
3.1 Overview
This section is an overview of how information is exchanged using the DB2 Everyplace Sync
Server technology. We cover additional issues around the synchronization process in the filtering
and conflict sections.
Figure 2: Processes in a DB2 Everyplace synchronization solution
The information exchange that occurs with DB2 Everyplace is a two step process (see Figure 2)
and both steps together form a complete synchronization cycle. One part is a synchronization
between the mobile device and the mirror database, the other part a replication between the
mirror database and the source system database:
1. Synchronization between mobile client and mirror database
Mobile users or clients make changes to their local copy of the data. Depending on their needs,
12
they want to synchronize these changes back to the company and receive important updates
related to their work. Through the client application, the mobile user triggers a synchronization.
The sync process starts with the Sync Client authenticating with Sync Server. The Sync Client
extracts the delta since the last synchronization and sends the delta in one or multiple messages
(depending on the size of the data changed) to the Sync Server. Once all changes from the client
are received, the Sync Server queries the mirror database and sends the delta of changes since the
last synchronization back to the client. The changes from the client are stored in the staging
tables until the next replication cycle occurs. If the Sync Server is receiving messages from the
client or preparing them for sending them to the client, you can configure Sync Server to store the
messages in a database (see msg db in Figure 2). Note that with V8.2, the option to store the
messages on the file system is removed.
2. Replication between backend database and mirror database
During subscription creation time, the initial replication between the source and mirror databases
occurs, and the mirror database is populated. Information on subscriptions, groups, users, and so
on is stored in the control database dsyctldb (see Figure 2). Replication is a process per mirror
database. During subscription creation time, you specify a replication cycle. (If you set it to 0,
this means only replication on demand will happen, meaning you must call it from a command
line utility.) The replication performs the following tasks:
a. It applies the changes from the staging tables to the mirror database.
b. It replicates all changes from the mirror database to the backend database.
c. It replicates all changes from the backend databases to the mirror database.
This has the following consequences:
•
•
•
The client will get information on rejected records from previous synchronizations only
when the first synchronization after a replication cycle occurs.
Any changes to the backend database are unavailable to clients unless a replication cycle
moves them to the mirror database.
Any changes from a client are available to other clients only after a replication cycle.
Replication supports n:1 relations between backend databases and client databases (see Figure 3
for an example of three backend databases that synchronize with one client database over three
mirror databases, implementing a 3:1 relation.). The concept of a mirror database was introduced
for higher scalability (see performance section) and to protect the backend database from a heavy
synchronization load which would otherwise affect your backend application performance. This
means that a DB2 Everyplace mobile database can receive data originating from different
sources, and consolidate it in one client database.
Prior to DB2 Everyplace V8.2, on any mirror database at any given point in time, either
replication occurred, or the mirror database was available for synchronization. This restriction is
now removed. Replication on a mirror database can now run parallel to clients synchronizing
against the same mirror database. This improvement increases the availability of Sync Server to
the clients significantly. Another major replication improvement in DB2 Everyplace in V8 is the
fact that replication can now run in parallel to backend applications modifying the source.
13
Figure 3: Replication between multiple backend databases and one client database
Subscriptions can also be used to synchronize files. Other subscription types are available to
define upload-only synchronization directly against the source, to set up custom subscriptions for
remote stored procedures and to use DB2 DataPropagator™ mechanism.
In the following section we look in more detail at the various subscription types.
3.2 Types of subscriptions
The synchronization solution allows the exchange of information in a number of ways. This
information can be data stored in database tables or files. The definition of an exchange for data
or files is called a subscription. You can create three types of subscriptions: file subscriptions,
table subscriptions and custom subscriptions.
3.2.1 File subscription
File subscriptions are needed for example to transfer client applications automatically to the
client. This file can be your application. The term we use for this process is file subscription. File
subscriptions in V8.2 download only to the device and are useful to transfer any file which may
be needed on the mobile device.
3.2.2 Custom subscription
14
Custom subscriptions can be used for stored procedures. DB2 Everyplace includes a remote
query and stored procedure adapter. This adapter enables DB2 Everyplace to use the Sync Server
architecture to call a stored procedure located at a DB2 data source without synchronizing. The
CALL statement supported by the DB2 Everyplace mobile database can be used to invoke a
stored procedure in the DB2 source database using this infrastructure. The results of the stored
procedure are returned directly to the application on the device. With stored procedures, you
have less network transmission, since stored procedures run locally on the DB2 source server.
Additional advantages of stored procedures are:
y
y
y
y
The processing load on client systems is lower.
Code maintenance on client systems is simplified.
The stored procedure is executed in real time against the source database.
The stored procedure can perform non-database related tasks.
For sensitive data you can use a remote stored procedure call. In this scenario, you call a stored
procedure that delivers a result set as a temporary table. This result set is only available to the
application. After the application is closed, the data is no longer available.
Examples for real time data access where the remote stored procedure feature is useful are:
•
•
•
Real time ordering
Real time stock quantity check
Real time server notification
Using remote stored procedures through custom subscriptions has the following disadvantages:
•
•
Client application waits until result of remote stored procedure is returned
Solution is less scalable
Therefore, remote stored procedures should only be used if transactions need to be performed
against the source database.
3.2.3 Table subscriptions
Data subscriptions allow you to make data stored in the tables of one or more database
management systems available to a mobile device. Subscriptions use replication mechanisms
such as DataPropagator or JDBC subscriptions to achieve this function. Subscriptions use the
concept of a mirror database. This makes information exchange independent from the backend. It
keeps unpredictable loads away from operational systems. Data will be retrieved from the source
to the mirror and sent back from the mirror to the source at replication time. Upload is a special
subscription to sync directly to the source. However, this subscription only allows insert
operations during synchronization with the source database.
15
3.2.3.1 Upload subscription
Upload subscriptions should be used when you only want to send data from the mobile devices to
the source database. No changes to the source database from other programs are expected. No
mirror database exists for upload subscriptions. Data will be directly inserted to the source
database. No data rows changed on the device by update or delete SQL statements are
synchronized. Upload subscriptions could be used for example with a barcode reader system at a
supermarket to send information to a source database.
Upload subscriptions need no additional tables on the backend database. The table on the device
will be created during the first synchronization. A DB2 Everyplace specific column on the table
controls the synchronization status of each row (shown in Figure 4).
Figure 4: Upload subscription
3.2.3.2 JDBC subscription
Figure 5 provides an overview of how JDBC subscriptions are implemented on the server side.
There are some tables specific to each table on the source side to store history information.
Additionally you have one table for information on all subscribed tables on the backend database.
This table contains for example information about the suffix of the table specific additional
tables. There is a similar situation on the mirror side. There are three table-specific tables and
some additional tables per mirror database. These additional tables either manage replication,
contain static mapping information, or handle client synchronization-specific information.
16
Figure 5: JDBC subscription
JDBC subscriptions use triggers (update/insert/delete) to get information about changes in the
source or mirror database. A trigger is a set of actions that are defined for specific events and will
be executed when the defined event occurs. With JDBC subscriptions your data sources can
include DB2, Informix, Cloudscape, Oracle, Microsoft SQL Server, Sybase or any JDBCcompliant database that supports triggers. Sync Server calls a replication process according to
the time setting in the JDBC subscription to exchange data between the mirror database and the
source database.
3.2.3.3 DataPropagator subscription
In some situations, data propagation techniques are appropriate rather than triggers, because
triggers are part of transactions. DataPropagator is included as the replication function in DB2 on
the distributed platforms (Linux, UNIX® and Windows). On OS/390 DataPropagator is an extra
feature. DB2 Everyplace Sync Server has adopted it as a proven method to replicate data
between source and mirror databases. For this environment, a special configuration of
DataPropagator is used which is called update anywhere. The source table is the base or master
table for the process. Tables created at the mirror database side during subscription definition are
treated as replicas.
In V8.1.4 the administrator is required to set up the DataPropagator subscription in the DB2
Replication Center and then create the DB2 Everyplace subscription and associate it to the
DataPropagator subscription using the XML tool (dsyadminxml.bat). With DB2 V8.2 and DB2
Everyplace V8.2, both the DataPropagator and DB2 Everyplace subscription can be created using
the XML tool, eliminating the need to use the DB2 Replication Center.
During mobile data synchronization, the mirror and backend databases can serve both as source
and mirror databases in replication terms. DataPropagator replicates client changes from the
mirror to the remote database and also replicates changes from the backend database to the mirror
database.
17
This processing is done with the DB2 DataPropagator Capture and Apply programs. Capture runs
on the source side. The Capture and Apply processes on the mirror database will be invoked by
Sync Server. Figure 6 shows a rough outline of these processes:
y Replication needs it's own database specific control tables which use per default the
Schema 'ASN' in each database.
y Each table which is included in a replication scenario needs to be registered and needs its
own History table (CD..). This table is necessary for the capture process to write changes
made to source.
y In an update anywhere scenario, we define one table we want to replicate as master or
base table. This table resides in Figure 6 in the backend database. The table in the mirror
database as counterpart is named Replica .
y The Apply process handles the exchange of data between backend and mirror database.
y Integrating Data Propagator replication to an DB2 Everyplace scenario adds additional
tables with the schema 'DSY' to the mirror database. These tables contain information similar
to that in the tables in the mirror database of a JDBC subscription.
Figure 6: DataPropagator subscription
3.3 Filtering
DB2 Everyplace Sync Server includes several filtering options to support horizontal, vertical and
join filtering methods. Filtering data from the server is an important part of controlling the data
available to the client. Filtering can also help control client database size by synchronizing the
minimum amount of data necessary for a specific client.
Figure 7 shows a scenario with different filtering options. From backend to mirror, only rows
with selection criteria 'A' in first selection column are replicated to the server. This is called
horizontal filtering. During synchronization to the client ,vertical filtering reduces the table by
one column, in this case the last column of the table. Additionally horizontal filtering reduces the
18
number of rows, synchronizing only rows with selection criteria '1' in the second selection
column to the first device.
Filters can be defined at the subscription, group or user level. There are dependencies between
the levels. Filtering always starts at the subscription level. You add a table to a subscription and
then you can apply vertical filters by selecting the columns you need to synchronize. In addition,
you can specify horizontal filtering, further reducing the amount of data replicated to the mirror
database, or you can add filters with parameters. For the parameters, you can set the defaults on
the group level and it can be overwritten if you specify a specific value on the user level during
the definition of the user.
Figure 7: Horizontal and vertical filtering
3.3.1 Vertical filtering
Vertical filtering can be applied on replication and synchronization. You can configure this
during subscription creation. If vertical filtering is applied to the replication process, it improves
performance by reducing the amount of data replicated to the mirror database. It also reduces the
amount of data synchronized to any user device, depending on the definition.
Vertical filtering is defined during subscription definition by unsubscribing columns. An
unsubscribed column is a column that is replicated but not synchronized to the users. Data
inserted to the data source from the client will be composed of the columns of data on the client
combined with default column values defined for the group to complete the rows of data inserted
to the data source.
Also, vertical filtering can be used as a security mechanism by restricting the availability of
specific columns to specific users.
19
3.3.2 Horizontal filtering
Horizontal filtering can be accomplished at the subscription level, group level or user level as
shown in Figure 8. The first level of horizontal filtering is applied on the subscription level. If no
additional filter restrictions is applied, each user would get all the rows passing the filter
condition on the subscription level. If additional filters are applied on the group level (as you can
see in Figure 9), then the number of qualified rows is further reduced. Finally, if you apply filter
conditions on the user level, they further restrict the number of rows sent to the device. Where
you do not apply any filter conditions, users will receive all rows passing the filter criteria of the
group where the users belong.
Figure 8: Different Levels where filters can be applied
Horizontal filtering uses a Standard Query Language (SQL) WHERE clause to select data from
the source data. In Figure 9, filters are applied on different levels to reduce the overall amount of
data received on the mobile device:
20
Figure 9: Horizontal filter example
Horizontal filtering is useful for:
•
•
Protecting your data: Only the rows needed are synchronized to the device. This reduces
the amount of data which might be compromised if the device is lost or stolen.
Improving synchronization time: A reduced amount of data which needs to be transferred
over the network reduces the time required for synchronization. This is especially
important on low bandwidth networks.
3.3.3 Join filtering
This filtering method refers to the use of the WHERE clause containing a subquery that accesses
another replicated table (see Figure 10) of the same subscription. Join filtering was significantly
enhanced in DB2 Everyplace V8.2, removing almost all the restrictions of the previous releases,
improving performance, and providing support for more filter conditions of this type.
The sample in Figure 10 shows all the levels where you add filter information for a table.
Specific for Join filtering is the select string in the WHERE clause of the subscription on the
business table. It delivers a list of zip codes out of the ZipTab table. With the list as selection
criteria we enter the business table and deliver during synchronization only rows which match the
selection criteria. So we get only two rows of the business table on the client device, because
these are the only rows that have a matching zip code in the column Zip.
21
Figure 10: Join filtering
3.4 Conflict handling
If possible, you should avoid conflicts with your mobile solution design. The DB2 Everyplace
Synchronization Server on the mid-tier system handles conflicts during each replication cycle.
3.4.1 Basic conflict resolution
DB2 Everyplace handles or detects conflicts at the row level.
There are two types of conflicts:
y Conflicts between changes at the source and changes on a mobile device
y Conflicts that occur when at least two clients modify the same row within the same
replication cycle
For the first type of conflict, the default behavior rejects the client change, and the source update
is sent to the device. In the second case, by default the Sync Server resolves the conflict based on
the row version which is used by the Sync Server to keep track of the latest row versions and
obsolete row versions. In this case the client with the newer row version wins. Since conflict
resolution happens during replication, only on the first synchronization after the next replication
will the clients get the information regarding rejected rows. The client whose updates were
rejected receives, for each rejected row, the rejected row and the new row. The default of the
Sync Client API is to ignore the conflicts, meaning the new row is applied to the database, and
the rejected row is not processed further. However, if the Sync Client Callbacks are used, then
you can change this behavior by applying the old rejected record again to the database.
3.4.2 Custom logic
22
The conflict resolution rules can be overwritten using the new custom logic feature. This feature
allows you to write Java programs with new rules and tasks for conflict resolution. Your Java
program can be called before, during or after synchronization or replication. Custom logic creates
an overhead on synchronization and replication because it is called for each conflicting record.
Custom logic is available only for JDBC subscriptions.
4 Security questions
Data security is a major issue in the industry. Data must be secured against unauthorized access.
The database administrator must have the ability to control what data the mobile user can see or
change on the mobile device.
For a wired or wireless mobile solution with DB2 Everyplace, you have four points where
security issues must be considered: device, transport, synchronization server, and the source
server. We will not consider the security issues on the synchronization server or between the
synchronization server and the database source server because the data handling and transfer
between two DB2 installations is under the control of DB2's security mechanisms. So we will
just consider the security issues for the device and the transport layer in more detail.
4.1 Device
When controlling access to the local database you have three factors to consider:
y
y
y
Who will access the data on the mobile device?
What will be accessed?
Which type of access is allowed?
A single device normally belongs to a single mobile user. Lost or stolen device data is protected
by native device security functions (username, password). Access is protected using a username
and password for each user.
Group and user access is controlled by the MDAC tool on the Sync Server. Sync Server users
can only access the data subscriptions they are assigned and authorized to receive.
The Mobile Devices Administration Center allows you to define synchronization subscription
sets for groups of users who have the same needs for access to applications and data.
With the MDAC tool on the Sync Server, groups of users can be defined and each group can be
given access to different applications and data records according to the security level for the
group. For each group and user, changes are based on the user privileges for insert/update/delete
of the data tables as defined with the subscription object.
Additionally the access of data by different user groups can be specified using filter techniques.
Filtering is a method to partition data in such a way that a mobile user receives only the rows and
columns where a "need to know" exists. Applying filters to limit the data to the set where a "need
to know" exists also reduces the amount of data which might be compromised in case the device
is stolen or lost.
23
Additional protection on the mobile devices is accomplished by the DB2 Everyplace mobile
database using data encryption which can be set on a per table basis. If you use the IBM
Cloudscape database, you can also use database encryption to protect your data.
4.2 Transport layer
The data communication between device and server can be protected using various techniques:
y
y
y
Encryption of the messages exchanged between sync server and client
Encryption of the communication layer using SSL between sync server and client
Synchronizing through proxy configurations hiding the true location of the server
These techniques can be combined. For example, you can use message encryption parallel to
using SSL, in which case the encrypted messages are sent through a protected communication
layer.
For message encryption, the Sync Server supports across-the-wire data encryption using the
symmetric (shared secret) key encryption algorithm: the DES (Data Encryption Standard)
solution. The encryption level can be 56-bit or 128-bit data encryption standard.
The encryption level is defined when the subscription object is created with the MDAC tool on
the Sync Server. DB2 Everyplace uses HTTP for transporting the data for synchronization;
therefore, a firewall and a special port can be set up for synchronization purposes.
The data communication is compatible with existing network firewall and security technology
solutions, so the communication channel can additionally be protected using a Point-to-Point
Tunneling Protocol (PPTP) or a Virtual Private Network (VPN) connection. VPNs use advanced
encryption and tunneling to permit organizations to establish secure end-to-end private network
connections over third-party networks, such as the Internet or extranets.
Note that using security features like encryption will have a performance impact on your system.
The more features you use, the higher the impact will be.
Websphere Everyplace Communication Manager (WECM) is an IBM product you can use for
managing connections to mobile devices using VPN technology.
5 Programming
Since products are evolving fast (and DB2 Everyplace is no exception), we will not list the
precise version of any plugin or integrated development environment (IDE) supported in this
section, because it will be outdated very soon anyway. If you need to know if your version of an
IDE is supported or which DB2 Everyplace plugin versions are available for it, please see the
DB2 Everyplace Application Developer Guide (listed in the bibliography) of the latest DB2
24
Everyplace version for this information. The same will be true for the operating system versions
where we also just name the platform.
DB2 Everyplace supports applications written in C/C++, Java and .NET for the following
platforms (.NET only on Microsoft platforms): Palm OS, Symbian, Windows CE and PocketPC,
Win32®, QNX Neutrino, Linux and embedded Linux. You can test the applications on most
platforms with device emulators which are usually delivered with the development kits.
DB2 Everyplace implements a subset of the SQL 92 standard, DB2 Call Level Interface (CLI),
Open Database Connectivity (ODBC), .NET, and Java Database Connectivity (JDBC). JDBC is
supported on all platforms that have Java technology support. The IBM Synchronization Client
on the mobile device provides APIs to access synchronization functionality directly from a
program on the device.
DB2 Everyplace mobile database is available with the SDK (download location is listed in the
bibliography) for evaluation purposes.
Now let us take a look at the methods for developing applications.
5.1 C/C++ Applications
The basic steps to develop a DB2 Everyplace database application in C/C++ application are:
1. First install DB2 Everyplace on the development workstation.
2. Define the application and its data requirements.
3. Determine what data the end user will need to see or change and how that data will be
retrieved, stored, and updated in the DB2 Everyplace database.
4. Understand the DB2 CLI/ODBC interfaces and determine what DB2 CLI/ODBC functions to
use in the application.
5. Write a C/C++ application program using the DB2 CLI/ODBC functions supported in DB2
Everyplace mobile database and Sync Client.
6. Prepare, compile, and link the application code with the DB2 Everyplace header files and
platform libraries.
7. Test the application on an emulator or device.
It is not uncommon to test the application on the desktop first before deploying it to the device
for performing final testing there.
To write your C++ application you can use the following tools on these mobile platforms:
•
•
•
Palm OS (for example using IBM WorkPad®)
o GNU Software Developer Kit
o Metrowerks Codewarrior for Palm Computing Platform
Symbian OS Version (for example using Nokia 9210)
o Microsoft Visual C++, together with the EPOC or Symbian C++ Software
Developer's Kit (SDK) for Symbian OS
Windows CE (e.g. Compaq(R) iPaq Pocket PC)
25
•
•
•
o Microsoft eMbedded Visual Tools
Win32 (Microsoft Windows NT or Windows 2000)
o Microsoft Visual C++
QNX Neutrino
o Metrowerks Codewarrior for QNX Neutrino or QNX Neutrino SDK
Linux and embedded Linux (for example Compaq iPaq)
o Embedded Linux distribution's cross platform development tools. The embedded
Linux kernel should have support for ELF (Extended Link Format) binaries enabled.
Embedded Linux is the most advanced embedded operating system solution for the
consumer electronics industry.
In Figure 11 you see on overview of how the C++ Command Line Interface (CLI) is processed
for DB2 Everyplace.
5.2 Java Applications
DB2 Everyplace mobile database supports a subset of the JDBC methods of the JDBC 2.0 and
JDBC 3.0 standards. With DB2 Everyplace V8 there is now also a Java API for the
Synchronization Client available.
There is a DB2 Everyplace plugin available for WebSphere Studio Device Developer for rapid
application development. Using the WebSphere Studio Device Developer allows you also to use
the IBM J9 JVM, a J2ME compliant JVM which is available for Palm OS, Windows CE and
PocketPC, QNX Neutrino and embedded Linux. By Using WSDD and J9 JVM, you have the
following advantages:
• Remote debugging of your application running on J9 JVM on your mobile device from
your desktop or laptop computer
• Java performance tuning tools such as MicroAnalyzer and SmartLinker integrated in
WebSphere Studio Device Developer
26
Figure 11: Overview CLI processing for C/C++ applications
•
•
•
•
J9 as runtime environment with advanced options like ahead of time compilation (AOT)
among others for premium performance
Built-in update functionality for J9 JVM
A J2ME compliant JVM which can be used as cross platform application runtime
environment reducing development and testing efforts
SWT package available for GUI development
The basic steps to develop a DB2 Everyplace database application using Java are:
1. Import the appropriate Java packages and classes (java.sql.*) and load the DB2
Everyplace JDBC driver class (com.ibm.db2e.jdbc.app.DB2eDriver) in your Java application.
2. Connect to the database by specifying the location with a URL (as defined in Sun's JDBC
specification and using the DB2 Everyplace subprotocol).
3. Pass SQL statements to the database, receive the results, and close the connection.
4. After writing your program, compile it as you would any other Java program. There are no
special pre-compile or bind steps required.
27
5.3 .NET applications
If you intend to write .NET applications, you probably want to use Microsoft Visual Studio .NET
2003. Depending on your target platform, you either use the .NET Standard Framework for
Win32 operation systems (Windows 2000, Windows XP) or the .NET Compact Framework for
PocketPC OS. ADO.NET, the classes for accessing databases using .NET technology, is
important for your application development using DB2 Everyplace mobile database. If you use
Microsoft Visual Studio .NET 2003, there are multiple programming languages available for you
such as Visual C#, Visual Basic .NET and others. You can pick the programming language you
are most familiar with to develop your application.
The basic steps to develop a DB2 Everyplace application are:
1. Create a new project by selecting your programming language and selecting the type of
application you intend to create (Windows Application for Win32 operating systems,
Smart Device Application if you write an application for a PDA solution running a mobile
OS from Microsoft).
2. Add the namespace libraries with the option Add reference in the solution explorer view,
and then add the required binaries of DB2 Everyplace mobile database and sync client
using the option Add --> Add existing item in the same view.
3. Import the required DB2 Everyplace namespaces in each class where you need them. For
example, in C# for the .NET Standard Framework it would be:
a. using IBM.Data.DB2.DB2e,
b. using IBM.Data.Sync
c. using IBM.Data.Sync.DB2e
for DB2 Everyplace mobile database and sync client. Write your code, compile it and
deploy it using Microsoft Visual Studio .NET 2003 to your target device.
5.4 Sync Client – programming considerations
This subsection is intended to briefly outline what you need to take into consideration when you
develop the part of the mobile application related to Sync Client.
• Option to trigger sync from the application GUI: Without such an option, the mobile user
cannot trigger synchronization. Make sure you start the synchronization in an own thread;
otherwise your GUI will not respond until synchronization completes. This is especially
important if you intend to provide cancel functionality (see next item).
• Option to cancel synchronization: If for a reason the mobile user must abort
synchronization this is a very useful option.
• Synchronization progress bar: Synchronization is an “invisible” process for the user. By
handling the synchronization events you can implement a sync progress bar for the sync
progress events. This provides feedback for the user to see approximately how far the
synchronization has proceeded.
• Synchronization of different target directories: By using this feature of sync client you can
specify to which destination the Sync Client should synchronize the data. You can pick a
different location for different subscriptions.
28
•
•
Option to enable or disable subscription sets: If you provide the option to the mobile user
to decide which data based on the subscription sets should be synchronized you can
achieve:
o Shorter synchronization times (for example the mobile user can sync subscription sets
containing data with daily changes each day, whereas data with weekly changes in
another subscription set is enabled only once a week for synchronization)
o Lower costs if synchronizations are short in case you sync over telephone networks
where you need to pay connection time
o More clients can synchronize against Sync Server if the data load per client is smaller
because less resources are needed by the Server to process each sync request.
Options for the mobile user to set Sync Client properties: Implementing options for the
user to set different Sync Client settings you will be able to achieve for example:
o Higher service ability if trace can be turned on
o Better performance: Adjusting messagesize and timeout properties to reflect
bandwidth and quality of your network (compare for example a 100Mbit Ethernet
connection to a GSM phone network connection) you can tune synchronization
performance and recovery speed.
For an extensive overview how to implement Sync Client functionality please take the tutorial
“Synch mobile apps with DB2 Everyplace and .NET” available on developerWorks at
http://www.ibm.com/developerworks/edu/dm-dw-dm-0409oberhoferi.html?S_TACT=104AHW11&S_CMP=LIB%20.
6 Administration and Monitoring
The basic idea in terms of administration in a DB2 Everyplace environment is to have no
administration required on the clients. In this chapter we discuss issues you must consider when
you plan to implement a DB2 Everyplace environment. You must set up the environment and
perform some administrative tasks on the mid-tier server. Also, we include hints to help you with
monitoring and troubleshooting.
6.1 Administration tools and setup
The Mobile Device Administration Center (MDAC) is the central GUI tool to administer the
whole environment. Additionally, there is a command line tool (dsyadminxml.bat) which allows
you to read and apply the definitions. The definitions read from the control database dsyctldb are
stored in an xml file.
29
Figure 12: Mobile Device Administration Center (MDAC)
Each MDAC object has a tag associated with it which can be used to write the definition files.
The tool can also be used to extract all definitions from the control database, or to duplicate a
given configuration by extracting the configuration definition from one machine and applying it
to another.
Synchronization objects are users, groups, subscriptions, subscription sets, and adapters. A user
can belong in one group only. A group can have multiple subscription sets, and each subscription
set can be associated with different groups. Each subscription can be included in different
subscription sets.
Subscription objects are tables and files which allow clients to get and exchange information.
For the main administration tasks, you use the Mobile Device Administration Center. This is the
GUI tool where you perform most of the definitions. There are a few exceptions where you can
use provided scripts. You will find many of these utilities and programs in the directory “<DB2
Everyplace path>\Server\bin". When you want to duplicate the information you can use the
dsyadminxml tool to read and apply definitions.
There are additional properties which you can change either by using the dsysetproperty.bat
tool or the dsyadminxml.bat tool. These parameters include for example the number of
concurrent connections and the data type mappings as well as parameters for other tasks.
6.2 Regular administrative tasks
Because the amount of data increases over time, and you need to be able to recover in the event
of a problem with your database, you should perform some administrative tasks on a regular
basis. These tasks include the following:
• RUNSTATS/REORGCHK/REORG
These are tasks that DBAs commonly run to improve performance. The optimizer in DB2
requires certain statistics to make the right decisions when determining an access path.
When data changes frequently rows tend to lose their sequential order. In that case the
table may need to be reorganized to order the rows by the desired key. The need for
30
•
REORG can be checked from time to time using REORGCHK, and RUNSTATS can be
run to update the statistics in the catalog tables. These are tasks that can be scheduled on
a regular basis. With V8 of DB2 UDB you can use online tools to perform these tasks.
BACKUP/RESTORE
The recovery strategy must be carefully planned. DB2 installed out of the box performs
recovery only on the transaction level. This capability does not help if a server crashes.
Then you need the backup and log files to recover. DB2 offers several strategies that you
should consider so you can choose the strategy that meets your needs and works with your
acceptable down times. For those who require no down time, we describe a high
availability solution in section 9.
6.3 Monitoring
6.3.1 Basic monitoring information
DB2 Everyplace enables monitoring by writing certain events to the table DSY.LOG in the
control database DSYCTLDB. This database is created during Sync Server installation. The
following events will be written to this log table:
•
•
Start and end of a replication cycle
Begin and end of a synchronization step for each client
For basic monitoring you can use MDAC by reviewing the object named Logs. The Logs object
show the content of the DSY.LOG table.
There are several places to look at when you want to get information about your system running
DB2 Everyplace:
•
•
•
•
Table DSY.LOG:
DB2 Everyplace enables basic monitoring by writing certain events to the table
DSY.LOG in the control database DSYCTLDB. This database is created when you
install the Sync Server of the DB2 Everyplace Enterprise edition.
The following events will be written to this log table:
o Start and end of a replication cycle
o Begin and end of a synchronization step for each client
Table DSY.SESSIONMONITOR:
This table is located in each mirror database. It has only one entry per client and database
subscription. This entry always shows the subscription the client is currently working on
or has most recently worked on.
File system:
You should be aware of some places outside the control database where vital information
is stored:
Sync Server writes exceptions and other trace information to three files. The files are
DSYXXXX.trace (XXXX = number) and syncadapterinit.log. All files
are located under “<DB2 Everyplace path>\Server\logs\<Servername>”.
31
•
Web application and HTTP server:
For environments with a large number of users you should add an application server to
host the Sync Server servlet. This server drags an HTTP server along.
o The IBM HTTP server has its own log. This log gives information about the
clients, too. (see <IBM HTTP server>\logs\Access.log)
o The WebSphere application server itself also writes a logfile into the directory
<WebSphere>\appserver\logs\.
6.3.2 Advanced synchronization monitoring
DB2 Everyplace V8.2 introduces a new Sync Client Trace Upload feature. This new capability
has a Sync Server part and a Sync Client part. During synchronization, the Sync Client engine
writes information to three tables, which are then propagated to a source database called
DSYCSTAT on the mid-tier system through an upload subscription. This UPLOAD subscription
DSYCLIENTSTAT in the subscription set DSYCLIENTSTAT_SET must be assigned to all
user groups for which you intend to collect the synchronization information. The three tables in
the server source database contain the uploaded client information for traces, status, and statistics
of all synchronizations. The sample synchronization report located in
<DSYINSTDIR>\Server\syncreport uses only two of the three tables: status and statistics.
You can either modify the existing report in the above directory or write your own reporting
infrastructure by querying the database DSYCSTAT.
The sample synchronization report provides features ranging from administration to performance
monitoring and optimization. A synchronization report enables a DB2 Everyplace administrator
to manage all aspects of synchronizations including:
•
•
•
Administration
o User and device management: usage reports and synchronization statistics
Performance management
o Performance monitoring and optimization
Data recovery and error reporting
o Manage and administer synchronization failures to ensure data integrity
o Track errors and collect traces to assist troubleshooting and identify different types
of errors
6.4 Troubleshooting
In this section we describe what you can do in case you encounter problems.
6.4.1 MDAC object error situation
If an error occurs during subscription creation, there are two cases to distinguish as outlined in
6.4.1.1 and 6.4.1.2.
32
6.4.1.1 MDAC error situation during definition
If you receive an error during subscription creation, check the database connectivity first. For
more information check the files dsyadminXXXX.trace (XXXX = number) in the directory
“<DB2 Everyplace path>\Server\logs\<Servername>".
6.4.1.2 MDAC error situation during modification
Prior to editing a subscription, you should block it first. Otherwise clients might synchronize the
subscription when you apply the change, leading to unpredictable results. When this happens, it
might be the best solution to drop and recreate the subscription. Thus all internal structures for
this subscription are regenerated and any partial information from the clients related to this
subscription is deleted.
6.4.2 Sync Server traces
When you analyze error situations, first check the log information using MDAC. Other base
information about the servlet environment is stored under
"<DB2e>\WAS\logs\<IBMDB2eServer>". Addtional trace files are available in the file system
under "<DB2 Everyplace path>\Server\logs\<Servername>":
y dsyXXXX.trace (XXXX = number) contains sync and replication trace information
y dsyadminXXXX.trace (XXXX = number) contains MDAC and dsyadminxml tool trace
information
y syncadapterinit.log contains exceptions from Sync Server
If you need more detailed trace information, invoke the DSYTRACE utility. The command
DSYTRACE 1 will turn on the trace utility. All trace messages will be logged to the
dsy0000.trace file and to the console window. There are additional options available. You
can also set the trace parameters in the file DSYGdflt.properties. You will find the file in
the properties subdirectory of the server. The timestamp in the trace file is not automatically
converted, but there is a tool provided which does this offline.
6.4.3 DB2 database messages
DB2 error information is logged in two places:
• One for administrators called the Notify log. You will find this information in the event
viewer of Windows or under "<instancehome>\sqllib\db2dump" as a file with an
extension ".nfy". The level of information can be set in the database manager
configuration.
• The second file containing more in-depth information is db2diag.log. The
db2diag.log records DB2 errors and, depending on the DIAGLEVEL, warnings and
33
informational messages. The db2diag.log is an ASCII file and is located in the
directory specified by the DIAGPATH database manager configuration parameter.
6.4.4 Client access
When a client has problems during synchronization you first should check the following:
• Did the client ever reach the server?
o Look for error messages on your client device. If you see “6XX” errors, that
means there are communication problems. Write down the error for use in further
investigation.
o Verify that the Sync Server servlet is running. This can be checked with a Web
browser typing the following address: ‘http://<Serverip:port>/db2e/db2erdb’. The
servlet should answer. If it does, the servlet is running and you can end your
discovery for the moment. Otherwise you must access the server and use MDAC
to go ahead to the next step.
o Check entries in the synchronization object “Logs” in the MDAC. When you are
able to find an entry for the client in the timeframe matching your synchronization
time you can skip the next bullet.
• Does the user id exist and belong to a group that is enabled?
o Check if the client really exists under the “Users” object in MDAC.
o Check that the user belongs to the group.
o Check to see if the user is enabled. This is shown in column in the “Users” object.
You also should see the device type.
o Check to see that the group is enabled. You can check this in the “Groups”
object.
Increasing the timeout parameter on the client device in the advanced settings panel can often
solve communication problems if they have been caused by an unstable transportation layer
between the client device and the server.
6.5 Conflict information
Conflicts will be logged in the DSY.LOG table. See also chapter 3 for the different types of
conflicts and how to change the default conflict resolution behavior.
6.6 Duplicating a DB2 Everyplace installation
Scripts are a fast and easy way to duplicate installations. For duplicating installations, there are
basically two methods:
• Duplicating an existing installation on another machine using the same version of DB2
Everyplace
• Duplicating an existing installation on another machine using a different DB2 Everyplace
version. This could be the case if you want to test a new DB2 Everyplace version with your
existing configuration prior to installing the new DB2 Everyplace version on your
production system.
34
The first option is relatively easy to achieve by making backups of all involved databases and
restoring them on the same system. In addition, you need to extract your Web server settings and
apply them to the Web server on the new host. Finally, make sure that the database password for
Sync Server is updated accordingly in the DSYCTLDB. This method can not be applied for the
second option because in the database there are internal tables for Sync Server with structures
which might have changed on the new release. The second option is described in full detail in the
DB2 Everyplace Performance Tuning Guide (see bibliography).
Here are some additional hints for duplicating your DB2 Everyplace installation:
y DB2 can be installed with a response file which includes environment variables that should
be set up front. If you generate a file that contains the db2set command, this process will also
be used to set the DB2 environment variables.
y Database schema for DSYCTLDB is provided. You will find it with the filename
dsyctldb.ddl.
y If you use WebSphere, there is a utility with the name XMLConfig to export the
configuration into a file that stores the information as XML. You can use the utility afterwards to
import the configuration again or start and stop the application server for database backup
purposes (the utility is case sensitive).
6.7 Disaster recovery and high availability
Because DB2 Everyplace relies on DB2, the same mechanisms for availability that apply for DB2
UDB apply here. We only have one additional challenge. A DB2 Everyplace solution has a
minimum of three databases (msg db, dsyctldb and mirror db) and many clients that must be
synchronized after a crash recovery. So you should plan for disaster recovery, high availability,
or both.
Disaster recovery:
•
•
•
Run database with archival logging.
Use a user exit to save the archived logs.
Use log mirroring additionally to save log files.
Normally it is up to the administrator to monitor for a crash and to take action. In addition you
can use monitoring software such as Tivoli to monitor databases or the whole machine. The
scenario affords manual intervention to set up a second machine where you restore the backup
and apply log files for the database.
High availability (HA):
High availability on the database side forces you to buy additional platform-specific products and
a bit more hardware. You will need have a second machine, a separate disk unit, and a second
network adapter.
35
•
•
With V8 of DB2 UDB you can use HADR feature of DB2 to reduce the number of
transactions you lose in a disaster recovery case to zero and run an additional image on
the second machine which applies the logs. To reach high availability you also need a
heartbeat monitor. If you want this level of availability, you will need high availability
software such as HACMP for AIX.
You can also just use high availability software to get a high availability cluster. There is
a difference in the recovery time only. The HA software is used to manage a DB2
resource group. This resource group is similar to a DB2 Instance. It consists of resources
like disks with data and configuration directory and an IP address.
7 Software distribution
In the following section we will discuss the methods used for distributing and configuring the
DB2 Everyplace software. When you want to deploy either the base software or the scripts
needed to run a DB2 Everyplace scenario, you need to differentiate between two targets:
•
•
The server, where DB2 Everyplace Sync Server, WebSphere and DB2 will be deployed
The clients, where the DB2 Everyplace database and the sync client will be deployed
7.1 Server
On the server side we will consider a number of tasks:
•
•
The distribution of software including DB2 and WebSphere
The distribution of definitions and configurations including instances, databases and their
configurations, database objects such as tables, and WebSphere configurations
The base software is standard distribution work. For the configuration we have to refer to the
product documentation. WebSphere uses XML to apply definitions. DB2 definitions can be
accomplished partly during installation. Other definitions and configurations can be applied as
batch procedures. DB2 Everyplace can be configured using the dsyadminxml tool.
Various mechanisms are available for software distribution, such as SMS for Windows
environments or Tivoli software distribution for mixed environments. With these mechanisms
you also will be able to distribute scripts to:
•
•
•
•
Create the mirror databases
Configure the databases and database manager
Configure WebSphere (XMLConfig -import <file> -adminNodeName ...)
Configure DB2 Everyplace with the XML file and tool
7.2 Clients
36
On the client side, the software distribution mechanism available will depend on the device
platform. Sync software like HotSync for Palm devices or MS ActiveSync for PocketPC and
WinCE can be used to copy files to devices or you can use hardware backup modules provided
by the hardware vendors. In both scenarios you must customize the client afterwards for
synchronization.
DB2 Everyplace V8 offers additional features such as autodeploy to distribute updates of its own
code to the clients. So you must deploy the code only once as described above. All additional
changes in the code can be delivered by our own synchronization.
For Windows clients you can zip the database including the code to send it or provide it for ftp.
You must personalize the configuration internally before you provide it or the clients will be
required to do it. When you customize upfront you can synchronize the first time to add the data
and zip it afterwards. An application for DB2 Everyplace can be provided via file subscription
with the DB2 Everyplace sync mechanism.
IBM and other vendors offer additional software to deploy software to devices and manage the
devices. These tools, such as WEDM (Websphere Everyplace Device Management), are
available under WESD (Websphere Everyplace service delivery).
8 Scalability and availability
Scalability and availability must be considered when planning deployment of a mobile solution.
The central focus point for these issues is the midtier server. We strongly recommend that you
also read the DB2 Everyplace Performance Tuning Guide (see bibliography) for greater
understanding of scalability and performance issues.
8.1 WebSphere
IBM WebSphere Application Server provides a rich e-business application deployment
environment with a complete set of application services including capabilities for transaction
management, security, clustering, high performance, availability, connectivity and scalability.
You can deploy Sync Server on the WebSphere Application Server (Application Server) to
increase your capability to administer and tune it.
8.2 Sync Server servlet
The DB2 Everyplace Sync Server is a servlet which comes embedded in a WebSphere servlet
engine extract. The servlet starts the listener which waits for requests from clients. In addition, it
controls the replication from the mirror database on the midtier server to the backend data source
and vice versa. Since the WebSphere servlet engine extract is only a servlet runner, it has
minimal configuration and monitoring facilities and tighter limitations on the number of
concurrent users.
37
8.3 First level of scalability
Configuring the Sync Server servlet under WebSphere Application Server is completely
independent of the Servlet Engine extract.
The DB2 Everyplace Sync Server is currently supported for Application Server 4.0 and 5.0 (for
details on the recommended FixPak levels see DB2 Everyplace product documentation). The
WebSphere installation package includes the IBM HTTP Server (which is based on Apache
server).
Performance measurements by IBM personnel on DB2 Everyplace have shown that DB2
Everyplace Sync Server performs best under the the control of WebSphere when there are more
than 60 users running synchronizations concurrently. This number was determined by
benchmarking under controlled conditions such as number of processors, amount of memory,
number of filter criteria, and accepted response time. Measurements also show that the resource
consumption per concurrent client, per number of subscriptions and for the size of sync data is
lower using the full WebSphere Application Server.
So if you want to reduce the response time, you will need to consider the investment for
additional resources such as processors or memory. This means that even if the client production
environment has less than 60 users, you should consider running synchronization with full
WebSphere. The total number of users supported can be increased by adding processors and
memory as well.
8.4 Second level of scalability
Instead of increasing the power of one central midtier server, think about the whole architecture.
With DB2 Everyplace V8 and WebSphere you can split the midtier server into many application
servers working with one control database server which allows you to share the DSYCTLDB
among multiple servers.
Scaling beyond the limitations of one application server can be achieved in one of two ways:
• Vertical cloning
• Horizontal cloning
Vertical cloning means that you clone and deploy multiple Sync Servers on the same physical
machine in the WebSphere Application Server environment. Before you decide to use this option,
you should measure resource utilization on your machine. The DB2 Everyplace performance
team recommends that you not deploy another clone if CPU and memory are already utilized by
85% or more. (For details see the Performance Tuning Guide.)
38
Horizontal cloning means adding additional physical machines on which you install additional
configurations of WebSphere Application Server and Sync Server to generate a cluster
environment. This scenario could be used, for example, if you have many regional sales teams.
Each team can run against its own midtier server which only contains the data for this regional
group. The advantage will be the even distribution of the load among multiple servers.
8.5 Availability
In a DB2 Everyplace environment the midtier server is the main resource that must be available.
Here are a couple of approaches to the availability issue for the midtier server:
•
Run more instances of WebSphere with DB2 Everyplace V8 sharing the same servlet. In
this case you must have a domain name server routing or WebSphere Edge Server
implemented. The scenario in the following picture shows a high availability solution
using WebSphere.
Figure 13: WebSphere cluster
To support Sync Server load balancing, WebSphere server affinity, Network Dispatcher
CBR/WET, or Network Dispatcher can be used. In terms of usability, WebSphere server
affinity is the best choice.
The sample shown in Figure 13 is a highly distributed scenario. Client requests are
distributed on the two Web servers through a network dispatcher. Web servers,
application servers and database servers are installed on physically separate machines.
All the requests from the synchronization client received by the Web server in Machine A
are redirected by the Web server plug-in, which is a component of WebSphere, to the
application server cluster for processing. Machines C and D require an Administrative
Server to manage their resources. The Administrative Server uses a repository located in a
DB HA cluster.
39
•
Make the database highly available using cluster software such as Microsoft Cluster
Server on Windows or HACMP on AIX. In the picture above the databases are placed on
a machine in a cluster. If this node fails, DB2 will start on the other node transparent to
the applications. The applications will have to reconnect.
For availability, you might need to duplicate existing configurations as mentioned in section 6.6
if you want to recover faster from a crash (see details in chapter 6.7).
9 Performance
In this chapter, we look at performance considerations for setting up and executing a DB2
Everyplace project. The location for the complete DB2 Everyplace Performance Tuning Guide
is listed in the bibliography and we strongly recommend that you read it prior to planning any
medium to large DB2 Everyplace installation.
Running a DB2 Everyplace sync solution requires proper tuning of the execution environment
(including the involved databases and WebSphere Application Server) and DB2 Everyplace.
Follow these steps to make sure that you have tuned for optimum performance:
1. Document your intended system configuration.
2. Document your projected workload and desired performance covering:
•
Number of mobile users on the system
•
Average amount of data for initial download
•
Average amount of data for delta sync
•
Average number of syncs per hour
•
Average number of syncs at peak times
•
Number of replication cycles
•
List intended features with significant performance impact (like filters)
•
Database layout of bufferpools, tablespaces, available disks and data model
3. Schedule a performance tuning period prior to going into production to reduce the risk of
using a poorly tuned setup on a production system. During this time, run extensive automated
multiuser tests based on your projected workload. Make sure you change only parameters in
one area at a time and iterate through all important areas.
4. In production, perform routine performance maintenance. Examples for this would be
periodic database RUNSTATS and the periodic capture of important system metrics (CPU,
memory, IO, network). Validate this periodically obtained information against your originally
projected workload and document the performance profiles in such a way that trends can be
identified before they become a problem.
9.1 Data design
40
The DB2 Everyplace sync process is a row level-based process. This means that if only one
column in a row is changed, the entire row will be synchronized. Therefore, a good data design
for performance requires separation of volatile and static data into different tables (see Figure
14). If this is done, fewer messages (due to smaller data load) are needed, and thus you will have
shorter sync times. If the sync time is shorter, more clients can synchronize per hour, leading to
higher concurrency and availability of the server to more users.
Another issue regarding data design for good performance is to separate data into different
subscriptions if the involved tables have to be replicated or synchronized with different
frequency. The gain of such a design could be twofold:
Figure 14: Good versus bad table design
• First of all, the sync time will be shorter since you can only synchronize the subscriptions sets
containing subscriptions with daily data most of the time. Perhaps only once a week the
subscription set containing the subscriptions with the weekly data will need to be synchronized.
• Second, replication time will benefit from this approach. Replication is a process per mirror
databases. If the data is separated into multiple subscriptions targeting different mirror
databases (see Figure 15 ), you can schedule different replication cycles:
41
Figure 15: Improving replication performance by separating data with different needs regarding
replication schedule
o For the replication of the daily data, you might want to schedule a replication cycle
twice a day or more, depending on your needs. This replication cycle will be shorter,
since the replication process does not process any work related to subscriptions on
other mirror databases, in our example the subscriptions containing the weekly data.
o For the replication of the weekly data, you might want to replicate only once a week.
Again, this replication cycle will be shorter since none of the daily data is replicated.
An example for such a design would be a service technician solution, where you might intend to
synchronize the material list only once a week because this data might not change at all in a
week. However, the service technician must synchronize the list of customers where an on-site
repair should be performed on a daily basis or even more frequently, in case a customer
postponed or cancelled the repair request earlier in the day.
The last point regarding the data design is the layout of the tables in the tablespaces. Note that
with DB2 Everyplace Sync Server V8.1.4 you can specify the table space of the DB2 UDB
mirror database where the table should be replicated. If you take advantage of this feature, you
can improve synchronization performance by parallelizing database I/O by distributing the tables
into different tablespaces laid out on multiple hard disks. With parallel I/O on the database level,
the Sync Server can read more data needed in concurrent synchronization scenarios in a smaller
amount of time.
9.2 DB2 Everyplace database performance
Using the mobile databases DB2 Everyplace or Cloudscape, there are two very important things
to consider regarding performance:
• Index usage
• Running the database on a memory card
The performance of insert/update/delete operations will suffer with each additional index created,
therefore degrading sync performance. However, on the other hand, your application querying the
database might not perform fast enough without indexes. Therefore, you need to balance
application versus sync performance regarding the number of indexes you create in the database.
For more details (prefix scanning, bidirectional index scanning, and so on) on the available
options please read the product documentation.
If you intend to run the mobile database from a memory card added to your PDA or smartphone
to have more disk space available, we strongly recommend that you benchmark the intended
solution first. The available memory cards vary greatly in I/O speed. This is the reason why due
to our experience, we recommend that you run benchmarks with your application on the intended
device with the intended memory card first, before you buy a certain card type for all of your
devices.
42
9.3 Sync client performance
Configuring the sync client, there are two very important considerations:
•
•
Average data size on sync
Network quality
The average data size during synchronization has impact on the timeout parameter. The timeout
value specifies how long the client will wait for a server reply. If the data load increases, the
timeout value should be adjusted accordingly. For example a more common timeout value for
synchronizations for 5-15 MB of data is 3-5 minutes. During peak hours, where Sync Server
might be very busy, this value might need to be increased. Adjust it until you achieve an
acceptable synchronization success rate.
The network (quality, speed, latency, reliability) affects the message size parameter. If the
network is not reliable, make the message size smaller to allow faster recovery. In such a
scenario, messages will be lost more frequently and the client will need to request/resend a
message more frequently. If you have a reliable, high quality network with high latency, you
should use larger message sizes to reduce the number of messages. In this scenario, sync
performance suffers from the high latency. Reducing the number of handshakes between client
and server by exchanging fewer messages means paying the latency price less often, and
therefore performance will be better.
Again, similar to the timeout setting, testing in your environment is needed to find the proper
setting for optimal performance.
9.4 Sync Server performance
Tuning Sync Server performance is a more complex task than tuning the other DB2 Everyplace
components in a sync solution. We will provide only a short list of recommended steps here,
since a full coverage of Sync Server performance tuning is beyond the scope of this article. You
should read the DB2 Everplace Performance Tuning Guide to get a more detailed understanding
of the topic prior to starting to implement a solution.
This would be the minimum list to consider during design and implementation:
• Minimize the number of subscriptions.
• Maximize the time between replication cycles (replication competes with
synchronizations on the machine hosting Sync Server for the same CPU, memory, I/O,
and other resources).
• Use data partitioning into different mirror databases to reduce data size in the mirror
databases for better query performance (this might be especially useful if groups of
mobile users work with different data).
43
•
•
•
•
•
•
•
Dedicate TCP/IP backbones between the mirror databases and the Sync Server for better
performance (apply the same approach between source and Sync Server if you use upload
subscriptions).
Design conflict free (with minimal conflicts) solutions because conflict resolution is very
expensive for various reasons.
If not needed, turn DeleteOutOfScope -Filtering off (using RowFilter.OutOfScope.Delete)
Adjust at least the following parameters to fit your needs:
o ThreadPoolCount (found in DSYGdflt.properties),
o Jdbc.MaxConnections, DB2ClientSession.Connections (both found in
DSYCTLDB in the dsy.properties table, for configuration use).
All three parameters will affect available concurrency of Sync Server. So if your
synchronization success rate is too small, you might want to see if increasing these
parameters solves the problem.
Tune the involved databases as outlined in the performance tuning guide.
Tune the involved Web Application Server as outlined in the performance tuning guide.
10 Summary
With DB2 Everyplace, mobile professionals such as sales people, inspectors, auditors, field
service technicians, doctors, realtors, insurance claim adjusters and many others can keep in
touch with vital data that they need, wherever they are and wherever the data is. Specifically,
organizations are now able to deliver their DB2 enterprise data to mobile and embedded devices.
With DB2 Everyplace, you can access and perform updates to a database on a mobile device.
Using the DB2 Everyplace Sync Server, you can synchronize data from the mobile device with
other data sources in your enterprise.
Additionally, DB2 Everyplace helps in collecting data that is structured, clean and complete.
There is no need for consolidation to be done as is necessary for data warehousing.
In the first part of this article we provided a technical overview of DB2 Everyplace. In the second
part we provided hints and tips for administrating and troubleshooting a DB2 Everyplace
solution. Finally, we outlined scalability and performance considerations.
Start your mobile solution with DB2 Everyplace now because:
•
•
•
•
Mobile computing technology has reached the level of performance and capacity needed
for enterprise applications
Storage has evolved from 512KB to 16MB and more (microdrives enables 4GB of storage
and more on a handheld device).
Cost of devices is declining.
Several mobile device manufacturers are aggressively pushing to expand the device
market (Palm, Handspring, Compaq, HP, Phillips, Psion, Sharp, Nokia, Ericsson, and
Motorola).
44
•
Growth in wireless access allows even greater flexibility in accessing enterprise data from
a mobile device. There are over 1 billion wireless subscribers worldwide.
Bibliography
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
DB2 Everyplace homepage:
http://www.ibm.com/software/data/db2/everyplace/new81.html
DB2 Everyplace library:
http://www.ibm.com/software/data/db2/everyplace/library.html
DB2 Everyplace Performance Tuning Guide:
http://www.ibm.com/software/data/db2/everyplace/library.html
DB2 Everyplace Application Developer Guide:
http://www.ibm.com/software/data/db2/everyplace/library.html
DB2 Everyplace Installation Guide:
http://www.ibm.com/software/data/db2/everyplace/library.html
DB2 Everyplace Sync Server Installation Guide:
http://www.ibm.com/software/data/db2/everyplace/library.html
DB2 Everyplace Success Stories:
http://www.ibm.com/software/success/cssdb.nsf/softwareL2VW?OpenView&Count=30&
RestrictToCategory=db2software_DB2Everyplace
FAQ for DB2 Everyplace:
http://www.ibm.com/software/data/db2/everyplace/support.html
DB2 Everyplace Mailing list:
http://groups.yahoo.com/group/db2everyplace/
DB2 Everyplace SDK:
http://www14.software.ibm.com/webapp/download/product.jsp?s=p&id=JPEN-4HNW2H
DB2 Everyplace .NET application development:
http://www-106.ibm.com/developerworks/edu/dm-dw-dm-0409oberhoferi.html?S_TACT=104AHW11&S_CMP=LIB%20
Cloudscape homepage:
http://www.ibm.com/software/data/cloudscape/
Cloudscape library: http:
http//www.ibm.com/software/data/cloudscape/pubs/
Cloudscape trial version:
http://www14.software.ibm.com/webapp/download/product.jsp?cat=data&S_TACT=&S_
CMP=&id=JSTN-57L7UQ&s=c
WebSphere Studio Device Developer homepage:
http://www.ibm.com/software/wireless/wsdd/
WebSphere Studio Device Developer library:
http://www.ibm.com/software/wireless/wsdd/library.html
Resources on .NET programming:
http://www.ondotnet.com/dotnet/
45
•
An Overview on HA and DR for DB2 UDB
http://www.ibm.com/developerworks/db2/library/techarticle/0304wright/0304wright.html
46