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

SQL Anywhere 12.0.0 » MobiLink - Getting Started » MobiLink Tutorials » Tutorial: Changing a schema using the ScriptVersion extended option


Lesson 5: Perform 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. Before you do this 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.


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

 To perform a schema change on the remote database
  1. Create an sp_hook_dbmlsync_schema_upgrade hook by executing the following SQL against 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()
        ALTER TABLE customer 
        ADD cell_phone varchar(12) default null;
        ALTER OPTION ScriptVersion='my_ver2';
        UPDATE #hook_dict
        SET value = 'always'
            WHERE name = 'drop hook';
  2. Next, 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;eng=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.

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