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
 
number of rows while opening the cursor

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

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Sat May 13, 2006 3:29 pm    Post subject: number of rows while opening the cursor
Reply with quote

Hi,

In DB2 application program , can we know the number of rows upfront at the time of opening the cursor?

Thanks,
Parikshit.
Back to top
View user's profile Send private message

ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Mon May 15, 2006 11:45 pm    Post subject:
Reply with quote

Hi,

Could you please explain more and what number of rows you want to count?

As far as what I understand :

1. you can know how many rows match your query by using SELECT COUNT(*) ........................

2. CURSOR will fetch only 1 row at a time.

Correct me if I am wrong.


Thanks & Regards,

Ranjit...
Back to top
View user's profile Send private message
getdpk

New User


Joined: 06 Feb 2006
Posts: 2

PostPosted: Tue May 16, 2006 11:58 am    Post subject: Re: number of rows while opening the cursor
Reply with quote

It is not possible to get the Count of rows while opening a Cursor.

The Only way to do is, Use COUNT(*) before using the cursor.

Thanks,
Deepak
Back to top
View user's profile Send private message
Brian's_song

New User


Joined: 27 Dec 2005
Posts: 21

PostPosted: Sun May 21, 2006 3:04 pm    Post subject:
Reply with quote

you create a variable and then add 1 to variable every time you get a rec by the cursor.
Back to top
View user's profile Send private message
Gurmeet

New User


Joined: 22 Feb 2006
Posts: 46
Location: Pune

PostPosted: Tue May 23, 2006 10:47 pm    Post subject: Re: number of rows while opening the cursor
Reply with quote

You might be using a where clause to define your cursor, use the same where clause with Select count(*), should give u the count...

~Gurmeet
Back to top
View user's profile Send private message
vijayamadhuri

Active User


Joined: 06 Apr 2005
Posts: 180

PostPosted: Tue May 23, 2006 11:56 pm    Post subject: Re: number of rows while opening the cursor
Reply with quote

When you execute a SELECT statement, you retrieve a set of rows. That set
of rows is called the result table. In an application program, you need a
way to retrieve one row at a time from the result table into host
variables. A cursor performs that function.
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Thu Jun 08, 2006 12:51 pm    Post subject:
Reply with quote

Hi all,

Thanks for your response.
It seems to me that there is no way to find out the number of rows the cursor has selected (in the result table) at the time of opening the cursor.

I have put count(*) before opening the cursor to solve my problem.

I was interested if there is any variable in SQLCA which gives the number of rows as well.

Thanks for your help.
Back to top
View user's profile Send private message
Hanfur

Active User


Joined: 21 Jun 2006
Posts: 104

PostPosted: Mon Jul 03, 2006 11:08 am    Post subject: Hi
Reply with quote

Hi,

When you perform a cursor controlled INSERT,UPDATE,DELETE
after the operation the number of rows affected would be updated in the
SQLERRD(3) field of SQLCA.

But not sure of any /meachanism/method ; which would give the number of rows in result set at the time when a Cursor Opened.


-Han.
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Mon Jul 03, 2006 12:52 pm    Post subject:
Reply with quote

Great,
It seems to me that there is no mechanism available in DB2 application program that give you the number of rows in the cursor at the time of opening it.
Its safe to use a select count(*) query before opening the cursor.

Thanks a lot to all of you for your help though !
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Wed Jul 05, 2006 5:09 pm    Post subject: Re: number of rows while opening the cursor
Reply with quote

Oops!

Actually GET DIAGNOSTICS v_rowcount = ROW_COUNT;

gets the number of rows retrived when u open the cursor!

Try this and let us all know how it worked!

Thanks,
Ravi
Back to top
View user's profile Send private message
Hanfur

Active User


Joined: 21 Jun 2006
Posts: 104

PostPosted: Thu Jul 06, 2006 10:39 am    Post subject: Hi
Reply with quote

Ravi,
Is it a Cobol compiler directive ?

Where this has to be coded to get the data on ROW_COUNT?


-Han.
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Thu Jul 06, 2006 10:58 am    Post subject:
Reply with quote

Hi Ram,

I think your program will give you an error while BIND. I think -204. Invalid token.

Ram, I would suggest you not to post any irrrelevent question ( in the context of this topic, your question is irrelevent). You could have opened another topic instead. icon_smile.gif

Thanks,
Parikshit
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Thu Jul 06, 2006 3:33 pm    Post subject: Re: number of rows while opening the cursor
Reply with quote

Hans,

GET_DIAGNOSTIC is not an cobol directives.The GET DIAGNOSTICS statement provides diagnostic information about the last SQL statement
similar to SQLCA and SQLCODE

Work fine with V7 and higher version.

row_count should be of type Integer.

EXEC SQL GET_DIAGNOSTIC :row_count =ROW_COUNT;

Thanks,
Ravi
Back to top
View user's profile Send private message
Hanfur

Active User


Joined: 21 Jun 2006
Posts: 104

PostPosted: Thu Jul 06, 2006 5:08 pm    Post subject: Hi
Reply with quote

Ravi,
Thanks for your reply..that makes sense...

-Han.
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 Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 1 Wed Dec 06, 2017 1:50 am
No new posts Can we combine the rows with same key... V S Amarendra Reddy DB2 12 Fri Dec 01, 2017 10:29 pm
No new posts Increment number for every group of r... srajendran2 DFSORT/ICETOOL 7 Thu Nov 09, 2017 10:31 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

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