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
 

 

Why do we go for CURSOR instead of SELECT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
lucky.rohith
Currently Banned

New User


Joined: 06 Aug 2006
Posts: 10

PostPosted: Thu Sep 28, 2006 9:11 am    Post subject: Why do we go for CURSOR instead of SELECT
Reply with quote

Hi
This was the question asked by one intervier.
The question is
Why do v go for CURSOR instead of SELECT.Through these two v can select all the rowsin the table,Then why v go for CURSOR.
He asked to explain tat difference.
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Sep 28, 2006 10:39 am    Post subject:
Reply with quote

Hi lucky,

If the query return multiple rows then we need to use cursor to process every record of resultset that cant be possible in using select in program.
Back to top
View user's profile Send private message
cj10

New User


Joined: 18 Sep 2006
Posts: 9

PostPosted: Thu Sep 28, 2006 11:43 am    Post subject:
Reply with quote

The host program can process only 1 row at a time. The SELECT query might return multiple rows, but for the program to process 1 at a time, we require cursors.
Back to top
View user's profile Send private message
lucky.rohith
Currently Banned

New User


Joined: 06 Aug 2006
Posts: 10

PostPosted: Sun Oct 01, 2006 10:26 am    Post subject:
Reply with quote

HI Guptae,
Thanks fpr ur reply.But i'm not satisfied with ur reply.I hav answered the same thing to my intervier,he was not satisfied too.
So can u explain tat throughexample.

Thanks
lucky
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Sun Oct 01, 2006 8:23 pm    Post subject:
Reply with quote

Hi Lucky,

If we have Employee Db2 table & if we want to write empoyees record whose salary > 10000 in to O/p File then we have to go for cursor.

Employee db2 table: Emp name, DOJ, Basic Pay ,Emp code

O/p File : Emp name , Doj , Basic pay, Emp code , HRA , TA

HRA should be calculated 20% of basic pay &
TA should be 15% of basic Pay

SO we will use cursor in main program & fetch record by record then write in to flat file after calculating HRA ,TA

Hope I make myself clear enogh.
Back to top
View user's profile Send private message
lucky.rohith
Currently Banned

New User


Joined: 06 Aug 2006
Posts: 10

PostPosted: Mon Oct 02, 2006 8:32 am    Post subject:
Reply with quote

Hi Guptae,
Thanks for ur reply.I got the use of the Cursor.But when we wil go for cousor and wen for simple select.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Mon Oct 02, 2006 12:53 pm    Post subject:
Reply with quote

Hi Lucky,

U need to go for cursor when u know the result of ur query is more than 1 row & u want to process them otherwise go for simple select.
Back to top
View user's profile Send private message
meetreks

New User


Joined: 28 Sep 2006
Posts: 26
Location: Edinburgh

PostPosted: Mon Oct 02, 2006 7:36 pm    Post subject: Re: Why do we go for CURSOR instead of SELECT
Reply with quote

Hi,

The reason why we dont use a SELECT when multiple rows/table are possible is, we get a -811 when we do so. This exception is avoided when we use a cursor. The reason behind it is simple.

When we sue a cursor, contary to the poppular belief that only one row is selected during the execution of the FETCH statement, DB2 actually retuns all qualified rows/paging size for the FETCH statement. But then manner in which they are communicated back to the application program slightly differs from that of the SELECT statement.

While we use the FETCH , information about only one row is communicated back in the communication area (SQLCA) and hence all the information will be stable and correct.

In SELECT, DB2 cannot correctly provide the information in the communication area, and hence you could have incorrect information in the communication area. ALso DB2 cannot confirm that you will always get the same row whenever you reissue the SELECT.

Hope this helps.
Back to top
View user's profile Send private message
lal_arun_272

New User


Joined: 09 Oct 2006
Posts: 31
Location: Bangalore

PostPosted: Mon Oct 09, 2006 8:14 pm    Post subject: Re: Why do we go for CURSOR instead of SELECT
Reply with quote

Why do we use cursor ???.

I use to see when ever some one ask this question, every one will go for select statment is retrive more than one row we will go for cursor. else the job end with 811.

Let me put it different way..

1. in SQL if can write select statment with into clause which retrive more than one row we don't need cursor. <<DB2 won't have such select statment>>so we should use cursor

2.we can update or delete rows from the table using update or DELETE statment using queries But think before update or delete we need to check what is existing value thier ...that case we should use cursor



Thanks
Arunlal
Back to top
View user's profile Send private message
Jotedup7

New User


Joined: 24 Sep 2006
Posts: 1

PostPosted: Tue Oct 10, 2006 9:28 am    Post subject:
Reply with quote

lol check this one icon_biggrin.gif
http://thefunnyvids.org
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 SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 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
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


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