August 2011 Edition
Importing Data from ODBC Sources into SQL Anywhere on Microsoft Windows
SQL Anywhere contains many built-in features to move external data in and out of the database. In a case when it’s required to migrate data from an existing database to a new SQL Anywhere database, a database administrator can use a set of built-in ‘sa_migrate*’ system stored procedures that will connect to an existing database, recreates the schema locally and then migrates the data automatically to the local tables.
In order to support migrating data from another database, an ‘external server’ definition must be first defined using the CREATE SERVER statement. Any server that can be accessed as a remote server can be used with the migration procedures.(See: http://dcx.sybase.com/index.html#1201/en/dbusage/ug-servclassrd-sectb-4215004.html).
The following database systems are supported by the SQL Anywhere migration procedures: :
• SQL Anywhere
The example shown below migrates a simple ‘CUSTOMERS’ table from an Oracle (11g) database into SQL Anywhere (12.0.1).
1. Create an Oracle DSN using the “iAnywhere Solutions 12 – Oracle ODBC” ODBC Driver
Make sure that your client configuration file (tnsnames.ora) contains the correct IP and PORT information for connecting to the Oracle server. This tutorial assumes that the TNS entry for the target Oracle database is ‘ORCL’.
To create the DSN, open the ODBC Data Source Administrator, (Run: ‘odbcad32.exe’ or open Start > Programs > SQL Anywhere 12 > SQL Anywhere > ODBC Administrator )
When ODBC Data Source Administrator opens, click ‘Add’. The ‘Create New Data Source’ dialog appears:
Select the appropriate ODBC driver and enter the appropriate configuration options in the DSN entry in order to make a successful connection to your remote ODBC source.
Some ODBC drivers offer a ‘Test Connection’ button to help ensure the DSN information is correct during entry:
2. Create and start a new SQL Anywhere 12.0.1 database
Open a Command Prompt, and use the following two commands to set up a new SQL Anywhere database on a running database server:
Connect to the newly created SA database with the Interactive SQL utility:
Create a remote server to previously created ORCL data source, while referring to the DSN that was created in earlier steps.
Create a remote login to connect to the ‘ORCL’ data source if this is different than your current SQL Anywhere login information (Note that by default, a remote server tries to use the same credentials that the local SQL Anywhere user is currently using)
3. Import the required tables using sa_migrate() stored procedure.
To import the single ‘CUSTOMER’ table, use the sa_migrate() procedure, while specifying the table and owner name: (See: http://dcx.sybase.com/index.html#1201/en/dbreference/sa-migrate-sysproc.html )
To import just one remotetable (CUSTOMER), specify the following SQL:
To migrate all the tables for the entire database, specify ‘NULL’ as table name parameter: