Lesson 3: Synchronize the databases 129
Summary 134
About this chapter
About MobiLink
120
About MobiLink
Data replication is the sharing of data among physically distinct databases. Changes made to shared data in any one database are replicated to the other databases in the replication setup. Sybase provides three distinct technologies for data replication:
♦ MobiLink
♦ SQL Remote
♦ Replication Server
Sybase MobiLink technology enables replication between an ODBC- compliant consolidated database and Adaptive Server Anywhere or UltraLite remote databases. The consolidated database and the database server can be made by many database systems, including Sybase Adaptive Server Anywhere, Sybase Adaptive Server Enterprise, Oracle, Microsoft SQL Server, or IBM DB2. The remote database must be Adaptive Server Anywhere.
MobiLink is designed for two-way replication involving a consolidated data server and large numbers of remote databases, typically including many mobile databases. Administration and resource requirements at the remote sites are minimal. The system is connection-based and a remote site can connect as often as desired. At the end of each connection, the databases are fully synchronized.
MobiLink works by lumping the results of multiple transactions on the remote database into one set of changes to be applied to the consolidated database. Since synchronization always occurs at a transaction boundary, referential integrity is preserved. The order of the individual changes made during the component transactions is not preserved: since uncommitted data is never replicated, data integrity is preserved.
$
For more information about synchronization strategies, including complete MobiLink documentation, see the ASA Replication and Synchronization Guide.$
For an introduction to SQL Remote synchronization, see "Replicating Data with SQL Remote" on page 103.Chapter 8 Adaptive Server Anywhere Synchronization Using MobiLink
121
Lesson 1: Create the consolidated database
In this tutorial, you create a consolidated database, extract a remote database, and then synchronize the two databases via MobiLink.
The first step in the tutorial is to create a .sql file for the database. This file has two purposes. Firstly, the file creates and populates the tables in the consolidated database. This process is not part of synchronization, but makes the tutorial simpler by setting up the data that will be synchronized.
Secondly and most importantly, the .sql file contains synchronization
scripts that control the synchronization process. These scripts specify how
uploaded data is to be incorporated into the consolidated database and which data is to be downloaded to the remote database. Synchronization scripts are SQL statements or procedures that control the synchronization process. Each script controls a particular event during synchronization. The scripts, which are written in the native language of the consolidated DBMS, are stored in tables within the consolidated database.
♦ The consolidated database is the central database and is connected to a MobiLink synchronization server. Remote databases are, as their name indicates, the remote databases that must be synchronized with the consolidated database.
A reference database is an Adaptive Server Anywhere database that is created for the sole purpose of creating remote databases. The consolidated database and reference database can have different schemas. The reference database must be an Adaptive Server Anywhere database, but the consolidated database can be made by a wide range of Sybase and non-Sybase database-management systems. For the purpose of simplicity in this tutorial, we use the same database as both the reference and consolidated database. This is possible because the consolidated database in this tutorial is an Adaptive Server Anywhere database and because the schema of the remote database is a subset of the schema of the consolidated database.
♦ This tutorial assumes that you have Adaptive Server Anywhere 7.0 installed on Windows 95/98/NT.
Create a SQL file for the consolidated database
In this section, you create a file that uses SQL commands to create and insert data into two tables, People and Pets, and uses synchronization scripts to specify how the databases should be synchronized.
Lesson 1: Create the consolidated database
122
v To create the SQL file:
1 Create the directory c:\synchtutorial.
2 Copy the following code into a new file and save it as server.sql in your
c:\synchtutorial directory.
create table People (
person_id integer primary key, fname char(30),
lname char(40), notes char(500) );
create table Pets (
pet_id integer primary key, person_id integer,
name char( 30 ), species char( 30 ),
foreign key person_id references People );
insert into People
values( 0, ’Alan’, ’Able’, ’good guy’ ); insert
into People
values( 1, ’Betty’, ’Best’, NULL ); insert
into Pets
values( 0, 0, ’Judy’, ’rat’ ); insert
into Pets
values( 1, 0, ’Carmel’, ’guinea pig’ ); insert
into Pets
values( 3, 1, ’Marshall’, ’cat’ ); commit;
call ml_add_table_script( ’version1’,
’People’,
’upload_cursor’,
’select person_id, fname, lname from People where person_id=?’ ); call ml_add_table_script( ’version1’, ’People’, ’download_cursor’,
’select person_id, fname, lname from People’ );
Chapter 8 Adaptive Server Anywhere Synchronization Using MobiLink 123 call ml_add_table_script( ’version1’, ’Pets’, ’upload_cursor’,
’select pet_id, person_id, name, species from Pets where pet_id=?’ ); call ml_add_table_script( ’version1’, ’Pets’, ’download_cursor’,
’select pet_id, person_id, name, species from Pets’
); commit;
Explanation of the sample code
The server.sql sample code creates tables in the consolidated database, inserts data into the tables, and stores synchronization scripts that specify which data is to be uploaded to the consolidated database and downloaded to the remote database.
The sample code creates two tables: People and Pets. The People table has four columns: person_id, fname, lname, and notes. The Pets table also has four columns: pet_id, person_id, name and species.
The following is the People table in the consolidated database after data has been inserted:
person_id fname lname notes
0 Alan Able good guy
1 Betty Best (NULL)
The following is the Pets table in the consolidated database after data has been inserted:
pet_id person_id name species
0 0 Judy rat
1 0 Carmel guinea pig
3 1 Marshall cat
Create tables
Lesson 1: Create the consolidated database
124
The last part of the file is four synchronization scripts. Each script starts with
call ml_add_table_script. The scripts specify that the first three columns in the People table should be uploaded to the consolidated database and downloaded to the remote database, and that all four columns in the Pets
table should be uploaded to the consolidated database and downloaded to the remote database. When server.sql is run, the scripts are stored in tables in the consolidated database for future use.
Build the consolidated database
In this section, you use the file server.sql to create a consolidated database and add the tables Pets and People to it.
v To build the consolidated database:
1 Create a new consolidated database called master.db:
♦ Open a command prompt window and navigate to c:\synchtutorial. ♦ Type dbinit master.db
2 Start the consolidated database server by typing dbeng7 master.db
3 Start Interactive SQL and connect to master.db:
♦ On the Identification tab, enter the user name DBA and password
SQL.
♦ On the Database tab, in the Database File box, type or browse to
c:\synchtutorial\master.db.
♦ Click OK.
4 To add the database tables and synchronization scripts, you use the file
server.sql, which you created earlier in the tutorial:
♦ From the File menu, choose Run Script.
♦ Select the file c:\synchtutorial\server.sql, and click Open.
The statements in the file are executed. Your consolidated database now contains two tables with data: People and Pets. In addition,
synchronization scripts are now stored in the consolidated database; these will determine the upload and download contents.
5 Verify that tables have been created and data has been inserted successfully by displaying the contents of each file:
♦ Execute the following SQL statement:
SELECT * from People
Synchronization scripts
Chapter 8 Adaptive Server Anywhere Synchronization Using MobiLink
125 Tip
To execute an SQL statement in Interactive SQL, type or copy the statement in the SQL Statements pane, and then either press F5, or choose SQL➤Execute.
The contents of the People table are displayed:
person_id fname lname notes
0 Alan Able good guy
1 Betty Best (NULL)
♦ Execute the following SQL statement:
SELECT * from Pets
The contents of the Pets table are displayed:
pet_id person_id name species
0 0 Judy rat
1 0 Carmel guinea pig
3 1 Marshall cat
Now you are ready to create the remote database. What next?