Oppolzer - Informatik / Blog

What happens if the same technique is done without GTTs, that is, the cursor for
the result set relies on "normal" tables?

The client fetches the rows from the result set, and normal behaviour should be,
that the client closes the cursor and commits, so the connection is terminated.

But if it fails to do so, the connection will stay active for some time, until
it is terminated due to some sort of timeout, I guess. Was this the problem that
the OP had?

Are there other solutions to this sort of problem? Is there anything the SP can
do about it?

Does a cursor WITH HOLD make any sense in this szenario? Normally IMO there
should only be ONE commit at the end of the processing, issued by the client,
and the cursor should be read-only.

Kind regards

Bernd



Am 09.02.2015 um 14:37 schrieb R.B.:
>
> I use GTTs defined with ON COMMIT DROP TABLE then use a WITH RETURN definition
> in the CURSOR that is OPENed and never CLOSEd. That way, if the client side "forgets"
> to COMMIT, the host side can simply react to a-601 on the GTT by issuing a COMMIT
> itself and re-driving the create GTT logic. Works a treat and no WITH HOLD
> required or desired!
>
>
>
> R.B.
>
>
>
>
> From: Bernd Oppolzer [mailto:db2-l@lists.idug.org]
> Sent: Monday, February 9, 2015 1:28 PM
> To: db2-l@lists.idug.org
> Subject: [DB2-L] - RE: releasing CURSOR WITH HOLD
>
>
>
> C.,
>
> I don't quite understand your comment.
>
> I know that there are Stored Procs returning result sets,
> and result sets are in fact open cursors, and the caller of the stored proc
> can fetch the results from the result set using special SQL statements
> like ASSOCIATE LOCATOR etc. after the Stored Proc CALL, so the
> connection has to stay active for a longer time than the CALL.
>
> I never tested that, so I don't know too much details, and I don't know
> if it works with cursors WITH HOLD and what other consequences
> this technique has. I thought, it would be valid to point the OP in this
> direction: if the Stored Proc has result sets, the connection has to
> be kept beyond the time of the CALL, because the client works on the
> result sets, and CLOSE CURSOR in the SP does make no sense, IMHO.
>
> Kind regards
>
> Bernd