Switch to standard view 
  Sybase logo
 
 
 



 

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
• UltraLite
• Sybase Adaptive Server Enterprise
• IBM DB2
• Microsoft SQL Server
• Microsoft Access
• Oracle
• MySQL
• Advantage Database Server
• Any generic ODBC driver that can connect to a remote server

The example shown below migrates a simple ‘CUSTOMERS’ table from an Oracle (11g) database into SQL Anywhere (12.0.1).

Before proceeding with the tutorial, ensure that the Oracle database is up and running on the target machine, and that the Oracle Basic Instant Client 11g and SQL Anywhere 12.0.1 software are correctly installed on the client machine. Ensure that the path ‘%SQLANY12%\bin32’ (or ‘%SQLANY12%\bin64’) are available in your system path (%PATH%).

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’.


e.g.

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = port))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 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:

dbinit test.db
dbsrv12 test.db

Connect to the newly created SA database with the Interactive SQL utility:

dbisql –nogui –c “uid=dba;pwd=sql;servername=test”

Create a remote server to previously created ORCL data source, while referring to the DSN that was created in earlier steps.

CREATE SERVER "ORCL" CLASS 'ORAODBC' USING 'ORCL' READ ONLY;

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)

e.g.

CREATE EXTERNLOGIN "DBA" TO "ORCL" REMOTE LOGIN 'MLTRADER' IDENTIFIED BY 'sql';

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 )

sa_migrate(
base_table_owner,
server_name
[, table_name ]
[, owner_name ]
[, database_name ]
[, migrate_data ]
[, drop_proxy_tables ]
[, migrate_fkeys ]

e.g.

To import just one remotetable (CUSTOMER), specify the following SQL:

call sa_migrate('DBA','ORCL','CUSTOMER','MLTRADER');

To migrate all the tables for the entire database, specify ‘NULL’ as table name parameter:

call sa_migrate('DBA','ORCL',NULL,'MLTRADER');

 

 

 

 

 

 

 

 

 



Back to Top
© Copyright 2010, Sybase Inc.