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.