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
 

 

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: 10210
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: 6966
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 Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
No new posts JCL to fetch schedule status from act... parasmalik20 CA Products 1 Thu Jun 02, 2016 7:11 pm
No new posts ABEND due to FETCH FAILED jackzhang75 ABENDS & Debugging 17 Thu Mar 10, 2016 3:08 am


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