Re: Activation Group design question

On 03-Feb-2014 13:21 -0800, DeLong, Eric wrote:

<<SNIP>> Now, as to why your data is not written to disk as you
want, you should explore the SQL options for closing the SQL cursor.
You might need to use CLOSQLCSR(*ENDMOD)

<snip>
CLOSQLCSR
Specifies when SQL cursors are implicitly closed, <<SNIP>>

The Close SQL Cursors option should have nothing to do with the visibility of the data to another [program of an] application. The effect of that OPTION is all about performance and locks, not data. The isolation [commitment control] level would have an effect on the visibility of the data, but that effect is separate from the cursors being closed; i.e. data pending a ROLLBACK or a COMMIT is what affects the visibility of that data elsewhere, and just like a file opened outside of the SQL can be closed in a program while those data\record changes are still pending under isolation, so too an SQL cursor can be closed while those data\row changes are still pending.

Just like leaving a non-SQL ODP per RETURN without *INLR=*ON can be beneficial for performance, leaving the SQL ODP active [as pseudo-closed cursor] can also have performance benefits. Thus attempts to ensure the cursor is closed more often, increases the cost of the SQL for its open activity; also a potential cost for other statement\query activation work, beyond just the creation of the ODP. A similarly negative impact for the RLA, would occur when using the Close File (CLOF) to close an Open Query File (OPNQRYF) ODP and then re-invoking the same OPNQRYF request... instead of just using Position Database File (POSDBF) [or similarly, by another means] and re-reading the data from the existing ODP; that is way over-simplified, but somewhat reflective, for a stupidly-simple scenario.