===========================
General
===========================
Replicat process is running slow.
What to check?
Quick solution:
SEND REPLICAT REP_I_01 NOHANDLECOLLISIONS OWNER.TABLE
===========================
Commands
===========================
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPM_I_01 00:00:00 00:00:01
EXTRACT RUNNING DPM_P_01 00:00:00 00:00:02
EXTRACT RUNNING DPM_S_01 00:00:04 00:00:08
EXTRACT RUNNING EXT_I_01 00:00:02 00:00:07
EXTRACT RUNNING EXT_P_01 00:00:03 00:00:01
EXTRACT RUNNING EXT_S_01 00:00:03 00:00:01
REPLICAT RUNNING REP_I_01 83:00:54 03:24:01
REPLICAT RUNNING REP_P_01 00:00:00 00:00:00
REPLICAT RUNNING REP_S_01 00:00:05 00:00:01
GGSCI (ITMIL7DB00001) 12> SEND REPLICAT REP_I_01 STATUS
Sending STATUS request to REPLICAT REP_I_01 ...
Current status: Processing data
Sequence #: 1,046
RBA: 205,827,294
116,969 records in current transaction.
GGSCI (ITMIL7DB00001) 13> SEND REPLICAT REP_I_01 GETLAG
Sending GETLAG request to REPLICAT REP_I_01 ...
Last record lag 311,350 seconds.
===========================
Resolution
===========================
Remove NOHANDLECOLLISIONS from Replicat configuration on both sites.
Checking output of STATS command, the replicat is 90% of the time, is busy parsing collisions.
Before NOHANDLECOLLISIONS:
GGSCI (ITMIL7DB00001) 30> SEND REPLICAT REP_I_01 STATS
Sending STATS request to REPLICAT REP_I_01 ...
Start of Statistics at 2020-09-11 20:49:03.
Replicating from OWNER.SGA_SUBSCRIBER_SFI to OWNER.SGA_SUBSCRIBER_SFI:
*** Total statistics since 2020-09-09 07:57:00 ***
Total inserts 7047.00
Total updates 159072656.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 9701.00
Total operations 159079703.00
Total insert collisions 1370.00
Total update collisions 159071473.00
*** Daily statistics since 2020-09-11 00:00:00 ***
Total inserts 2657.00
Total updates 54334620.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 9701.00
Total operations 54337277.00
Total insert collisions 365.00
Total update collisions 54333908.00
*** Hourly statistics since 2020-09-11 20:00:00 ***
Total inserts 121.00
Total updates 2078586.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 9701.00
Total operations 2078707.00
Total insert collisions 21.00
Total update collisions 2078554.00
*** Latest statistics since 2020-09-09 07:57:00 ***
Total inserts 7047.00
Total updates 159072656.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 9701.00
Total operations 159079703.00
Total insert collisions 1370.00
Total update collisions 159071473.00
Stop Collision Handling for table SGA_SUBSCRIBER_SFI on both servers, A and B.
SEND REPLICAT REP_I_01 NOHANDLECOLLISIONS OWNER.SGA_SUBSCRIBER_SFI
After NOHANDLECOLLISIONS
Replicating from OWNER.SGA_SUBSCRIBER_SFI to OWNER.SGA_SUBSCRIBER_SFI:
*** Total statistics since 2020-09-09 07:57:00 ***
Total inserts 8091.00
Total updates 159072831.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 46304442.00
Total operations 159080922.00
Total insert collisions 1370.00
Total update collisions 159071473.00
*** Daily statistics since 2020-09-12 00:00:00 ***
Total inserts 384.00
Total updates 37.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 20684763.00
Total operations 421.00
*** Hourly statistics since 2020-09-12 02:00:00 ***
Total inserts 48.00
Total updates 2.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 5417534.00
Total operations 50.00
After some time the lag is resolved:
GGSCI (ITMIL7DB00001) 67> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPM_I_01 00:00:00 00:00:06
EXTRACT RUNNING DPM_P_01 00:00:00 00:00:03
EXTRACT RUNNING DPM_S_01 00:00:00 00:00:02
EXTRACT RUNNING EXT_I_01 00:00:03 00:00:08
EXTRACT RUNNING EXT_P_01 00:00:02 00:00:01
EXTRACT RUNNING EXT_S_01 00:00:01 00:00:04
REPLICAT RUNNING REP_I_01 00:00:00 00:00:05
REPLICAT RUNNING REP_P_01 00:00:00 00:00:03
REPLICAT RUNNING REP_S_01 00:00:02 00:00:00
Instead of having duplicate insert, the insert is converted to Update.
- If a row with the new key exists in the target, Replicat deletes the row that has the old key, and the row with the new key is updated, effectively replacing the old row.
Deleted in source but row not present in target
Ignored
Option A - in REPLICAT parameter file + restart the replicat
With Option B, no need for restart.
HANDLECOLLISIONS | NOHANDLECOLLISIONS
Steps to reset Replicat to begin Now
===========================
It can be used only if Extract logs all columns, and not just the changed colums + PK columns
REFRESH REPLICAT <EXTRACT GROUP>
