× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



ROW_COUNT isn't that helpful IMHO

Identifies the number of rows associated with the previous SQL statement
that
was executed. If the previous SQL statement is a DELETE, INSERT, REFRESH,
or UPDATE statement, ROW_COUNT identifies the number of rows deleted,
inserted, or updated by that statement, excluding rows affected by either
triggers or referential integrity constraints. If the previous SQL
statement is a
MERGE statement, ROW_COUNT identifies the total number of rows deleted,
inserted, and updated by that statement, excluding rows affected by either
triggers or referential integrity constraints. If the previous SQL
statement is a
multiple-row-fetch, ROW_COUNT identifies the number of rows fetched.
Otherwise, the value zero is returned.

DB2_NUMBER_ROWS is more helpful

If the previous SQL statement was an OPEN or a FETCH which caused the
size of the result table to be known, returns the number of rows in the
result
table. For SENSITIVE cursors, this value can be thought of as an
approximation since rows inserted and deleted will affect the next
retrieval of
this value. Otherwise, the value zero is returned.

Assuming the CGI program is returning the results via an open cursor...

Charles


On Wed, Jun 17, 2020 at 3:14 PM Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:

Why can you not simply run the regularar select and then immediately
afterwards:

exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ;

That is in theory more accurate since when you do two selects the database
may have changed between the two. Mostly theoretical I know but ...


Jon Paris

On Jun 17, 2020, at 5:03 PM, Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

Nathan...

I don't have that kind of control (I also don't know what you mean by a
"fetch loop"). I am an RPG programmer that has learned SQL as went along.

The application I'm using runs two separate SQL statements.

1. Select count(*) from my_sql_view where...
Then
2. Select field1, field2, field3, etc. from my_sql_view where...

The file and where clause are always the same.
The app creates a grid in the browser and displays a row count at the
bottom.
The app has filters that likely add to the SQL statement.

I'm just trying to get the "count" statement to not take 6x as long as
the select rows statement.
But I can't really change the fundamental way the software works... it
runs two separate statements.

Thx,
Greg

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On
Behalf Of Nathan Andelin
Sent: Wednesday, June 17, 2020 2:17 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx

Subject: Re: Hash Table Probe

Greg,

Are we to understand that you're running two select statements? One that
generates a result set? Another that returns a count of 20-40?

Since the count is so low, it might be quicker to count the rows yourself
during a "fetch" loop as opposed to running "select count(*) ..."



On Wed, Jun 17, 2020 at 12:06 PM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

Sorry... I should have mentioned that I've already been through some of
these explanations.

It's returning a count of 20-40 (that's how many rows meet the
criteria)... The Select is using
OPTIMIZE FOR 250 ROWS (as suggested by the IBM DB group)

The SQL statements are executing within a CGI application... so I can
only
really control the source (view) and the indexes.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.