cesaralt
New User
Joined: 19 Oct 2009
Posts: 3
Location: LIMA-PERU
|
|
|
|
A table TLDFDDLG has about 4’594,244 registers, a cursor is defined as follow:
EXEC SQL
DECLARE CURSORLG CURSOR FOR
SELECT
CLOG,
BLOG,
GLOG,
.
.
.
EYE_DAT
FROM TLDFDDLG
WHERE FSUBIDA = :WS-FSUBIDA
AND NSESION = :WS-XNSESION
AND TIZTRNID = :WS-TIBTRNID
AND CUSERID = :WS-CUSERID
ORDER BY CLOG DESC
END-EXEC.
This cursor is high CPU time consuming. The table TLDFDDLG has the following indexes:
D.LIB.DDL.DB2(TLDTDDLG)
CREATE UNIQUE INDEX I0_TLDFDDLG ON TLDFDDLG
(CLOG, FSUBIDA)
USING STOGROUP GDTLD001 PRIQTY 100 SECQTY 100
ERASE NO BUFFERPOOL BP2 CLOSE YES;
CREATE INDEX I1_TLDFDDLG ON TLDFDDLG
(CUSERID,FSUBIDA,CLOG)
USING STOGROUP GDTLD001 PRIQTY 100 SECQTY 100
ERASE NO BUFFERPOOL BP2 CLOSE YES;
COMMIT;
CREATE INDEX I2_TLDFDDLG ON TLDFDDLG
(FSUBIDA,NSESION,FLOG)
USING STOGROUP GDTLD001 PRIQTY 100 SECQTY 100
ERASE NO BUFFERPOOL BP2 CLOSE YES;
COMMIT;
CREATE INDEX I3_TLDFDDLG ON TLDFDDLG
(TS_PROC)
USING STOGROUP GDTLD001 PRIQTY 100 SECQTY 100
ERASE NO BUFFERPOOL BP2 CLOSE YES;
COMMIT;
CREATE INDEX I5_TLDFDDLG ON TLDFDDLG
(FSUBIDA, CTRANSID)
USING STOGROUP GDTLD001 PRIQTY 100 SECQTY 100
ERASE NO BUFFERPOOL BP2 CLOSE YES;
COMMIT;
CREATE INDEX I6_TLDFDDLG ON TLDFDDLG
(FSUBIDA,CLOG)
USING STOGROUP GDTLD001 PRIQTY 100 SECQTY 100
ERASE NO BUFFERPOOL BP2 CLOSE YES;
CREATE INDEX I7_TLDFDDLG ON TLDFDDLG
(FSUBIDA)
USING STOGROUP GDTLD001 PRIQTY 100 SECQTY 100
ERASE NO BUFFERPOOL BP2 CLOSE YES;
--PRUEBA
CREATE INDEX I4_TLDFDDLG ON TLDFDDLG
(DIASEM ASC)
USING STOGROUP GDTLD001 PRIQTY 100 SECQTY 100
ERASE NO
Do I have to crete another index to improve the OPEN Cursor?
Thanks for any help,
Regards,
Cesar A. |
|
