SQL can keep cursors open after the program finishes. This reduces the need for re-opening cursors when programs may be called repeatedly in a job.
Two “boundaries” are available for choosing when cursors will be fully closed — when the *MODULE ends and when the Activation Group (AG) ends. The default is to close cursors fully when the AG ends. If a program runs in the *DEFAULT AG or in *CALLER, the AG might never end.
A SQL RPG program might be compiled to run in its own named AG. The AG can be reclaimed (or ended) with the RCLACTGRP command, naming the AG of the program. Or it might be compiled to run in a *NEW AG. That AG will be created and destroyed automatically when the program starts and ends.
Standard format RPG SQL options might look this way to have cursors closed when the *MODULE ends:<pre>
C+ SET OPTION Commit=*NONE ,
C+ Naming=*SYS ,
C+ DynUsrPrf=*Owner ,
C+ CloSqlCsr = *EndMod
Free-format RPG might look this way:<pre>
exec sql set option CloSqlCsr = *EndMod ;</pre>
Those are both taken from test functions that I use for various things.
The location of SET OPTION can be important in earlier releases. It should be in the first /EXEC SQL block encountered in the source by the pre-compiler. I believe that this is no longer required, but I still do it that way anyway. I.e., I haven’t actually tried putting it anywhere but at the top of the C-specs. IMO, it can be more important to see it immediately than to risk missing it.