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

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Friday, January 16, 2015 4:27 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: 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.

--
Regards, Chuck