Number of rows in the resultant set of cursor

kishore erukulapati

Number of rows in the resultant set of cursor
Hi ,
Is there any way to find out the number of rows returned by cursor
without having Seperate Select using COUNT(*) function ?
Thanks
Kishore
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



RICK (SWBT) DAVIS

Re: Number of rows in the resultant set of cursor
(in response to kishore erukulapati)
Kishore,
I don't know how to do it, if its even possible. But, if you are
talking about trying to get the number immediately after the OPEN CURSOR
statement, I wouldn't think so. DB2 may have been able to avoid
materializing the entire result set, so it wouldn't know at that point.

HTH,
Rick Davis
"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."



-----Original Message-----
From: kishore erukulapati [mailto:[login to unmask email]
Sent: Thursday, December 23, 1999 11:32 AM
To: [login to unmask email]
Subject: Number of rows in the resultant set of cursor


Hi ,
Is there any way to find out the number of rows returned by cursor
without having Seperate Select using COUNT(*) function ?
Thanks
Kishore
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com








Viswanathan N

Re: Number of rows in the resultant set of cursor
(in response to RICK (SWBT) DAVIS)
just check up SQLERRD(3) in the sqlca
vishy




kishore erukulapati <[login to unmask email]> on 12/23/99 11:02:12 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>



To: [login to unmask email]

cc: (bcc: Viswanathan N/LTITL)



Subject: Number of rows in the resultant set of cursor









Note: Some recipients have been dropped due to syntax errors.
Please refer to the "$AdditionalHeaders" item for the complete headers.



Hi ,
Is there any way to find out the number of rows returned by cursor
without having Seperate Select using COUNT(*) function ?
Thanks
Kishore
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com








[login to unmask email]

Re: Number of rows in the resultant set of cursor
(in response to Viswanathan N)
Hi Kishore,
I gave a lot of thought but couldn't find out a way of doing this
without COUNT(*).However the existence of a particular row or some rows can
be checked without COUNT(*) .
One more thing is that if the tables for which u want to find out the
total no. of rows are not so frequently used then CARDF from the
SYSIBM.SYSTABLES can be used(Risky)...if the catalog statistics are always
correct.Say, the tables are used in the program which is running once in a
week and u always run ur RUNSTAT for those tables weekly before running the
program then above formula can be used............but it is not a good idea
to do that.

I hope i can get some idea as well from some more answers in this list.

Thanks

Sanjeev





"DAVIS, RICK (SWBT)" <[login to unmask email]>@RYCI.COM> on 12/23/99 07:05:52
PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: Number of rows in the resultant set of cursor


Kishore,
I don't know how to do it, if its even possible. But, if you are
talking about trying to get the number immediately after the OPEN CURSOR
statement, I wouldn't think so. DB2 may have been able to avoid
materializing the entire result set, so it wouldn't know at that point.

HTH,
Rick Davis
"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."



-----Original Message-----
From: kishore erukulapati [mailto:[login to unmask email]
Sent: Thursday, December 23, 1999 11:32 AM
To: [login to unmask email]
Subject: Number of rows in the resultant set of cursor


Hi ,
Is there any way to find out the number of rows returned by cursor
without having Seperate Select using COUNT(*) function ?
Thanks
Kishore
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



the










Viswanathan N

Re: Number of rows in the resultant set of cursor
(in response to ssethi@LOT.TATASTEEL.COM)
I think Kishore is asking for the number of rows returned in a query and
not the total rows in a table. That will be given in the systables after a
runstats.
Vishy



Viswanathan N

Re: Number of rows in the resultant set of cursor
(in response to ssethi@LOT.TATASTEEL.COM)
I had mentioned something in my last mail about SQLERRD(3) of SQLCA. This
gives information for updates / deletes etc. But I am not sure for the
selects. You can check up that . !!

Vishy



[login to unmask email]

Re: Number of rows in the resultant set of cursor
(in response to Viswanathan N)
Yes , i missed out this point.
The number of rows for a particular condition in a query cann't be obtained
by SYSTABLES .
Thanks for correction Vishy.
Any more answers will be helpful for me as well.


Sanjeev





Viswanathan N <[login to unmask email]>@RYCI.COM> on 12/24/99
06:39:38 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: Number of rows in the resultant set of cursor


I think Kishore is asking for the number of rows returned in a query and
not the total rows in a table. That will be given in the systables after a
runstats.
Vishy








Anand Babu

Re: Number of rows in the resultant set of cursor
(in response to Viswanathan N)
Hello Vishy

Can you please clarify this - Is the SQLERRD filled up just after the execution
of the SQL. I had experienced otherwise. We had to code a call for DSNTIAR to
fill up the values. Is there something that is missing ?


Regards

Anand



********************************************************************************
A Hammer May Miss it's Mark But A Compliment ... Never
If you are not living on the edge you are occupying too much space
****************************************


---------------------------------------- Message History
----------------------------------------


From: [login to unmask email] on 12/24/99 06:54 AM GMT

Please respond to [login to unmask email]

To: [login to unmask email]
cc:
Subject: Re: Number of rows in the resultant set of cursor




I had mentioned something in my last mail about SQLERRD(3) of SQLCA. This
gives information for updates / deletes etc. But I am not sure for the
selects. You can check up that . !!

Vishy








Viswanathan N

Re: Number of rows in the resultant set of cursor
(in response to Anand Babu)
no not at all . The SQLERRD field is in the SQLCA. You normally include the
SQLCA in your program . This will help you to directly access the SQLCA
variables. The DSNTIAR is a utility from IBM which will give the
information regarding the errors . like say you give the SQLCODE and it
will give you the meaningful error message .

I hope this clarifies ur doubt.
Vishy



[login to unmask email]

Re: Number of rows in the resultant set of cursor
(in response to Viswanathan N)
Kishore,

SQLERRD(3) doesn't give u the information about no. of rows selected ,it
gives information about no. of rows modified (update,insert or delete)
after the execution of sql statement , for no rows modification or in case
of failure of statement it is 0 and -1 if mass delete is issued in the
segmented tablespace.
It also doesn't give u the information about no. of rows modified(on
reference tables) due to the referential integrity rules

Anand,
this is an SQLCA field and it's value is returned just after the execution
of sql statements , u have to issue the call to DSNTIAR just after the
execution of the sql. I hope u would be calling the DSNTIAR for any failure
or warning condition in ur sqls ,so u can also code EXEC SQL WHENEVER
statement in the application and issue the call in that process which will
be executed when some error or warning condition arises.

I hope i am clear and correct.

Sanjeev





Anand Babu <[login to unmask email]>@RYCI.COM> on 12/24/99 07:01:13 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: Number of rows in the resultant set of cursor


Hello Vishy

Can you please clarify this - Is the SQLERRD filled up just after the
execution
of the SQL. I had experienced otherwise. We had to code a call for DSNTIAR
to
fill up the values. Is there something that is missing ?


Regards

Anand



********************************************************************************

A Hammer May Miss it's Mark But A Compliment ... Never
If you are not living on the edge you are occupying too much space
****************************************


---------------------------------------- Message History
----------------------------------------


From: [login to unmask email] on 12/24/99 06:54 AM GMT

Please respond to [login to unmask email]

To: [login to unmask email]
cc:
Subject: Re: Number of rows in the resultant set of cursor




I had mentioned something in my last mail about SQLERRD(3) of SQLCA. This
gives information for updates / deletes etc. But I am not sure for the
selects. You can check up that . !!

Vishy



the










RICK (SWBT) DAVIS

Re: Number of rows in the resultant set of cursor
(in response to ssethi@LOT.TATASTEEL.COM)
Vishy,
I think the original question asked how to determine the number of
rows that would be returned by a CURSOR. In a previous reply I said I didn't
think this was provided anywhere because the result set isn't always fully
materialized at CURSOR OPEN. If there is a way, I'd like to know too!

You are correct for INSERT, UPDATE, and DELETE. Here's a quote from
the SQL Reference manual of what SQLERRD(3) contains and applies to both DB2
V5 and V6 for OS/390:

"SQLERRD(3) Contains the number of rows affected after INSERT, UPDATE, and
DELETE (but not rows deleted as a result of CASCADE delete). Set to 0 if the
SQL statement fails, indicating that all changes made in executing
the statement were canceled. Set to -1 for a mass delete from a table in a
segmented table space. SQLERRD(3) can also contain the reason code of a
timeout or deadlock for SQLCODES -911 and -913."

Regards,
Rick Davis
"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."



-----Original Message-----
From: Viswanathan N [mailto:[login to unmask email]
Sent: Thursday, December 23, 1999 9:16 PM
To: [login to unmask email]
Subject: Re: Number of rows in the resultant set of cursor


just check up SQLERRD(3) in the sqlca
vishy




kishore erukulapati <[login to unmask email]> on 12/23/99 11:02:12 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>



To: [login to unmask email]

cc: (bcc: Viswanathan N/LTITL)



Subject: Number of rows in the resultant set of cursor









Note: Some recipients have been dropped due to syntax errors.
Please refer to the "$AdditionalHeaders" item for the complete headers.



Hi ,
Is there any way to find out the number of rows returned by cursor
without having Seperate Select using COUNT(*) function ?
Thanks
Kishore
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com













Richard A Yevich

Re: Number of rows in the resultant set of cursor
(in response to RICK (SWBT) DAVIS)
There is no way to know or be told ahead of time the number of rows that
will be retrieved by a SELECT CURSOR, other than doing a COUNT(*) with the
exact same predicates. HOWEVER, this will not necessarily be correct due to
the time of the COUNT(*) and the time of the SELECT, during which data could
have been inserted, updated, and deleted, unless of course you were holding
a gross table lock.

Richard Yevich

> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
> DAVIS, RICK (SWBT)
> Sent: Monday, December 27, 1999 7:01 AM
> To: [login to unmask email]
> Subject: Re: Number of rows in the resultant set of cursor
>
>
> Vishy,
> I think the original question asked how to determine the number of
> rows that would be returned by a CURSOR. In a previous reply I
> said I didn't
> think this was provided anywhere because the result set isn't always fully
> materialized at CURSOR OPEN. If there is a way, I'd like to know too!
>
> You are correct for INSERT, UPDATE, and DELETE. Here's a
> quote from
> the SQL Reference manual of what SQLERRD(3) contains and applies
> to both DB2
> V5 and V6 for OS/390:
>
> "SQLERRD(3) Contains the number of rows affected after INSERT, UPDATE, and
> DELETE (but not rows deleted as a result of CASCADE delete). Set
> to 0 if the
> SQL statement fails, indicating that all changes made in
> executing
> the statement were canceled. Set to -1 for a mass delete from a table in a
> segmented table space. SQLERRD(3) can also contain the reason code of a
> timeout or deadlock for SQLCODES -911 and -913."
>
> Regards,
> Rick Davis
> "This e-mail and any files transmitted with it are the property of SBC,
> are confidential, and are intended solely for the use of the individual
> or entity to whom this e-mail is addressed. If you are not one of the
> named recipient(s) or otherwise have reason to believe that you have
> received this message in error, please notify the sender at 314-235-6854
> and delete this message immediately from your computer. Any other use,
> retention, dissemination, forwarding, printing, or copying of this
> e-mail is strictly prohibited."
>
>
>
> -----Original Message-----
> From: Viswanathan N [mailto:[login to unmask email]
> Sent: Thursday, December 23, 1999 9:16 PM
> To: [login to unmask email]
> Subject: Re: Number of rows in the resultant set of cursor
>
>
> just check up SQLERRD(3) in the sqlca
> vishy
>
>
>
>
> kishore erukulapati <[login to unmask email]> on 12/23/99 11:02:12 PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
>
>
> To: [login to unmask email]
>
> cc: (bcc: Viswanathan N/LTITL)
>
>
>
> Subject: Number of rows in the resultant set of cursor
>
>
>
>
>
>
>
>
>
> Note: Some recipients have been dropped due to syntax errors.
> Please refer to the "$AdditionalHeaders" item for the complete headers.
>
>
>
> Hi ,
> Is there any way to find out the number of rows returned by cursor
> without having Seperate Select using COUNT(*) function ?
> Thanks
> Kishore
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
>
>
>
>
>
>
> To change your subscription options or to cancel your
> subscription visit the
>
>
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]