Results 1 to 7 of 7

Thread: Replication

  1. #1
    Join Date
    Apr 2004
    Posts
    190

    Unanswered: Replication

    Is there a limit to the number of tables you can replicate per subscription?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you are using SQL replication, the limit is 200.

    Andy

  3. #3
    Join Date
    Apr 2004
    Posts
    190
    Is there any "undocumented" way around this limit?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use Q replication.

    Andy

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can have more than one subscription in regular DB2 Replication.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, you can use more that one subscription. But you have to be very careful in setting them up. If there is ant RI between the tables of the different subscriptions, replication can fail. SQL replication works at the subscription level when moving data. And the order that the data move is not guaranteed the same order that actually occurred. As an example, say you have two tables that have RI defined between them. Due to the 200 table limitation, you have to put them in separate subscriptions. On the "Source" DB someone inserts a new row in the parent table then very shortly after inserts/updates a row in the child to point to the new row in the parent. All of this happens "between" refresh cycles of APPLY. Then APPLY does a refresh and starts with the subscription that the child table belongs. It tries to do the insert/update that occurred on the source, but it fails because because the RI prevents the insert/update since the "new" parent row has not been inserted yet (APPLY has not gotten there yet).

    This scenario can be fixed if you put all RI related tables in the same subscription. The problem still occurs if you have more than 200 tables all with RI to each other. There is no way to split it that will guarantee that the Units of Work (UOW) are processed in the exact same order on the destination as they were performed on the source. It has been awhile since I had tried using SQL replication, so IBM may have done something to fix this problem.

    Andy

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You haven't mentioned what version you are on ...

    If splitting the subscription is not possible, you can create Views (IIRC, views can be target, but not source) on the target and create INSTEAD OF Triggers to update various tables.

    I haven't tried doing this, but just a thought

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •