Millions of rows in IBMQREP_SPILLEDROW causing high CPU usage

Technote (FAQ)


Question

How can I reduce CPU usage when there are many rows in the IBMQREP_SPILLEDROW table?

Answer

Information is inserted into this table when Q subscriptions are being loaded or spilled. If CPU spikes when you run this query:

SELECT DISTINCT(S.SPILLQ)
FROM "DBQRPTV0".IBMQREP_SPILLEDROW S
WHERE S.SPILLQ NOT IN (SELECT T.SPILLQ
FROM "DBQRPTV0".IBMQREP_TARGETS T
WHERE T.STATE <> 'A' AND T.STATE <> 'I' AND T.SPILLQ = S.SPILLQ)
FETCH FIRST 1 ROW ONLY;

Then try issuing the RUNSTATS command on IBMQREP_SPILLEDROW and IBMQREP_TARGETS and create the following index:

CREATE INDEX DBQRPTV0.IX4TARGETS
ON DBQRPTV0.IBMQREP_TARGETS
(
SPILLQ ASC,
STATE ASC
)
USING STOGROUP SGSMS00
PRIQTY -1
SECQTY -1
ERASE NO
FREEPAGE 0
PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP3
CLOSE YES
COPY NO
PIECESIZE 2G
NOT PADDED
;


Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

InfoSphere Replication Server
Q Apply

Software version:

10.1

Operating system(s):

z/OS

Reference #:

1645662

Modified date:

2013-08-17

Translate my page

Machine Translation

Content navigation