Using the sa_migrate system procedures

The sa_migrate system procedures can allow you to migrate remote data. If you do not want to modify the tables in any way, you can use the two-step method. Alternatively, if you would like to remove tables or foreign key mappings, you can use the extended method.

When using the sa_migrate set of system procedures, you must complete the following steps before you can import a remote database:

  1. Create a target database.

    For more information, see Creating a database.

  2. Create a remote server to connect to the remote database.

    For more information, see Creating remote servers.

  3. Create an external login to connect to the remote database. This is only required when the user has different passwords on the target and remote databases, or when you want to login using a different user ID on the remote database than the one you are using on the target database.

    For more information, see Creating external logins.

  4. Create a local user who will own the migrated tables in the target database.

    For more information, see Creating new users.

Migrating a database in two steps using sa_migrate

Supplying NULL for both the table-name and owner-name parameters migrates all the tables in the database, including system tables. As well, tables that have the same name, but different owners, in the remote database all belong to one owner in the target database. For these reasons, you should migrate tables associated with one owner at a time.

To import remote tables (two-step method)

  1. From Interactive SQL, connect to the target database.

  2. In the SQL Statements pane, run the sa_migrate system procedure. For example, 

    CALL sa_migrate( 'local_a', 'ase', NULL, l_smith, NULL, 1, 1, 1 );

    This procedure calls several procedures in turn and migrates all the remote tables belonging to the user l_smith using the specified criteria.

If you do not want all the migrated tables to be owned by the same user on the target database, you must run the sa_migrate procedure for each owner on the target database, specifying the local-table-owner and owner-name arguments.

For more information, see sa_migrate system procedure.

Migrating individual tables using the sa_migrate system procedures

Do not supply NULL for both the table-name and owner-name parameters. Doing so migrates all the tables in the database, including system tables. As well, tables that have the same name but different owners in the remote database all belong to one owner in the target database. It is recommended that you migrate tables associated with one owner at a time.

To import remote tables (with modifications)

  1. From Interactive SQL, connect to the target database.

  2. Run the sa_migrate_create_remote_table_list system procedure. For example,

    CALL sa_migrate_create_remote_table_list( 'ase',
     NULL, 'remote_a', 'mydb' );

    You must specify a database name for Adaptive Server Enterprise and Microsoft SQL Server databases.

    This populates the dbo.migrate_remote_table_list table with a list of remote tables to migrate. You can delete rows from this table for remote tables that you do not want to migrate.

    For more information, see sa_migrate_create_remote_table_list system procedure.

  3. Run the sa_migrate_create_tables system procedure. For example:

    CALL sa_migrate_create_tables( 'local_a' );

    This procedure takes the list of remote tables from dbo.migrate_remote_table_list and creates a proxy table and a base table for each remote table listed. This procedure also creates all primary key indexes for the migrated tables.

    For more information, see sa_migrate_create_tables system procedure.

  4. If you want to migrate the data from the remote tables into the base tables on the target database, run the sa_migrate_data system procedure. For example,

    Execute the following statement:

    CALL sa_migrate_data( 'local_a' );

    This procedure migrates the data from each remote table into the base table created by the sa_migrate_create_tables procedure.

    For more information, see sa_migrate_data system procedure.

    If you do not want to migrate the foreign keys from the remote database, you can skip to step 7.

  5. Run the sa_migrate_create_remote_fks_list system procedure. For example,

    CALL sa_migrate_create_remote_fks_list( 'ase' );

    This procedure populates the table dbo.migrate_remote_fks_list with the list of foreign keys associated with each of the remote tables listed in dbo.migrate_remote_table_list.

    You can remove any foreign key mappings you do not want to recreate on the local base tables.

    For more information, see sa_migrate_create_remote_fks_list system procedure.

  6. Run the sa_migrate_create_fks system procedure. For example,

    CALL sa_migrate_create_fks( 'local_a' );

    This procedure creates the foreign key mappings defined in dbo.migrate_remote_fks_list on the base tables.

    For more information, see sa_migrate_create_fks system procedure.

  7. If you want to drop the proxy tables that were created for migration purposes, run the sa_migrate_drop_proxy_tables system procedure. For example, 

    CALL sa_migrate_drop_proxy_tables( 'local_a' );

    This procedure drops all proxy tables created for migration purposes and completes the migration process.

    For more information, see sa_migrate_drop_proxy_tables system procedure.

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