Migrating repository from Sybase IQ to SQL Anywhere

Sybase ETL 4.8 does not support Sybase IQ repository. If you are using a Sybase IQ repository from an earlier version of ETL, you must migrate your existing ETL repository from Sybase IQ to SQL Anywhere.

To migrate from a Sybase IQ repository to a SQL Anywhere repository:

Create a SQL Anywhere database.
Connect to the new SQL Anywhere repository using Sybase ETL Development.
Run SQL commands to migrate the repository data.

Start Sybase Central.
Select Tools | SQL Anywhere 11 | Create Database.
Read the information on the Welcome page and click Next.
Select “Create a database on this computer” and click Next.
Specify the full path for your database file. For example:
```
c:\temp\mysample.db
```
Click Finish.

If the specified directory does not exist, click Yes to confirm its creation.
Once the database creation is complete, click Close.

See “Creating a SQL Anywhere database” in SQL Anywhere 11.0 Guide.

After creating the SQL Anywhere database, create an entry in the sql.ini file or create an ODBC DSN to connect to this database. Then, use ETL Development to create tables in the database for the new repository.

In Windows, select Start | Programs | Sybase | Sybase ETL Development 4.8 | Sybase ETL Development.
Click Add, to add the new repository connection.
Enter the parameters for the new repository connection and click Test Logon to verify the connection.
A message displays stating the repository tables does not exist. Click Yes to create the tables.
Click Yes to create the repository. Click Save
Click Cancel to close the Repository window. Exit ETL Development.

Use the following commands to migrate Sybase IQ repository data into SQL Anywhere.

/* Create a remote server and login for the ETL
repository hosted in Sybase IQ. This example uses
ODBC and assumes there is an ODBC data source named- 'etl_rep_iq' that is configured to connect to the
existing ETL repository. The external login is
created for the Sybase IQ login with user 'DBA' and
password 'SQL'.*/

CREATE SERVER etl_rep_iq CLASS 'SAODBC' USING
'etl_rep_iq';

CREATE EXTERNLOGIN dba TO etl_rep_iq REMOTE LOGIN
DBA IDENTIFIED BY SQL;

/*Create proxy tables for the ETL repository tables
that reside on the Sybase IQ server.*/

CREATE EXISTING TABLE IQ_DS_DATA AT
'etl_rep_iq..DBA.DS_DATA';

CREATE EXISTING TABLE IQ_DS_OBJECT AT
'etl_rep_iq..DBA.DS_OBJECT';

CREATE EXISTING TABLE IQ_DS_CHUNK AT
'etl_rep_iq..DBA.DS_CHUNK';

CREATE EXISTING TABLE IQ_TRON_PERFORMANCE AT
'etl_rep_iq..DBA.TRON_PERFORMANCE';

/*Remove any existing data from the local repository
tables.*/

TRUNCATE TABLE DS_OBJECT;

TRUNCATE TABLE DS_DATA;

TRUNCATE TABLE DS_CHUNK;

TRUNCATE TABLE TRON_PERFORMANCE;

/*Copy data from the Sybase IQ tables to the new
repository tables.*/

INSERT INTO DS_OBJECT SELECT * FROM IQ_DS_OBJECT;

INSERT INTO DS_DATA SELECT * FROM IQ_DS_DATA;

INSERT INTO DS_CHUNK SELECT * FROM IQ_DS_CHUNK;

INSERT INTO TRON_PERFORMANCE SELECT * FROM
IQ_TRON_PERFORMANCE;

It is assumed that the Sybase ETL repository database name is etl_rep_iq and that you are connecting to Sybase IQ through an ODBC data source named etl_rep_iq. You must modify the commands as needed for your environment.