Contents Index sa_make_object system procedure sa_migrate_create_fks system procedure

ASA SQL Reference
  System Procedures and Functions
    System and catalog stored procedures

sa_migrate system procedure


Function 

Migrates a set of remote tables to an Adaptive Server Anywhere database.

Syntax 

sa_migrate (
local_table_owner,
server_name,
table_name, ]
owner_name, ]
database_name, ]
migrate_data, ]
drop_proxy_tables ]
migrate_fkeys ] )

Permissions 

None

Side effects 

None

See also 

sa_migrate_create_remote_table_list system procedure

sa_migrate_create_tables system procedure

sa_migrate_data system procedure

sa_migrate_create_remote_fks_list system procedure

sa_migrate_create_fks system procedure

sa_migrate_drop_proxy_tables system procedure

Migrating databases to Adaptive Server Anywhere

Description 

You can use this procedure to migrate tables to Adaptive Server Anywhere from a remote Oracle, DB2, SQL Server, Adaptive Server Enterprise, Adaptive Server Anywhere, or Access database. This procedure allows you to migrate in one step a set of remote tables, including their foreign key mappings, from the specified server. The sa_migrate stored procedure calls the following stored procedures:

You might want to use these stored procedures instead of sa_migrate if you need more flexibility. For example, if you are migrating tables with foreign key relationships that are owned by different users, you cannot retain the foreign key relationships if you use sa_migrate.

Before you can migrate any tables, you must first create a remote server to connect to the remote database using the CREATE SERVER statement. You may also need to create an external login to the remote database using the CREATE EXTERNLOGIN statement.

For more information, see CREATE SERVER statement and CREATE EXTERNLOGIN statement.

You can migrate all the tables from the remote database to an Adaptive Server Anywhere database by specifying only the local_table_owner and server_name parameters. However, if you specify only these two parameters, all the tables that are migrated will belong to one owner in the target Adaptive Server Anywhere database. If tables have different owners on the remote database and you want them to have different owners on the Adaptive Server Anywhere database, then you must migrate the tables for each owner separately, specifying the local_table_owner and owner_name parameters each time you call the sa_migrate procedure. In order to use this procedure, you must have the necessary permissions to create tables for the local Adaptive Server Anywhere user.

Caution    Do not specify NULL for both the table_name and owner_name parameters.

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. It is recommended that you migrate tables associated with one owner at a time.

Parameters 

local_table_owner    The user on the target Adaptive Server Anywhere database who owns the migrated tables. Use the GRANT CONNECT statement to create this user. A value is required for this parameter.

For more information, see GRANT statement.

server_name    The name of the remote server that is being used to connect to the remote database. Use the CREATE SERVER statement to create this server. A value is required for this parameter.

For more information, see CREATE SERVER statement.

table_name    If you are migrating a single table, specify the name of that table using the table_name parameter. Otherwise, you should specify NULL (the default) for this parameter. Do not specify NULL for both the table_name and owner_name parameters.

owner_name    If you are migrating only tables that belong to one owner, specify the owner's name using the owner_name parameter. Otherwise, you should enter NULL (the default) for this parameter. Do not specify NULL for both the table_name and owner_name parameters.

database_name    The name of the remote database. You must specify the database name if you want to migrate tables from only one database on the remote server. Otherwise, enter NULL (the default) for this parameter.

migrate_data    Specifies whether the data in the remote tables is migrated. This parameter can be 0 (do not migrate data) or 1 (migrate data). By default, data is migrated.

drop_proxy_tables    Specifies whether the proxy tables created for the migration process are dropped once the migration is complete. This parameter can be 0 (proxy tables are not dropped) or 1 (proxy tables are dropped). By default, the proxy tables are dropped.

migrate_fkeys    Specifies whether the foreign key mappings are migrated. ropped once the migration is complete. This parameter can be 0 (do not migrate foreign key mappings) or 1 (migrate foreign key mappings). By default, the foreign key mappings are migrated.

Examples 

The following statement migrates all the tables belonging to user p_chin from the remote database, including foreign key mappings; migrates the data in the remote tables; and drops the proxy tables when migration is complete. In this example, all the tables that are migrated belong to local_user in the target Adaptive Server Anywhere database.

CALL sa_migrate( 'local_user', 'server_a', NULL, 'p_chin', NULL, 1, 1, 1 )

The following statement migrates only the tables that belonging to user remote_a from the remote database. In the target Adaptive Server Anywhere database, these tables belong to the user local_a.

CALL sa_migrate( 'local_a', 'server_a', NULL, 'remote_a', NULL, 1, 0, 1 )

Contents Index sa_make_object system procedure sa_migrate_create_fks system procedure