Chapter 12 Working with Replication Server

Creating a Staging Database for Sybase IQ

Sybase IQ has unique features for managing large data warehouses.

If you want to use Sybase IQ to implement data warehouse and RepServer to replicate data from an OLTP database in Sybase IQ, you should implement a staging database because Sybase IQ is not optimized for inserting, updating and deleting row by row.

There are two approaches for implementing a staging database:

Use a Sybase ASE staging database. It is well suited for large volume of data and long data transfer period.
Use Sybase IQ system tables. It is possible to use this approach if the incremental data volume is low or if you use an automatic data transfer script.

Sybase ASE staging database

Implementing a staging database with Sybase ASE is time consuming because you have to:

Create a Sybase ASE database with the same structure as Sybase IQ.
Create the stored procedures used by RepServer function strings in the staging database.
Change RepServer connection to the staging database.
Create or modify RepServer function strings to invoke the stored procedures.
Create staging tables in Sybase IQ to move data from the staging database into temporary tables in Sybase IQ before moving the data into Sybase IQ tables.
Create a stored procedure in Sybase IQ to load data from the staging database into Sybase IQ.
Create a stored procedure in the staging database to clean transferred data.

Sybase IQ system tables

To use Sybase IQ system tables you have to:

Create the stored procedures used by RepServer function strings in the Sybase IQ database.
Create or modify RepServer function strings to invoke the stored procedures.
Create staging tables in Sybase IQ using IQ system tables.
Create a stored procedure in Sybase IQ to load data from the staging database into Sybase IQ.

PowerDesigner can use the model-driven approach to automate the creation of the staging database: you define replication using Sybase IQ as target database. Once the definition is complete, a single command allows you to create all the artifacts required to implement the staging database.