Multiple Select Threads

When a replicate table is materialized, Replication Server now allows more than one select threads.

In earlier releases, when a replicate table is materialized, Replication Server would spawn one select thread to get data from the primary table. To improve the performance of materialization, the syntax for creating a subscription with the direct_load option is extended to allow more than one select threads. Also, the maximum value of the direct load materialization configuration parameter max_mat_load_threads is changed from 20 to 80.

Syntax

create subscription <sub_name>
for {table_repdef | func_repdef | publication pub |
     database replication definition <db_repdef> }
     [ with primary at <server_name.db> ]
with replicate at <data_server.database
>
[where {column_name | @param_name}
     {< | > | >= | <= | = | &} value
[and {column_name | @param_name}
     {< | > | >= | <= | = | &} value]...]
[without holdlock [direct_load [init replicate table with {create | create_or_truncate | truncate | recreate}]
[user <username> password <pass>][<num_of_selects> selects [ on <column_name> |
                          with ( <condition1>, <condition2> [, <condition3> ] [, <condition4>]...)]][hold_resource_on_error]]| incrementally | without materialization][subscribe to truncate table]
[for new articles]

The num_selects column added to the rs_subscriptions system table in SAP Replication Server 15.7.1 SP202 accepts values 1 (default) through 10.

Example 1

(Only supported for DB2 version 9.7 or higher, and Oracle) In this example, there are four select threads.

create subscription sub1 for repdef1 with replicate at rds1.rdb1
where col1 > 0 and col1 <= 1000000
without holdlock  direct_load
4 selects

Example 2

(Only supported for DB2 version 9.7 or higher, and Oracle) In this example, the data is divided among the select threads using column <col1>. <col1> is part of primary key or searchable columns.

create subscription sub1 for repdef1 with replicate at rds1.rdb1
where col1 > 0 and col1 <= 1000000
without holdlolck  direct_load
4 selects on col1

Example 3

(Supported for Adpative Server, Microsoft SQL Server, Oracle, and IBM DB2 UDB) In this example, there are four select threads, with user provided where clauses. Here, select thread 1 handles data with <col2> < 10000, select thread 2 handles data with <col2> >= 10000 and <col2> < 20000, select thread 3 handles data with <col2> >= 20000 and <col2> < 30000, and select thread 4 handles data with <col2> >= 30000.

create subscription sub1 for repdef1 with replicate at rds1.rdb1
where col1 > 0 and col1 <= 1000000
without holdlock  direct_load
                 4 selects with (col2 < 10000,
                                 col2 >= 10000 and col2 < 20000,
                                 col2 >= 20000 and col2 < 30000,
                                 col2 >= 30000)