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
 
what will happen when we say open cursor

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

New User


Joined: 22 Jun 2009
Posts: 11
Location: India

PostPosted: Mon Feb 21, 2011 3:47 pm    Post subject: what will happen when we say open cursor
Reply with quote

hi

When in a program an open cursor commands gets executed, I know that the SQL in the cursor gets executed and a resultant table will be created, cursor is placed at the first row and rows are ready for fetch.

But what will happen to open cursor when we have ORDER by clause involved in the SQL statement.

My second question is

IS it always rule of thumb that the SQL in the cursor gets executed and the resultant table is produce at the time of opening of cursor or do we have exceptions.

Could someone please help me with this.

Many thanks
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Feb 21, 2011 4:24 pm    Post subject:
Reply with quote

the answer to the second question is NO.
Sometimes (almost) nothing is done when a cursor is opened (1)
Sometimes the sql is executed and a temporary result table is built. (2)

The question is : when (1) and when (2).
The answer is : check the accesspath, when it contains a work table is it is (2).

Often but not always, when you use ORDER BY , a work table is needed. Sometimes even without ORDER BY, a work table is used.

That's the best answer I can give without a full course of accesspaths and optimizer decisions.
Back to top
View user's profile Send private message
G1NXU6T

New User


Joined: 22 Jun 2009
Posts: 11
Location: India

PostPosted: Fri Feb 25, 2011 6:47 pm    Post subject:
Reply with quote

thanks GuyC.

But how can I check in the access path weather work table is created or not.

Which field will give me this value.

Thanks.[/quote]
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Feb 25, 2011 8:28 pm    Post subject:
Reply with quote

It's not just one field that would tell you.
A few possibilities
- if there is a sort step in the plan_table
- if there is list prefetching
-...
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 Rexx command to open an another text ... Bharath Vikraman PC Guides & IT News 7 Sat May 12, 2018 4:34 pm
No new posts -502 Error - Cursor already open anand jeyapaul DB2 7 Sun Oct 29, 2017 4:22 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Reply MQ open error Vignesh Sid COBOL Programming 6 Thu Sep 07, 2017 6:02 pm
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm

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