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
 

 

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 Mainframe Opening in Chennai ( 2 to 4... muralikrishnan_new Mainframe Jobs 0 Tue Jul 18, 2017 2:20 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 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 error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm


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