ASA SQL Reference
System Procedures and Functions
System and catalog stored procedures
Migrates a set of remote tables to an Adaptive Server Anywhere database.
sa_migrate (
local_table_owner,
server_name,
[ table_name, ]
[ owner_name, ]
[ database_name, ]
[ migrate_data, ]
[ drop_proxy_tables ]
[ migrate_fkeys ] )
None
None
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
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:
sa_migrate_create_remote_table_list
sa_migrate_create_tables
sa_migrate_data
sa_migrate_create_remote_fks_list
sa_migrate_create_fks
sa_migrate_drop_proxy_tables
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.
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.
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 )