Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » MobiLink - Getting Started » MobiLink tutorials » Tutorial: Changing a schema using the ScriptVersion extended option

 

Lesson 5: Performing a schema change on the remote database

In this lesson, you modify the remote database to add the new column to the customer table and to change the script version used to synchronize.

Prérequis

This lesson assumes you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Changing a schema using the ScriptVersion extended option.

This lesson assumes you have completed all preceding lessons. See Lesson 1: Creating and configuring the consolidated database.

Before you modify the remote database and change the script version used to synchronize, you must ensure that there are no operations for the customer table that need to be uploaded. The best way to do this is to perform the schema change in the sp_hook_dbmlsync_schema_upgrade hook. When you use this hook, dbmlsync ensures that the schema change is performed safely by locking the synchronizing tables at the start of synchronization and holding the locks until the schema change is complete.

Contexte et remarques

Caution

If you change the schema when there are operations to be uploaded, the remote database is always unable to synchronize after the schema change.

 Task
  1. Create an sp_hook_dbmlsync_schema_upgrade hook by executing the following SQL statement on the remote database. The hook adds a new column to the customer table and changes the value of the ScriptVersion extended option stored with the subscription. The hook is deleted by dbmlsync after it has executed.



    CREATE PROCEDURE sp_hook_dbmlsync_schema_upgrade()
    BEGIN
        ALTER TABLE customer 
        ADD cell_phone varchar(12) default null;
    
        ALTER SYNCHRONIZATION SUBSCRIPTION my_sub
        ALTER OPTION ScriptVersion='my_ver2';
    
        UPDATE #hook_dict
        SET value = 'always'
            WHERE name = 'drop hook';
    END;
  2. Synchronize to upload any operations that need to be uploaded and to perform the schema change by executing the sp_hook_dbmlsync_schema_change hook. Run the following command.

    dbmlsync -v+ -ot sync2.txt -c UID=DBA;PWD=sql;SERVER=remote -s my_sub -k

    After this synchronization, it is a very good idea to look at the dbmlsync log sync2.txt to ensure that there are no errors to indicate that the schema change was not completed.

Résultat

The schema change is now complete and you can continue synchronizing normally.

Suivant

None.