CODEWITHSUNDEEP
sap-asesap-iq
Brett Hannah
Brett Hannah

Reputation: 4327

Archiving data in Sybase IQ from Sybase ASE

What is the best means of solving the following problem:

I have a Sybase ASE database which is used as an OLTP server. There is a lot of data inserted into the database each day and as a result the 'live' tables hold only the last n days of data (n can vary from table to table).

I would like to introduce a Sybase IQ server as a Decision Support Server holding all of the previous days data for reporting purposes.

I would like a nightly job which would "sync" the Sybase IQ tables with those in ASE i.e. insert all new rows, update all changed rows but NOT delete any of the rows outside of the n days that the live table represents.

All ideas welcome!!!

Upvotes: 0

Views: 871

Answers (1)

kolchanov
kolchanov

Reputation: 2108

You have to develop an ETL (Extract Transform Load) process.

There are a lot commercial and free ETL products. But I think that the best way in this case

  1. Create RS ASE -> ASE replication (direct ASE -> IQ would have bad performance)

  2. Modify delete function string to separate delete operations

  3. Periodically truncate insert IQ tables from the second ASE db (update is very poor in IQ) via linked server connection

Upvotes: 0

Related Questions