Schema upgrades for SQL Anywhere remote databases

You can change the schema of remote SQL Anywhere databases after they are deployed.

Note

If you can ensure that there are no other connections to the remote database, you can use the ALTER PUBLICATION statement manually to add new or altered tables to your publications. Otherwise, you must use the sp_hook_dbmlsync_schema_upgrade hook to upgrade your schema.

See sp_hook_dbmlsync_schema_upgrade.

To add tables to SQL 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. Ensure that the synchronization is successful before proceeding.

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

    ALTER PUBLICATION your_pub
   ADD TABLE table_name;

    You can use this statement inside a sp_hook_dbmlsync_schema_upgrade hook. See sp_hook_dbmlsync_schema_upgrade.

    For more information, see ALTER PUBLICATION statement [MobiLink] [SQL Remote].

  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 SQL 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. Perform a normal synchronization of the remote database using the old script version. Ensure that the synchronization is successful before proceeding.

  4. At the remote database, use the ALTER PUBLICATION statement 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 [MobiLink] [SQL Remote].

    You can use this statement inside a sp_hook_dbmlsync_schema_upgrade hook. See sp_hook_dbmlsync_schema_upgrade.

  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 [MobiLink] [SQL Remote].

    You can use this statement inside a sp_hook_dbmlsync_schema_upgrade hook. See sp_hook_dbmlsync_schema_upgrade.

  7. Synchronize with the new script version.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0