Hi,
We are replicating over 200 tables from each production 'server' (13
at the moment, about 140 when all are configured) and noticed a
large amount of additional IO's after activating replication.
I thought that the apply might be the one causing this but after
capturing the SQL statements it looks like the capture process is
causing those IO's.
Within 15 minutes the ASN.IBMSNAP_REGISTER table was read about
100000 times. When examining these sections it looks like the table
doesn't have the right indexes. Almost all sections within the
ASNNN706 package which do read the register table need to read all
the rows from the table.
The following SQL statements from the ASNNN706 package are causing
almost all IO's against the register table:
-------------------------------------------------
Section = 22
SQL Statement:
UPDATE ASN.IBMSNAP_REGISTER SET CD_NEW_SYNCHPOINT = :H00116 :H00031
WHERE PHYS_CHANGE_OWNER = :H00152 AND PHYS_CHANGE_TABLE = :H00153
Section = 156
SQL Statement:
UPDATE ASN.IBMSNAP_REGISTER SET SYNCHPOINT = :H00123 :H00155 ,
SYNCHTIME = :H00181 :H00155
WHERE GLOBAL_RECORD = 'Y'
Section = 166
SQL Statement:
SELECT MIN(SYNCHPOINT) INTO :H00116 :H00031
FROM ASN.IBMSNAP_PRUNCNTL A
WHERE A.SOURCE_OWNER CONCAT(A.SOURCE_TABLE) IN
(SELECT B.SOURCE_OWNER CONCAT(B.SOURCE_TABLE)
FROM ASN.IBMSNAP_REGISTER B
WHERE PHYS_CHANGE_TABLE = :H00153 AND PHYS_CHANGE_OWNER =
:H00152 ) AND A.SYNCHPOINT IS NOT NULL AND A.SYNCHPOINT
X'00000000000000000000'
------------------------------------------------
Section 166 was executed over 400 times within 15 minutes so the
solution could be to add an additional index with at least the
PHYS_CHANGE_OWNER and PHYS_CHANGE_TABLE columns (SOURCE_OWNER and
SOURCE_TABLE are both in the unique index already), the section 22
update would also use this index.
Has anybody done this before? Could this 'solution' be the cause of
other problems (too much locking, deadlocks)?
Do I need to add other indexes to the replication tables?
I hope somebody can help with this problem.
TIA.
Kind regards, Gert
Ps. don't reply be email, it's a fake address (if email is the only
option use google to find my real email address :)
