Lesson 5: Perform a schema change on the consolidated database

In this lesson, you perform a schema change on the consolidated database.

To perform a schema change on the consolidated database

Now you are going to add a new column to the customer table to store the customer's cell phone number. First, add the new column to the consolidated database by executing the following SQL in the instance of Interactive SQL that's connected to the consolidated database.
ALTER TABLE customer ADD cell_phone varchar(12) default null;

Next, create a new script version called my_ver2 to handle synchronizations from remote databases with the new schema. Remote databases with the old schema continue to use the old script version, my_ver1. Execute the following SQL against the consolidated database:

CALL ml_add_column('my_ver2', 'customer', 'id', null );
CALL ml_add_column('my_ver2', 'customer', 'name', null );
CALL ml_add_column('my_ver2', 'customer', 'phone', null );
CALL ml_add_column('my_ver2', 'customer', 'cell_phone', null );

CALL ml_add_table_script( 'my_ver2', 'customer', 'upload_insert',
      'INSERT INTO customer ( id, name, phone, cell_phone ) ' 
      || 'VALUES ({ml r.id}, {ml r.name}, {ml r.phone}, {ml r.cell_phone})' );

CALL ml_add_table_script( 'my_ver2', 'customer', 'download_cursor',
       'SELECT id, name, phone, cell_phone from customer' );

CALL ml_add_table_script( 'my_ver2', 'customer', 'download_delete_cursor', '--{ml_ignore}' );
COMMIT;

Discuss this page in DocCommentXchange.