Re: SQL set options in SQLRPGLE source member (NOMAIN)

On 15-Jan-2015 14:20 -0600, Koester, Michael wrote:

After a good long time of probably not doing things right, I'm trying
to improve my processes. I am putting a bunch of procedures into a
single NOMAIN source member. Some (most?) of these procedures have
embedded SQL, so I know that my standard "exec sql set options(...)"
statement needs to be the first calculation of the source. Most all
of the procedures are EXPORTs, but the whole set gets compiled into a
single module used in a service program, where the procedures are
executed by a *pgm that does not have embedded SQL.

Is it okay to put my "exec sql set options(...)" in just the first
procedure (ProcedureA) in the source member? Does a program that
executes ProcedureB (but not ProcedureA) get those standard options?
Or does the SQL precompiler somehow make that "set options"
instruction available to all the procedures in the module? If so,
would it be just as good to code it outside (as in, before the
DCL-PI) of the first procedure?.

<http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzsoption.htm>
IBM i 7.2->Database->Reference->SQL reference->Statements->SET OPTION
_SET OPTION_
"...
_Invocation_

This statement can be used in a REXX procedure or embedded in an application program. If used in a REXX procedure, it is an executable statement. If embedded in an application program, it is not executable and must precede any other SQL statements. ...
..."

Expectations as I understood, was that: Appearing sequentially within the source, as the _first_ SQL statement coded, the _first_ SQL "SET OPTION statement establishes the processing options to be used for SQL statements"; the SQL options will be established for all SQL statements within the module in the described scenario.

In my experience as just tested on v5r3 using ILE RPG with "/free" or not, appearing sequentially within the source, irrespective placement of any other coded SQL statements, the _last_ SET OPTION statement establishes the processing options to be used for the SQL statements throughout a program. Having coded an SQL statement other than SET OPTION prior in the source has no impact; the same effect, regardless. Quite conspicuously, that is contrary to the documentation's "must precede" comment. I had never coded except one SET OPTION and always that one statement was the first SQL statement in my source, so I had never before noticed any contradiction to the docs; not sure what newer\later releases would do.

Or, do I need to locate it where each procedure must execute it (like
maybe coded just once, but as a private procedure that must be called
by each SQL procedure...) ?

As non-executable, invoking such a procedure would have no effect for the OPTIONS which are established just once; applicable to the entirety of the *MODULE being created. The effect is achieved only if the statement is sequentially the first, according to the docs, and that statement could be coded in a procedure to meet that [apparently bogus and inaccurately documented at v5r3 anyhow] requirement.