Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Cursor Optimize

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
cesaralt

New User


Joined: 19 Oct 2009
Posts: 3
Location: LIMA-PERU

PostPosted: Tue May 22, 2012 10:37 am    Post subject: Cursor Optimize
Reply with quote

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.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue May 22, 2012 4:56 pm    Post subject:
Reply with quote

You can drop index I7_TLDFDDLG .

For this query the best index would be to have all 4 columns from the where clause, followed by CLOG DESC.

But you can't just make the perfect index for each statement. it takes a little more consideration and balancing between too many and too little indexes.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Optimize sort inrec parse vishwakotin DFSORT/ICETOOL 6 Mon Jun 26, 2017 11:15 pm
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us