Lesson 4: Create a synchronization model

In this lesson, you create a synchronization model for your consolidated database.

Connect to the consolidated database by creating a new MobiLink project.

 To create a new MobiLink project

  1. Choose Start » Programs » SQL Anywhere 12 » Administration Tools » Sybase Central.

  2. From the Tools menu, choose MobiLink 12 » New Project.

  3. In the Name field, type ase_project.

  4. In the Location field, type C:\mlase, and then click Next.

  5. Check the Add A Consolidated Database To The Project option.

  6. In the Database Display Name field, type ase_cons.

  7. Click Edit. The Connect To A Generic ODBC Database window appears.

  8. In the User ID field, type sa.

  9. In the Password field, type the password for the sa account.

  10. In the ODBC Data Source name field, click Browse and select ase_cons.

  11. Click OK, then click Save.

  12. Check the Remember The Password option, and then click Next.

  13. Choose the Create a new model option, and then click Next.

  14. Check the Add a remote schema name to the project option.

  15. Type ase_remote_schema for the remote schema name, and then click Finish.

The Create Synchronization Model Wizard provides step-by-step instructions for setting up synchronization between the consolidated database and remote database.

 To create a synchronization model

  1. On the Welcome page, type sync_ase in the What Do You Want To Name The New Synchronization Model field, and then click Next.

  2. On the Primary Key Requirements page, select all three check boxes (you guarantee unique primary keys in Lesson 2). Click Next.

  3. Select the ase_cons consolidated database from the list, and then click Next.

  4. Click No, Create A New Remote Database Schema, and then click Next.

  5. On the New Remote Database Schema page, in the Which Consolidated Database Tables And Columns Do You Want To Have In Your Remote Database list, select the following tables:

    • au_pix
    • authors
    • discounts
    • sales
    • salesdetail
    • stores
    • titleauthor
    • titles

  6. Click Next.

  7. Click Timestamp-based Download, and then click Next.

    Timestamp-based downloads minimize the amount of data that is transferred because only data that has been updated since the last download is transmitted.

  8. On the Timestamp Download Options page, click Use Shadow Tables To Hold Timestamp Columns, and then click Next.

    Using shadow tables is often preferred because it does not require any changes to existing tables.

  9. Perform the following tasks on the Download Deletes page:

    1. Click Yes on the Do You Want Data Deleted On The Consolidated Database To Be Deleted On The Remote Databases option.

    2. Click Use Shadow Tables To Record Deletions.

      MobiLink creates shadow tables on the consolidated database to implement deletions.

    3. Click Next.

  10. Click Yes, Download the Same Data to Each Remote, and then click Next.

    You specify how to download specific data to a remote database by using custom logic when editing the synchronization model.

  11. Click No Conflict Detection, and then click Next.

    Although this tutorial specifies no conflict detection, many applications require conflict detection.

  12. Perform the following tasks on the Publication, Script Version and Description page:

    1. In the What Do You want To Name The Publication field, type sync_ase_publication.

    2. In the What Do You Want To Name The Script Version field, type sync_ase_scriptversion.

      The publication is the object on the remote database that specifies what data is synchronized. MobiLink server scripts define how uploaded data from remotes should be applied to the consolidated database, and script versions group scripts. You can use different script versions for different applications, allowing you to maintain a single MobiLink server while synchronizing different applications.

    3. Click Finish.

 To edit a synchronization model

  1. From the View menu, choose Folders.

  2. In the left pane of Sybase Central under MobiLink 12, expand ase_project, Synchronization Models and then sync_ase.

  3. Set the direction that data is synchronized for each table in the synchronization model.

    Click the Mappings tab in the right pane, and then set the rows the Dir column as follows:

    • The sales and salesdetail tables should be set to Bi-directional (both upload and download).
    • The remaining tables should be set to Download To Remote Only.

  4. Filter the rows downloaded to the remote database by remote ID.

    1. For the row containing the stores table, change the Download Subset to Custom.

    2. Click the Download Subset tab at the bottom of the right pane.

    3. Filter the rows by remote ID, which uniquely identifies the remote database, by adding a restriction to the WHERE clause of the download_cursor script.

      Type a search condition in the SQL Expression To Use In The Download Cursor's WHERE Clause field. For example, the following SQL script can be used for the stores table: 

      "dbo"."stores"."stor_id" = {ml s.remote_id}

      The download cursor script specifies what columns and rows are downloaded from each table to the remote database. The search condition ensures that you only download information about one store, namely, the store that has an identifier that equals the remote ID for the database.

  5. Repeat step 3 for the rows containing the sales, salesdetail, and discounts tables.

    Note

    You must rename the table specified in the SQL script to the table name in the row that you are editing.

    Use the following WHERE clause script for the salesdetail table: 

    "dbo"."salesdetail"."stor_id" = {ml s.remote_id}

    Use the following WHERE clause script for the discounts table: 

    "dbo"."discounts"."stor_id" = {ml s.remote_id}

  6. Save the synchronization model.

    From the File menu, choose Save.

    The synchronization model is complete and ready for deployment.

 Further reading
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0