Lesson 5: Performing a schema change on the consolidated database

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

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

Perform a schema change on the consolidated database

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 statement in the instance of Interactive SQL that is connected to the consolidated database.
ALTER TABLE customer ADD cell_phone VARCHAR(12) DEFAULT NULL;

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 statements on 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;

Proceed to Lesson 6: Performing a schema change on the remote database.

Discuss this page in DocCommentXchange.