How to setup Horizontal Partition on a large table to increase performance (45139)

How to setup Horizontal Partition on a large table to increase performance

1. On the source
sp_ctrl>copy config <current config file> to <new config file>

2. Edit newconfig and add or remove the post queue name(in routing) for the tables you want to isolate
sp_ctrl > edit config <new config file>
Also edit the shareplex_partition table

Example to create partitioned replication and configuration

insert into owner,shareplex_partition(partition_schema,description,route,target_table_name,ordering,
col_conditions) values ('sales_partition_west', 'Replicate data for western region', 'targetsystem@o.target_SID', 'customer_order', 1,'region=west');

insert into owner,shareplex_partition(partition_schema,description,route,target_table_name,ordering,
col_conditions) values ('sales_partition_east', 'Replicate data for western region', 'targetsystem@o.target_SID', 'customer_order', 1,'region=east');

insert into owner,shareplex_partition(partition_schema,description,route,target_table_name,ordering,
col_conditions) values ('sales_partition_south', 'Replicate data for western region', 'targetsystem@o.target_SID', 'customer_order', 1,'region=south');

insert into owner,shareplex_partition(partition_schema,description,route,target_table_name,ordering,
col_conditions) values ('sales_partition_north', 'Replicate data for western region', 'targetsystem@o.target_SID', 'customer_order', 1,'region=north');

datasource:o.src_SID

owner.customer_order  owner.customer_order

  !sales_partition_west                                                                                                                                                                                          

!   target_host: 'WEST@o.target_sid'

owner.customer_order  owner.customer_order                       

!sales_partition_east

!    target_host: 'EAST@o.target_sid'

owner.customer_order   owner.customer_order

!sales_partition_south

!     target_host: 'SOUTH@o.target_sid'

owner.customer_order  owner.customer_order

!sales_partition_north

!     target_host: 'NORTH@o.target_sid'


3.  stop post on target

sp_ctrl> stop post

4. Activate new config on the source
sp_ctrl> activate config configname


5. start old post queue on target and wait until all messages drained.

sp_ctrl> start post queue <old post queuename>

Once old post queue has 0 messages in backlog, then start new post queue

sp_ctrl> start post queue <new post queuename>

6. Wait until the backlog message reaches 0 for old post queue, then delete old post queue
shutdown shareplex on the target
qview -i
qview>qsetup <port#>
qview>deleteq P
qview>exit

truncate shareplex_trans table on target

7. start Shareplex on the target