MobiLink Synchronization User's Guide
  Synchronization Techniques

Schema changes in remote databases

As your needs evolve, deployed remote databases may require schema changes. The most common schema changes are adding a new column to an existing table or adding a new table to the database.

Adaptive Server Anywhere remote databases 

To add tables to Adaptive Server Anywhere remote databases

1. Add the associated table scripts in the consolidated database.

   The same script version may be used for the remote database without the new table and the remote database with the new table. However, if the presence of the new table changes how existing tables are synchronized, then you must create a new script version, and create new scripts for all tables being synchronized with the new script version.

2. Perform a normal synchronization.

   This step is optional, but recommended, before changing schema.

3. Use the ALTER PUBLICATION statement to add the table. For example,

   ALTER PUBLICATION your_pub
      ADD TABLE table_name

   For more information, see ALTER PUBLICATION statement.

4. Synchronize. Use the new script version, if required.

Changing table definitions in remote databases 

Changing the number or type of columns in an existing table must be done carefully. When a MobiLink client synchronizes with a new schema, it expects scripts, such as upload_update or download_cursor, which have parameters for all columns in the remote table. An older remote database expects scripts that have only the original columns.

To alter a published table in a deployed Adaptive Server Anywhere remote database

1. At the consolidated database, create a new script version.

   For more information, see Script versions.

2. For your new script version, create scripts for all tables in the publication(s) that contain the table that you want to alter and that are synchronized with the old script version.

3. At the remote database, perform a normal synchronization using the old script version.

4. At the remote database, use the ALTER PUBLICATION statement to to temporarily drop the table from the publication. For example,

   ALTER PUBLICATION your_pub
     DROP TABLE table_name
    

   For more information, see ALTER PUBLICATION statement.

5. At the remote database, use the ALTER TABLE statement to alter the table.

   For more information, see ALTER TABLE statement.

6. At the remote database, use the ALTER PUBLICATION statement to add the table back into the publication.

   For more information, see ALTER PUBLICATION statement.

7. Synchronize with the new script version.

Note: Steps 4 through 6 may also be performed by the sp_hook_dbmlsync_schema_upgrade stored procedure. For more information, see sp_hook_dbmlsync_schema_upgrade.

For more information about changing schemas for Adaptive Server Anywhere remote databases, see sp_hook_dbmlsync_schema_upgrade.

UltraLite remote databases 

You can change the schema of a remote database by deploying a new application or through a schema upgrade.

• If you deploy a new application without a schema upgrade, you need to repopulate the UltraLite database by synchronizing with the MobiLink synchronization server.

• In the schema upgrade case, your data will be preserved. It is usually impractical to have all users upgrade to the new version of the application at the same time.

You need to be able to have both versions co-existing in the field and synchronizing with a single consolidated database. You can create two or more versions of the synchronization scripts that are stored in the consolidated database and control the actions of the MobiLink synchronization server. Each version of your application can then select the appropriate set of synchronization scripts by specifying the correct version name when it initiates synchronization.

For more information about schemas in UltraLite, see Databases and schema files.