Re: GET DIAGNOSTICS question

I guess I've discovered a new type of cursor--the "antisocial" cursor. My
logical shows three rows, my SQL run interactively shows three rows, and
DB2_NUMBER_ROWS shows 12,017. ROW_COUNT shows 0, as I'd expect.

My concern with COUNT(*) is a (possibly erroneous) belief that it's not
efficient. Typically, my to-be-processed set of records is less than 1,000
at any time, so perhaps I'm worrying about something not worth the worry.
And COUNT(*) may be the prescribed method to get an exact record count. If
that's the case, what does DB2_NUMBER_ROWS do?

I hoped SQL would have a nice crisp solution so people looking at this code
in the future would immediately understand what the program is doing, with
the "why" materializing as comments. But the workaround will take 8 LOC:
two comments and two IF statements--not elegant but functional and
comprehensible.

Thank you all!

\-rf

On Wed, May 10, 2023 at 4:17 PM Buck Calabro <kc2hiz@xxxxxxxxx> wrote:

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

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

Be sure to read Charles' reply - he was much clearer than I regarding
DIAGNOSTIC values.

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?

When I need a count before starting a processing loop, I run the
SELECT COUNT(*) INTO...first - no cursor.
Yes, this means two separate exec sql blocks.
But I very rarely need to know how many records there are _before_ I
start processing them. (Reeve apparently does, thus my reply).
It's more typical for me to simply process them until I get to the end
of the subfile or end of the result set. If I need a count of those I
FETCHed or UPDATEd, I use SQLER3. That's especially useful if I'm
FETCHing FOR n ROWS.

The SQL cursors I use take a long time. It's probably quicker for the
second SELECT, but I don't think that much.

The optimiser can be a pleasant surprise that way. SELECT COUNT(*)
always seems to run faster than selecting a bunch of columns over a
bunch of rows.
In general, if I have slow performing cursors, I look at creating some
new indexes as a first step.
--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.