Multi-Path Replication from Adaptive Server to Sybase IQ

Set up a multipath replication replication system with two primary and replicate connections for end-to-end replication from an Adaptive Server primary to a Sybase IQ replicate database.

1. At the pdb primary database, select or create two sets of tables or stored procedures that you want to replicate through two replication paths.
2. Create the default connection to the pdb primary Adaptive Server database.

   Use create connection or rs_init to create the default connection. See Replication Server Configuration Guide > Configure Replication Server and Add Databases using rs_init.

3. Enable Multithreaded Adaptive Server RepAgent and Multiple Paths for Adaptive Server RepAgent.
   You can also set the memory and send buffers available to Adaptive Server RepAgent. See Replication Server Administration Guide Volume 2 > Performance Tuning > Multi-Path Replication > Multiple Primary Replication Paths > Enabling Multithreaded RepAgent and Multiple Paths for RepAgent.
   1. Enable multithreaded RepAgent.
      At the primary Adaptive Server, enter:

      sp_config_rep_agent pdb, ‘multithread rep agent’, ‘true’

   2. Set the number of replication paths for RepAgent.
      For example, to enable two paths, enter:

      sp_config_rep_agent pdb, 'max number of replication paths', '2'

4. Create an alternate replication path from the pdb primary database to the PRS Replication Server.
   See Replication Server > Administration Guide: Volume 2 > Performance Tuning > Multi-Path Replication > Multiple Primary Replication Paths > Creating Multiple Primary Replication Paths.
   1. Create an alternate replication path for the Adaptive Server RepAgent named pdb_conn2 between pdb and PRS Replication Server.
      At the primary Adaptive Server, enter:

      sp_replication_path "pdb", 'add',
      "pdb_conn2", "PRS",
      "dbmaint2", "dbmaint2pwd"

      (Optionally) Create logical paths that you can use to distribute data and objects bound to a physical path to multiple Replication Servers. See Replication Server Administration Guide Volume 2 > Performance Tuning > Multi-Path Replication > Multiple Primary Replication Paths > Creating Logical Primary Replication Paths .
   2. Create the corresponding alternate primary connection in Replication Server and use the same Adaptive Server data server name with the alternate path name—pdb_conn2:
      At the Replication Server, enter:

      create alternate connection to ASE_DS.pdb
      named ASE_DS.pdb_conn2
      set error class to rs_sqlserver_error_class
      set function string class to rs_sqlserver_function_class
      set username to dbmaint2
      set password to dbmaint2pwd
      with primary only

   The replication system contains two primary replication paths: the default—ASE_DS.pdb, and ASE_DS.pdb_conn2.
5. Use admin show_connections, 'primary' to display the primary connections you have created.
6. Create the default replicate connection to the Sybase IQ database with dbmaint1 as the maintenance user.

   You must specify the connection profile and connection profile version, and a unique maintenance user name for the default connection and each alternate connection.

   create connection to IQSRVR.iqdb
   using profile rs_ase_to_iq;standard
   set username to dbmaint1
   set password to dbmaint1
   go

7. Verify that the connection is running with admin who:
8. Create an alternate replicate connection named IQSRVR.pdb_conn2 to the iqdb Sybase IQ database with dbmaint2 as the maintenance user.

   create alternate connection to IQSRVR.iqdb
   named IQSRVR.pdb_conn2
   using profile rs_ase_to_iq;standard
   set username to dbmaint2
   set password to dbmaint2pwd
   go

   (Optionally) Create alternate connections to available Sybase IQ multiplex nodes. Ensure that the connection names are unique.
   For example, to create the pdb_conn3 alternate connection to the iqdb2 database in the IQSRVR2 Sybase IQ node, enter:

   create alternate connection to IQSRVR2.pdb_conn3
   named IQSRVR2.iqdb2_conn1
   using profile rs_ase_to_iq;standard
   set username to dbmaint3
   set password to dbmaint3pwd
   go

9. Display the replicate connections you have created with admin show_connections, 'replicate'.
10. Enable RTL for the default and alternate replicate connections to Sybase IQ.
    1. Enable RTL for the default connection:

       alter connection to IQSRVR.iqdb
       set dsi_compile_enable to ‘on’
       go

    2. Enable RTL for the IQSRVR.pdb_conn2 alternate connection:

       alter connection to IQSRVR.pdb_conn2
       set dsi_compile_enable to ‘on’
       go

    3. Suspend and resume the connections to the replicate Sybase IQ database:

       suspend connection to IQSRVR.iqdb
       go
       suspend connection to IQSRVR.pdb_conn2
       go
       resume connection to IQSRVR.iqdb
       go
       resume connection to IQSRVR.pdb_conn2
       go

    Restart Replication Server if you use configure replication server to enable RTL or if you want to suspend and resume all connections at the same time.
11. Optionally set values for parameters to configure RTL processing and tune RTL performance.
12. At the primary database, mark the testtab1 and testtab2 tables for replication.

    sp_setreptable testtab1,'true'
    go
    sp_setreptable testtab2,'true'
    go

13. Set the distribution mode at the primary database to distribute by object binding:

    sp_config_rep_agent pdb, ‘multipath distribution model’, 'object’

14. Quiesce Replication Server and restart RepAgent.
    See Replication Server Administration Guide Volume 1 > Manage a Replication System > Quiesce Replication Server > Quiescing a Replication System.
15. Bind the testtab2 table to the ASE_DS.pdb_conn2 alternate connection.

    sp_replication_path pdb, 'bind', "table", "dbo.testtab2", "pdb_conn2"

    You can only bind objects to alternate replication paths. All objects that you do not bind to an alternate replication path, such as the testtab1 table, use the default path instead.
16. Create replication definitions for the marked tables. Add any required referential constraint clauses to the replication definition to support RTL:
    To create the repdef_testtab1 replication definition for the testtab1 table, enter:

    create replication definition repdef_testtab1
    with primary at ASE_DS.pdb1
    with primary table named ‘testtab1’
    with replicate table named dbo.‘testtab1’
    (c1 int, c2 int, c3 char(10))
    primary key(c1)
    go

    To create the repdef_testtab2 replication definition for the testtab2 table, enter:

    create replication definition repdef_testtab2
    with primary at ASE_DS.pdb1
    with primary table named ‘testtab2’
    with replicate table named dbo.‘testtab2’
    (c1 int, c2 int, c3 char(10))
    primary key(c1)
    go

    All replication definitions refer to the default primary connection.
17. Create a subscription to match each table replication definition and specify the connection:
    1. Create the sub_testtab1 subscription for the repdef_testtab1 replication definition and specify the default connection to replicate transactions:

       create subscription sub_testtab1 for repdef_testtab1
       with replicate at IQSRVR.iqdb
       without materialization 
       go

    2. Create the sub_testtab2 subscription for the repdef_testtab2 replication definition and specify the IQSRVR.iqdb_conn2 alternate connection to replicate transactions:

       create subscription sub_testtab2 for repdef_testtab2
       with replicate at IQSRVR.pdb_conn2
       without materialization 
       go

       See Replication Server > Administration Guide Volume 2 > Performance Tuning > Multi-Path Replication > Replication Definitions and Subscriptions > Moving Subscriptions Between Connections.