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:
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)
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 )
[, table_name ]
[, owner_name ]
[, database_name ]
[, migrate_data ]
[, drop_proxy_tables ]
[, migrate_fkeys ]
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: