With the advent of pervasive computing technologies, more businesses want to extend their legacy solutions to mobile devices such as PDAs or smart phones for a better response time and improved customer satisfaction. In this article, learn how to use DB2 Everyplace as one open source solution for pervasive computing. Implementation of such technology as DB2 Everyplace lets mobile professionals such as sales people, inspectors, auditors, field service technicians, doctors, realtors, and insurance claim adjusters keep in touch with vital data when they are away from the office.

In this article, you'll learn how to use a single DB2 Everyplace Sync Server to solve the challenge of supporting access from mobile clients in multilanguage platforms. This process can be used as a reference to develop a single solution to allow a worldwide workforce to access vital data.

Challenge and solution

If you want to develop a mobile solution for Chinese customers, for instance, you would store the data locally when the network is unavailable and sync the local data to an enterprise database when you were back at the office, or when the network is available. The data stored on both the mobile device side and the server database would most likely be written in Chinese characters. The most straightforward solution is for the data format on both sides to be in Chinese code pages. That solution does work; but what if you deploy the same solution for a customer in Japan or Korea -- must you modify the code for the new customers? That takes extra work and is often unacceptable.

Here's another scenario: An international firm has sales forces around the world. The customers in different countries may have information in different languages. This raises two issues:

1. How do you enable information with different languages to be stored in a single central database and then updated and retrieved by multiple clients correctly?
2. How do you solve the data separation so one client can only get the data he wants or is allowed to view?

Can you develop one solution to meet all of the requirements? Yes. You only need to install DB2 Everyplace Sync Server in one operating system platform (for example, English) and enable all sync clients in different language platforms to synchronize native language data with the single DB2 Everyplace server.

To solve the first issue, you need to dig into each part of the end-to-end solution to make sure native languages are supported by the sync server and clients. To solve the second issue, you can just leverage the horizontal filtering feature provided by DB2 Everyplace. With DB2 Everyplace and Workplace Client Technology™ Micro Edition V5.7.1, you can promptly solve the problems and proof the solution with one sample.

DB2 Everyplace Sync Server

In general, the DB2 Everyplace solution has the following key components:

1. Sync Server
2. Mobile Database, which can be used in many kinds of mobile devices
3. Sync Client, which is used as a synchronization engineer to support mobile devices and cooperate with the DB2 Everyplace Mobile Database

The DB2 Everyplace Sync Server manages the bidirectional data synchronization between source and target databases. It acts as a bridge between client software in mobile devices and a JDBC database server.

We use the DB2 Everyplace Sync Server and DB2 Everyplace Sync Client to realize the data and application synchronization between mobile devices and enterprise data sources.

Data synchronization can be bidirectional or unidirectional. There are two parts to the synchronization:

• The mobile client submits the change of local copy to data source in the enterprise server.
• The mobile client receives all changes of data source stored in the enterprise server from the last synchronization.

Figure 2 shows the relationship between mobile client, sync server, and data source.

Enable native language support

To enable the native language support for the sync server and clients, unify the encoding throughout the process of synchronization. Figure 3 shows that the encoding of data is always UTF-8 in every step of synchronization. This encoding ensures the native language data uses a uniform encoding and won't be damaged from or to the sync server. This way, you can enable a single database to support sync client applications in multiple language platforms. DB2 Everyplace Sync Client applications in different platforms can synchronize the data in a single source database and receive their own native language data correctly.

To unify the encoding for DB2 Everyplace Sync Server and Client, follow these steps:

DB2 Everyplace Server Side:

1. Create DB and Mirror DB for sync server with the UTF-8 code set.

DB2 Everyplace Client Side:

1. Set the encoding property of the DB2 Everyplace Sync Client to UTF-8.
2. When you query/update the local DB (DB2 Everyplace Mobile Database), set the encoding property of the jdbc connection to UTF-8.

Using DB2 Everyplace synchronization horizontal filtering

You should not only enable mobile clients in different platforms to receive their own native language data correctly, but also make sure the clients only receive the data they need. DB2 Everyplace provides a "filter" function to allow access to a subset of the data in a database.

You can use horizontal mirror filters when you want client devices to subscribe to certain rows from a mirror database. A horizontal mirror filter uses a SQL search condition to select a subset of rows from tables on the mirror database during synchronization. You can also use filter parameters in horizontal mirror filters to control which rows the DB2 Everyplace Sync Server sends to specific groups and users.

For more information about horizontal filtering, please refer to the IBM DB2 Everyplace Sync Server Administration Guide and John Casey's "DB2 Everyplace Version 8.2 synchronization horizontal filtering" article (see Resources).

Implementation

To simplify the problem, assume that many mobile clients in different language platforms need to access the data in only one table, and the mobile clients can get the data in their own languages correctly.

System environments

Now you'll need to install the following software in your server system:

1. DB2 Universal Database Enterprise Edition V8.1.6 or higher
2. DB2 Everyplace Enterprise Edition V8.2
3. WebSphere Application Server V5.0 or higher (optional)

Install the Workplace Client Technology Micro Edition V5.7.1 toolkit on WebSphere Studio Device Developer (Device Developer) if you want to develop the client-side applications.

The implementation includes the following two parts: enabling UTF-8 encoding and configuring horizontal filtering for data synchronization.

Enable UTF-8 encoding

1. Create databases and tables for the sync server

1. Create a standard database with the DB2 wizard and enter TESTDB in the Database name field.
2. Click 6. Region in the left panel and select UTF-8 from the Code set drop-down list.
   Figure 5. Create a database with UTF-8 encoding
   
   
3. Click Finish to create the data source DB - TESTDB.
4. Repeat steps 1 - 3 and create the mirror DB - TESTMIR.
5. Create a table TESTTABLE in TESTDB for synchronization. For example, if you want to create the following table for synchronization:

   Table 1. Original table

   RecorderID	ProductName	ProductAmount
   00000001	APPLE	1000
   00000002	COFFEE	2000

   You should also add the field "Country" to it.

   Table 2. Table with country tag

   RecorderID	ProductName	ProductAmount	Country
   00000001	APPLE	1000	CN
   00000002	COFFEE	2000	US

   The horizontal filter uses this field to allow access to a subset of the data in a database for each kind of user.

   Listing 1 shows the script to create a table.

   Listing 1. Script to Create the TESTTABLE

   CREATE TABLE TESTTABLE ( "RECORDERID" VARCHAR(20) not null primary key , "PRODUCTNAME" VARCHAR(128) , "PRODUCTAMOUNT" VARCHAR(20) , "COUNTRY" VARCHAR(20) , );

   
   

2. Develop the sync client application

This sync client application is used to synchronize the data to and from the sync server. We have implemented the client on IBM's SMF runtime, which is the open-standard OSGi framework. It can run successfully with minor changes to the latest runtime environment WebSphere Everyplace Deployment for Windows and Linux V6.0, which is OSGI-based too.

Create the basic sync client application.

To implement a basic DB2 Everyplace synchronization application development:

1. Import the DB2 Everyplace synchronization packages.
   • Import com.ibm.mobileservices.isync.*;
   • Import com.ibm.mobileservices.isync.event.*;

   For a JNI-based synchronization provider,

   • Import com.ibm.mobileservices.isync.db2e.jni.*;

   For a Trap-based synchronization provider,

   • Import com.ibm.mobileservices.isync.db2e.sti.*;
2. Implement the eventIssued method of the ISyncListener interface for event notification during synchronization.
3. Get a DB2eISyncProvider instance .
4. Get an instance of the synchronization service from the provider object.
5. Get an instance of the configuration store from the service object.
6. Get an instance of the synchronization driver from the configuration store object.
7. Register your application listener object that implements the ISyncListener interface for event notification from the synchronization driver object during synchronization.
8. Perform synchronization on all enabled subscription sets. Check the return code and exception for status of the synchronization.
9. Close and free all resources allocated by the synchronization provider.

For more information about DB2 Everyplace synchronization applications development, see the IBM DB2 Everyplace Application Development Guide Version 8.1.4 (see Resources).

Set the encoding of the sync client application.

When getting an instance of the synchronization service from the provider object (Step 4), set the isync.encoding property to UTF-8 with the code shown in Listing 2:

Properties userProps = new Properties();
userProps.put("isync.encoding", "UTF-8");
ISyncProvider provider = ISyncManager.getISyncProvider("isync:db2e:");
ISyncService service = provider.createSyncService("http://192.168.0.1:8080", userProps);
		 

3. Develop the DB2 Everyplace mobile database application

The DB2 Everyplace mobile database application is a helper application to insert the native data into a local DB2 Everyplace table and view the content of the local DB2 Everyplace table to help to ensure the synchronization is successfully completed and the data is consistent with the server side.

Create the basic DB2 Everyplace mobile database application.

To implement a basic DB2 Everyplace Mobile Database Application:

1. Import the java.sql package and any other necessary Java classes.
2. Load the DB2 Everyplace JDBC driver. The class name is com.ibm.db2e.jdbc.DB2eDriver.
3. Connect to the database using a URL of the form jdbc:subprotocol:subname. The DB2 Everyplace subprotocol is db2e. If the database is in c:\dir1\dir2, use the URL jdbc:db2e:c:/dir1/dir2/. You may also use a relative path for the subname.
4. Create a Statement object.
5. Access the database (your application logic goes here):
• Execute a SQL statement using the Statement object.
• Retrieve data from the returned ResultSet object (if the SQL statement you executed is a query).
6. Release database and JDBC resources by closing the ResultSet, Statement, and Connection objects.

For more information about DB2 Everyplace database applications development, check out the IBM DB2 Everyplace Application Development Guide Version 8.1.4 (see Resources).

Set the encoding of the jdbc connection for DB2 Everyplace mobile database application.

Use the code shown in Listing 3 to set the encoding of db2e jdbc connection to UTF-8.

Properties p = new Properties();
p.put("DB2 Everyplace_ENCODING","UTF-8"); 
Connection con = DriverManager.getConnection(dbUrl,p);
		 

Configure horizontal filtering for data synchronization

You'll now use the DB2 Everyplace Mobile Devices Administration Center to configure the DB2 Everyplace Sync Server.

First, create groups.

1. Right-click Groups in the left panel and click Create.
2. Enter the group name ENGGroup in the Name field, check Enable synchronization, and click OK.
3. Repeat steps 1 and 2 to create the other two groups: GERGroup and JAPGroup.

Next, create users.

1. Right-click Users in the left panel and click Create.
2. Enter the user name enguser1 in the Name field.
3. Click ... to the right of the group field, select the Group ENGGroup for the user, and click OK.

4. Click the Authentication tag in the Create User window and enter the password for the user.
5. Click OK.
6. Repeat steps 1 through 5 to create the following two users for JAPGroup and DERGroup:
   • username: japuser1 (JAPGroup)
   • username: dergroup (DERGroup)

Create the subscription set.

1. Right-click Subscription sets in the left panel and click Create.
2. Input the subscription set name "testSubSet" in the Name field.
3. Click Group and select these three groups to add to the Selected groups list.

4. Click OK.

Create the subscription.

1. Right-click Subscriptions and click Create > Table subscription > JDBC subscription.

2. Enter the name of the subscription TestSub.
3. Click Source, click ... to the right of the Database URL field, and select the TESTDB. Enter the user name and password that have the privilege to access this database.

4. Click Mirror, click ... to the right of the Database URL field, and select the TESTMIR. Enter the user name and password that have the privilege to access this database.
5. Click Subscription sets and choose the TestSubSet.

6. Click Identification and then click Define Subscription.

7. Click Add, select the TESTISYNC in the table list, choose the source/mirror table space, and click Add. Then click Close.

Create a horizontal filter.

1. Select the subscription you just defined and click Advanced. Click Rows in the Advanced Replication Subscription window and enter the following text in the Subset of rows for individual users field: COUNTRY=:COUNTRY_PARAM

In this example, we used :COUNTRY_PARAM as a filter parameter, which let us filter data at the group level. For more information about the horizontal filtering, see the IBM DB2 Everyplace Sync Server Administration Guide Version 8.2 (see Resources)"

2. Click OK to close the Advanced Replication Subscription window. Check Replicate now and click OK in the Create JDBC Subscription window.

3. Click Groups in the left panel, right-click the group ENGGroup, and select Edit.
4. Click Data filter and click Add. Enter the following values in the fields and click OK.
• Parameter name: :COUNTRY_PARAM
• Default value: 'ENG'

The parameter name is the name you used when defining the horizontal mirror filter in step 1. It must begin with a colon (:) and follow DB2 UDB rules for ordinary parameters. The default value is substituted for the parameter name in the horizontal mirror filter. The DB2 Everyplace Sync Server sends all users within this group the rows that fulfill the horizontal mirror filter. With these four steps, the rows whose COUNTRY field equals 'ENG' will only be received by users in ENGGroup.

For more information about the horizontal filtering, see the IBM DB2 Everyplace Sync Server Administration Guide Version 8.2 (see Resources)."

5. Repeat steps 3 and 4 and add a data filter for the other two groups.
   • DERGroup
     • Parameter name: :COUNTRY_PARAM
     • Default value: 'DER'
   • JAPGroup
     • Parameter name: :COUNTRY_PARAM
     • Default value: 'JAP'

Sample

The sample can run successfully with Workplace Client Technology Micro Edition V5.7.1 on Windows and Linux platforms and the Windows CE mobile device platform. The feature includes the following two plug-ins:

• com.ibm.sample.isync.client - Sync Client Application
• com.ibm.sample.db2e.client - the DB2 Everyplace Mobile Database Application

For more information about the use of this sample, refer to the Readme.txt in the wi-cloudsource.zip file.

In conclusion

Supporting mobile client access in multilanguage platforms with a single DB2 Everyplace sync server leverages the flexible framework provided by DB2 Everyplace and dramatically reduces costs when deploying the solution for customers in a similar situation. Our solution works well not only on Windows and Linux, but also on Windows CE. Furthermore, the solution is based on the open-standard OSGi framework, so it can be easily migrated to IBM WebSphere Everyplace Deployment 6.x products with minor changes.

With your knowledge of traditional JDBC application development and DB2 Everyplace server administration experience, you can now easily extend your DB2 Everyplace server to support mobile clients in different language platforms.

Back to top

Download

Information about download methods

Resources

Learn

• "DB2 Everyplace Version 8.2 synchronization horizontal filtering" (developerWorks, October 2004): This article explains horizontal filtering, a method for synchronizing a select subset of rows between the server and the DB2 Everyplace mobile device that can significantly enhance performance.
  
  
• Derby: For more information about Derby, please refer to Derby's Web site.
  
  
• DB2 Everyplace Library: Use this library to learn how to develop a DB2 Everyplace application.
  
  
• WebSphere Everyplace Micro Environment: Learn more about this environment.
  
  
• IBM DB2 Everyplace Application Development Guide Version 8.1.4: For more information about DB2 Everyplace synchronization applications development, see this guide.
  
  
• WebSphere Studio Device Developer help system: Get acquainted with the Service Management Framework Runtime, 3.5.2 User's Guide.
  
  
• OSGi: Learn OSGi from the OSGi Service-Platform Release 3 specification.
  
  
• IBM WebSphere Device Developer: Visit the WebSphere Device Developer site to learn how Device Developer can help you.
  
  
• developerWorks Wireless technology zone: Specializing wireless technology solutions.
  
  

Discuss

• developerWorks blogs: Get involved in the developerWorks community!
  
  

About the authors

Wei Wang is a software engineer at the IBM China Software Development Lab. His work focuses on pervasive computing client technologies.

Mu Gang Du is a staff software engineer at the IBM China Software Development Lab. His work focuses on pervasive computing client technologies.

Rate this article

Comments

Back to top

Table of contents

• Challenge and solution
• DB2 Everyplace Sync Server
• Enable native language support
• Using DB2 Everyplace synchronization horizontal filtering
• Implementation
• Sample
• In conclusion
• Download
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers