Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Optimise a Fetch cursor

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

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Mon Aug 23, 2010 2:19 pm    Post subject: Optimise a Fetch cursor
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10327
Location: italy

PostPosted: Mon Aug 23, 2010 2:39 pm    Post subject: Reply to: Optimise a Fetch cursor
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
Warnings : 1

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Mon Aug 23, 2010 2:42 pm    Post subject:
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: 6968
Location: porcelain throne

PostPosted: Mon Aug 23, 2010 2:44 pm    Post subject:
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
Warnings : 1

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Mon Aug 23, 2010 4:51 pm    Post subject:
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: 8593
Location: Back in jolly old England

PostPosted: Mon Aug 23, 2010 4:59 pm    Post subject:
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
Warnings : 1

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Thu Sep 02, 2010 1:15 pm    Post subject:
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    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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
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

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us