Modeling Replications to a Sybase IQ Data Warehouse

Sybase® IQ (IQ) is a high-performance decision support server designed specifically for data warehousing. Since IQ is not optimized for inserting, updating and deleting row by row, you should implement a staging database to replicate data from OLTP databases to an IQ data warehouse.

1. Create a PDM to represent the structure of your primary database. You can reverse-engineer an existing database by selecting File > Reverse Engineer > Database.
2. Select File > New Model to open the New Model window and select Data Movement Model in the Model Type list and Data Movement Diagram in the Diagram pane.
3. Click the Select Extensions button to open the Select Extensions dialog, click the General Purpose sub-tab, select the appropriate version of Replication Server and the IQ Staging extension file, and then click OK to return to the New Model window.
4. Click OK to create the DMM, which opens with an empty diagram.
5. Click the Replication Server tool in the Toolbox, and then click in the center of the diagram to create a replication process. Right-click the Replication server symbol, and select Replication Wizard to open a wizard that guides you through configuring Replication Server for replicating data between your source and remote databases (see Replicating Data with the Replication Wizard).

   The source database can be any supported database and the remote database must be Sybase IQ.

   When you click OK to close the wizard, PowerDesigner will create source and remote database objects in your DMM, as well as all the necessary articles, publications, and subscriptions that Replication Server requires to manage the replication of data between them:

   
   
   
6. Open the property sheet of the IQ database, select the Staging Database tab, and enter the appropriate properties:

   Staging database options	Description
   Sybase ASE version	Version of the Sybase ASE staging database automatically created.
   Database name	PDM name of the staging database.
   Database code	PDM code of the staging database.
   Server name	Server name of the staging database.
   Server code	Serve code of the staging database.
   Use insert table in Sybase IQ	Indicates that an insert staging table will be used in Sybase IQ to copy inserted rows from staging database in order to support transformation inside Sybase IQ.
   Support update in Sybase IQ	Indicates that an update statement will cause an update in Sybase IQ. If you do not select this option, update statements will be replaced by delete and insert statements.
   Insert table code	Template for defining the code of an insert table.
   Update table code	Template for defining the code of an update table.
   Delete table code	Template for defining the code of a delete table
   Use stored procedure for function strings	Creates stored procedures in the staging database and uses them in RepServer function strings.
   Insert procedure code	Template for defining the code of insert stored procedures.
   Update procedure code	Template for defining the code of update stored procedures.
   Delete procedure code	Template for defining the code of delete stored procedures.

7. Click OK to return to the diagram and then select Tools > Check Model to verify that your model contains no errors and then save the model for reference.
8. Select Tools > Generate Data Movement Model to open the Generate dialog.
9. Click the Enable transformations button on the Detail tab, and then click the Extensions tab and select the IQ Staging xem.
10. Click OK to launch the generation of a new DMM that recreates the original transformation but with an ASE staging database and an IQ Staging transformation process inserted between the replication server and the IQ database to represent the data transfer between the staging database and IQ:
    
    
    

    The RepServer definition is modified, it is no longer directly connected to Sybase IQ but to the Sybase ASE staging database, and function strings to replicate data into the Sybase ASE staging database have been added:

    • Creating a Sybase ASE database with the same structure as Sybase IQ.

    • Creating the stored procedures used by RepServer function strings in the staging database.

    • Changing the RepServer connection to the staging database.

    • Creating or modifying RepServer function strings to invoke the stored procedures.

    • Creating 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.

    • Creating a stored procedure in Sybase IQ to load data from the staging database into Sybase IQ.

    • Creating a stored procedure in the staging database to clean transferred data.

    Note: If you need to change any aspect of your replication definitions, you must do so in the original DMM, and then regenerate to recreate the staging database. Any changes made to replication definitions in the generated DMM will not be accurately reflected in the staging database.

• Generating Scripts for Replication to IQ
  To generate the RepServer definition and Sybase ASE staging database, you need to generate the RepServer definition, the Sybase ASE staging database, and the Sybase IQ database.
• Transferring Staged Data to IQ
  When the replication is set up, you can start RepServer to begin data replication. Data modifications made in the source database are replicated to the Sybase ASE staging database or the staging tables in Sybase IQ. At some point, you need to transfer the data from the staging database into Sybase IQ.