ASA SQL User's Guide
Importing and Exporting Data
You can import tables from remote Oracle, DB2, Microsoft SQL Server, Sybase Adaptive Server Enterprise, Adaptive Server Anywhere, and Microsoft Access databases into Adaptive Server Anywhere using the sa_migrate set of stored procedures or the Data Migration wizard.
If you do not want to modify the tables in any way, you can use the single 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 stored procedures, you must complete the following steps before you can import a remote database:
Create a target database.
For information about creating a database, see Creating a database.
Create a remote server to connect to the remote database.
For information about creating a remote server, see Creating remote servers.
Create an external login to connect to the remote database. This is only required when the user has a 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 information about creating an external login, see Creating external logins.
Create a local user who will own the migrated tables in the target database.
For information about creating a user, see Creating new users.
If you use the Data Migration wizard, you can perform all these steps, except creating the target database, from the wizard in Sybase Central.
To import remote tables (Sybase Central)
From Sybase Central, connect to the target database.
In the left pane of the Sybase Central window, select the Adaptive Server Anywhere 9 plug-in.
In the right pane, click the Utilities tab.
All the operations you can perform on a database appear in the right pane.
Double-click Migrate Database in the right pane.
The Database Migration wizard appears.
You can also open the Database Migration wizard by right-clicking on the target database in the left pane and choosing Migrate Database from the popup menu, or by choosing the Tools
Select the target database from the list, and then click Next.
Select the remote server you want to use to connect to the remote database from which you want to migrate data, and then click Next.
If you have not already created a remote server, click Create Remote Server Now to open the Remote Server Creation wizard.
For more information about creating a remote server, see Creating remote servers.
Select the tables that you want to migrate, and then click Next.
Select whether you want to migrate the data and the foreign keys from the remote tables and whether you want to keep the proxy tables that are created for the migration process, and then click Next.
If the target database is version 8.0.0 or earlier, the Migrate the foreign keys option is not enabled. You must upgrade the database to version 8.0.1 or later to use this option.
For more information about upgrading, see Upgrading a database.
Select the user that will own the tables on the target database, and then click Finish to complete the migration.
If you have not already created a user, click Create User Now to open the User Creation wizard.
Click Finish.
The following example uses the sa_migrate stored procedure to import all the tables that belong to one owner on the remote database in one step.
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, it is recommended that you migrate tables associated with one owner at a time.
To import remote tables (single step)
From Interactive SQL, connect to the target database.
In the Interactive SQL Statements pane, run the sa_migrate stored 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. It is recommended that you migrate tables associated with one owner at a time.
For more information, see sa_migrate system procedure.
For target databases that are version 8.0.0 or earlier, foreign keys are migrated automatically. If you do not want to migrate the foreign keys, you must upgrade the database file format to version 8.0.1 or later.
For more information about upgrading, see Upgrading a database.
To import remote tables (with modifications)
From Interactive SQL, connect to the target database.
Run the sa_migrate_create_remote_table_list stored 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 you do not wish to migrate.
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.
For more information about the sa_migrate_create_remote_table_list stored procedure, see sa_migrate_create_remote_table_list system procedure.
Run the sa_migrate_create_tables stored 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 about the sa_migrate_create_tables stored procedure, see sa_migrate_create_tables system procedure.
If you want to migrate the data from the remote tables into the base tables on the target database, run the sa_migrate_data stored procedure. For example,
Enter the following stored procedure:
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 about the sa_migrate_data stored procedure, 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.
Run the sa_migrate_create_remote_fks_list stored 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 about the sa_migrate_create_remote_fks_list stored procedure, see sa_migrate_create_remote_fks_list system procedure.
Run the sa_migrate_create_fks stored 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 about the sa_migrate_create_fks stored procedure, see sa_migrate_create_fks system procedure.
If you want to drop the proxy tables that were created for migration purposes, run the sa_drop_proxy_tables stored 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 about the sa_migrate_drop_proxy_tables stored procedure, see sa_migrate_drop_proxy_tables system procedure.