Re: GET DIAGNOSTICS question

If my first FETCH returns 02000 (meaning there are no eligible orders), I'm
going to close the cursor, sleep, and restart. I don't need an exact
number, just zero (sleep) or greater than zero (process all). I don't need
to use SQL when a few line of code will solve the problem.

When I look at all the improvements and enhancements (conditional
selection, conditional ordering, reduced access path maintenance thanks to
the Index Advisor, much easier counting and grouping, stored
procedures) I've made using SQL, this one edge case--not getting the
expected results from DB2_NUMBER_ROWS--is insignificant. But the question
of what DB2_NUMBER_ROWS does is going to bug me!

On Thu, May 11, 2023 at 6:03 AM Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:

I always feel that the other problem with using count is that there is no
guarantee that the table has not changed between the time of obtaining the
count and the data retrieval. Yes - I know there are ways of avoiding this
but most folks don't bother and/or it would cause other issues.

As long as the result set fits, these days I like retrieving directly into
an automatically sized array and using %Elem to tell me how many rows I
got. Or, if I only need the count to iterate over the result set, to simply
use For-Each.


Jon P.

On May 10, 2023, at 6:40 PM, Alan Cassidy <cfuture@xxxxxxxxxxx> wrote:

Thanks Buck! I've been using the DIAGNOSTIC values, but that seems a

more sure thing.

But wait, you need a SELECT to do the count, and you can't do it when

you open a cursor. I don't think you would run the cursor and then do this
count with the same SELECT statement, so how is this?

The SQL cursors I use take a long time. It's probably quicker for the

second SELECT, but I don't think that much.

--alan

On 5/10/2023 9:30 AM, Buck Calabro wrote:

On Wed, 10 May 2023 at 04:38, Reeve <rfritchman@xxxxxxxxx> wrote:

How do I determine the number of records in the cursor I've just

opened?

GET DIAGNOSTICS :x = ROW_COUNT returns the number of rows

processed--not

what I need here. GET DIAGNOSTICS :x = DB2_NUMBER_ROWS seems to be the
right call but it gives me a nonsense (I think) number (14932) when

there

are zero rows. If I have zero rows available, I close the cursor,

sleep,

wake up, open the cursor again, and check for available rows.

If I want a count, I do SELECT COUNT(*) INTO...
To me, the diagnostic counts always seemed more like approximations
rather than actionable data.
--buck

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.