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
 

 

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 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
No new posts FTOPEN TEMP is failing due to dataset... mbenaud TSO/ISPF 7 Thu Jul 28, 2016 4:28 pm


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