In this section you synchronize your remote and consolidated databases, and then modify the remote database and synchronize them again.
Synchronize the databases
To synchronize the databases the first time, you must create an ODBC data source. This is because the MobiLink synchronization server can connect to the database engine only through ODBC.
v To synchronize the consolidated and remote databases: 1 Create an ODBC data source for the master database:
♦ From the Windows Start menu, choose Programs➤Sybase SQL Anywhere 7➤Adaptive Server Anywhere 7➤ODBC Administrator.
♦ Click Add. In the Create New Data Source dialog, select Adaptive Server Anywhere 7.0, and then click Finish.
♦ Configure the new data source. To do this, select the ODBC tab and supply the Data source name MobiLink/ASA tutorial. Select the Login tab, and supply the User ID DBA and the password SQL. Select the Database tab and supply the Database file
c:\synchtutorial\master.db. Click OK.
♦ Click OK again to close the ODBC Data Source Administrator. 2 Start the MobiLink synchronization server.
♦ The following command starts the MobiLink synchronization server and identifies the ODBC data source MobiLink/ASA tutorial as the consolidated database. At a command prompt, type
start dbmlsrv7 –c "dsn=MobiLink/ASA tutorial"
♦ When finished, a MobiLink message dialog appears stating, Ready to handle requests. Do not close this dialog: it is updated as you proceed. Read the text to ensure you have started the MobiLink synchronization server successfully.
3 Synchronize the two databases by typing
dbmlsync –c "dbn=remote;uid=DBA;pwd=SQL"
When finished, a DBMLSync message dialog appears. Read the text to ensure you have synchronized the databases successfully.
Lesson 3: Synchronize the databases
130
4 Verify that the data has transferred successfully.
♦ Execute the following SQL statement:
select * from People
The People table in the remote database should appear as follows. Note that the remote database lacks the notes column because that column was not mentioned in the synchronization template.
person_id fname lname
0 Alan Able
1 Betty Best
♦ Execute the following SQL statement:
select * from Pets
The Pets table in the remote database should appear as follows.
pet_id person_id name species
0 0 Judy rat
1 0 Carmel guinea pig
3 1 Marshall cat
Modify the remote database
In this section, you make some changes to the remote database. Because the remote database contains a synchronization definition, Adaptive Server Anywhere automatically remembers which data must be sent to the consolidated database when you next synchronize.
v To modify the remote database:
1 Add a new person to the remote database with the following SQL statement:
insert into People values( 2, ’Charles’, ’Fish’)
2 Add Charles’s pet guinea pig using the following statement.
insert into Pets
values( 4, 2, ’Hershey’, ’guinea pig’ )
3 Commit these changes by typing
Chapter 8 Adaptive Server Anywhere Synchronization Using MobiLink
131 4 The above changes will be sent to the consolidated database
automatically. However, you can make changes to only the remote database by temporarily suspending synchronization. You can suspend the synchronization of delete statements only; all other statements will continue to be synchronized.
♦ Temporarily suspend logging of deletions for synchronization by executing the following SQL statement.
stop synchronization delete
♦ Delete Carmel the guinea pig and commit the change.
DELETE from Pets WHERE pet_id=1; commit
♦ Restart logging of changes for synchronization.
start synchronization delete
5 Verify your changes:
♦ Execute the following SQL statement:
select * from People
The People table in the remote database should appear as follows.
person_id fname lname
0 Alan Able
1 Betty Best
2 Charles Fish
♦ Execute the following SQL statement:
select * from Pets
The Pets table in the remote database should appear as follows.
pet_id person_id name species
0 0 Judy rat
3 1 Marshall cat
4 2 Hershey guinea pig
Upload changes
Lesson 3: Synchronize the databases
132
v To upload changes made to the remote database:
1 Ensure that the MobiLink synchronization server is still running. If it is not, restart it by typing
dbeng7 master.db remote.db
2 Synchronize the two databases. Run the command dbsync as follows:
dbmlsync –c "dbn=remote;uid=DBA;pwd=SQL"
3 Open a new window in Interactive SQL and connect to the master database.
♦ In Interactive SQL, choose File➤New Window.
♦ When the Connect dialog appears, select ASA via jConnect, and then choose localhost from the list of servers. Supply the user name
DBA and the password SQL. Enter the parameters dbn=master. Click OK.
When you are connected, the new Interactive SQL window appears. The title bar now says, master (DBA) on master.
4 Verify that the databases have been synchronized.
♦ Execute the following SQL statement:
select * from People
The People table in the master database should appear as follows.
person_id fname lname notes
0 Alan Able good guy
1 Betty Best (NULL)
2 Charles Fish (NULL)
♦ Execute the following SQL statement:
select * from Pets
The Pets table for both databases should appear as follows.
pet_id person_id name species
0 0 Judy rat
1 0 Carmel guinea pig
3 1 Marshall cat
Chapter 8 Adaptive Server Anywhere Synchronization Using MobiLink
133 The inserts and updates made only to the remote database prior to synchronization now apply to both databases after synchronization. 5 Close your connections and exit Interactive SQL:
♦ Disconnect from the master database and from the remote database by executing the following SQL statement in each Interactive SQL window.
disconnect
♦ Exit Interactive SQL.
6 Shut down the MobiLink synchronization server by typing the following at a command prompt:
dbmlstop
7 Shut down the database server.
♦ Right-click the database server icon in the system tray at the bottom right hand corner of your screen and click Exit.
Summary
134
Summary
During this tutorial, you accomplished the following tasks.
♦ Created a new Adaptive Server database that served as both the consolidated database and the reference database.
♦ Created a synchronization template and synchronization site that defined which tables and columns were to be extracted for the remote database.
♦ Extracted a remote database, using the original database as the reference database.
♦ Started a MobiLink synchronization server, using the original database as the consolidated database, and synchronized the remote database in order to download an initial set of data.
♦ Modified the remote database and explicitly prevented one of these changes from being logged for synchronization.
♦ Synchronized the remote database again, uploading your changes to the consolidated database.
Where do I go from here?
$
For more information about MobiLink, see the ASA Replication and Synchronization Guide.$
For an introduction to SQL Remote synchronization, see "Replicating Data with SQL Remote" on page 103.135
C H A P T E R 9
Creating Reports with InfoMaker
This chapter includes a brief tutorial of InfoMaker report making that gets you started and teaches you the basic skills required to be productive in the InfoMaker environment.
Topic Page
About InfoMaker 136
Lesson 1: Getting Started 137