RE: Loading arrayDS from sql select into statement?

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Tuesday, September 15, 2015 12:19 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Loading arrayDS from sql select into statement?

On 15-Sep-2015 07:08 -0600, Charles Wilt wrote:

On Tue, Sep 15, 2015 at 9:03 AM, Koester, Michael wrote:

I was intrigued by the idea of GET DIAGNOSTICS, but at least for my
current needs, I too tend to favor opening the process with a "select
count(*) into..."
With that, I can wrap the whole "declare cursor", "open cursor",
"fetch into", and "close cursor" in an "if countOfRows > 0"
structure, thereby not executing unnecessary code when there are no
array rows to be loaded.

While this particular need does not require trimming nanoseconds, I
was just looking for "best practices" ideas that would enhance my
coding, now and future.

Just note that if using a SENSITIVE cursor, the number of rows you get
with the SELECT COUNT may not equal the number of rows fetched.

If you specify an INSENSITIVE cursor, then the count should match
since the system copies the data to temp storage. Unless somebody adds
a row between the SELECT COUNT and the OPEN of the cursor.

The default for cursors is ASENSITIVE, which means you could end up
with either SENSITIVE or INSENSITIVE.

Irrespective the sensitivity, that the prior SELECT COUNT(*) is a
*separate query* already allows for I\O [performed since or even while the
count was being obtained] to cause the countOfRows to be incorrect when
referenced for what would be available in the cursor-query; and incorrect
beyond just zero vs non-zero.

If someone is looking for "best practices", I would expect anyone
reviewing that usage would discourage such coding, and that they would
recommend instead, just looping on FETCH until the EOF. It's not like
that early-escape logic asking "if the prior count(*) suggests there will
be an empty result-set", being performed at run-time, will prevent having
to code that FETCH-loop. Seems like little is gained, except the amount
of work being done; i.e. an additional SQL statement along with the
additional CPU and wall-clock time, for almost no gain.

--
Regards, Chuck