IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Optimise a Fetch cursor


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Mon Aug 23, 2010 2:19 pm
Reply with quote

Hi,

I have the below cursor which takes more time to fetch.Could anyone suggest how t optimise the cursor.

EXEC SQL DECLARE CURS-EPCF CURSOR FOR
SELECT EPCF_REF,
EPCF_CPT_CLI,
EPCF_CPT_FOUR,
EPCF_CD_TYP_SOLLOG
FROM TDEPCF
WHERE ( (EPCF_CPT_CLI = :SYS-USINE AND
(EPCF_CD_TYP_SOLLOG = 'E' OR
EPCF_CD_TYP_SOLLOG = 'I' ))
OR
(EPCF_CPT_FOUR = :SYS-USINE AND
EPCF_CD_TYP_SOLLOG = 'S' ) )

AND EPCF_DT_APPLI <= :W-TIMESTAMP
AND EPCF_DT_FIN > :W-DT-TRT
AND EPCF_CD_LOCA = :EPCF-CD-LOCA
FOR FETCH ONLY
END-EXEC.



Thanks in advance.

Regards,
Rajan BOSE
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Aug 23, 2010 2:39 pm
Reply with quote

h
Quote:
ave the below cursor which takes more time to fetch

explain please ... more than what ???
Back to top
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Mon Aug 23, 2010 2:42 pm
Reply with quote

Hi,

As per my understanding the fetch takes more time in the below step

WHERE ( (EPCF_CPT_CLI = :SYS-USINE AND
(EPCF_CD_TYP_SOLLOG = 'E' OR
EPCF_CD_TYP_SOLLOG = 'I' ))
OR
(EPCF_CPT_FOUR = :SYS-USINE AND
EPCF_CD_TYP_SOLLOG = 'S' ) )


Is there any other optimised way to declare the cursor?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Aug 23, 2010 2:44 pm
Reply with quote

Quote:
more time to fetch


than what?

run your select thru explain and work with the output to 'optimize' your sql.
Back to top
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Mon Aug 23, 2010 4:51 pm
Reply with quote

Hi Dick,

Thanks.I have solved the problem.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Mon Aug 23, 2010 4:59 pm
Reply with quote

Perhaps you may wish to post the resolution - in the vain hope that someone else with a similar question might actaully use the search button icon_biggrin.gif
Back to top
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Thu Sep 02, 2010 1:15 pm
Reply with quote

Hi,

Sorry for the late response.I haave created a new index to solve my problem.

Regards,
Rajan BOSE.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Fetch data from programs execute (dat... DB2 3
No new posts Code Multi Row fetch in PL1 program PL/I & Assembler 1
No new posts Need to fetch data from so many DB2 t... DB2 9
No new posts fetch the record number from FMNMAIN ... CLIST & REXX 10
No new posts Is SQLCODE -811 possible while fetchi... DB2 1
Search our Forums:

Back to Top