You can migrate an individual table using the database migration system procedures.
You must have the following system privileges:
CREATE ANY TABLE
CREATE ANY INDEX
INSERT ANY TABLE
SELECT ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
You must already have a remote server created. You must already have a user to own the tables in the target database.
To create an external login, you must have the MANAGE ANY USER system privilege.
Context and remarks
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. Also, 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.
Create a target database.
From Interactive SQL, connect to the target database.
(Optional) Create an external login to connect to the remote database. An external login is only required when the user has different passwords on the target and remote databases, or when you want to log in using a different user ID on the remote database than the one you are using on the target database.
Run the sa_migrate_create_remote_table_list system procedure. For example:
CALL sa_migrate_create_remote_table_list( 'rmt_server1', NULL, 'remote_user1', 'mydb' );
You must specify a database name for Adaptive Server Enterprise and Microsoft SQL Server databases.
This procedure 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.
Run the sa_migrate_create_tables system procedure. For example:
CALL sa_migrate_create_tables( 'local_user1' );
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.
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:
CALL sa_migrate_data( 'local_user1' );
This procedure migrates the data from each remote table into the base table created by the sa_migrate_create_tables procedure.
If you do not want to migrate the foreign keys from the remote database, you can skip to Step 10.
Run the sa_migrate_create_remote_fks_list system procedure. For example:
CALL sa_migrate_create_remote_fks_list( 'rmt_server1' );
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.
Run the sa_migrate_create_fks system procedure. For example:
CALL sa_migrate_create_fks( 'local_user1' );
This procedure creates the foreign key mappings defined in dbo.migrate_remote_fks_list on the base tables.
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_user1' );
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|