Re: SQL options precedence for SQLRPGLE

It appears to me that the manuals are not very clear on this subject,
because when they refer to 'the job' it can mean either the job where the
program is compiled or the job where the compiled program is run. Take for
instance the reference for CRTSQLRPG (<
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzsoption.htm

). For parameter SRCFILE it says for value *LIBL: "All libraries in the

job's library list are searched until the first match is found". This
obviously refers to the job where the program is compiled. But for
parameter CLOSQLCSR, value *ENDJOB it refers to the job where the program
is run: "SQL cursors are closed, SQL prepared statements are discarded,
and LOCK TABLE locks are released when the job ends". It is left to the
reader to determine which job is meant; in these two cases the context
leaves no doubt. But for other settings it gets harder.

In the case of SRTSEQ there are two special values *JOB and *JOBRUN. To my
mind this implies that *JOBRUN refers to the job where the program is run
and thus that *JOB refers to the job where the program is compiled.
Whoever wrote the documentation that Buck pointed to apparently failed to
note the difference, because *JOB and *JOBRUN are described as being
exactly the same. In the documentation for CRTSQLRPG the distinction is
made, however IMHO not very clearly.

Joep Beckeringh


rpg400-l-bounces@xxxxxxxxxxxx wrote on 19-06-2013 21:58:38:

Buck Calabro

On 6/19/2013 3:31 PM, CRPence wrote:

I perceive the resolution being the reverse of that. The SQL OPTION
SRTSEQ is where the resolution starts. The SQL options effect either

an

explicit setting or a setting as a /special value/ that redirects to
effect resolution of the setting from elsewhere, possibly redirecting

to

the SRTSEQ() of the job, and the job also may have redirected to
SRTSEQ() of the user, and the user may have redirected to the system
value QSRTSEQ to establish its setting. Viewed that way,

conspicuously

exposes that the CHGJOB SRTSEQ(different) may have no effect on the

SQL

statements performed within the job. Thus changing the Sort Sequence
for the job when the SQL option SRTSEQ has been explicitly established
for the SQL environment [within a job] will have no effect on those

SQL

requests.

However an unresolved special value with the implied meaning of

"look

elsewhere" could be established in the SQL options. That special

value

resolution can remain confusing, because there are two types of
redirected resolution; i.e. two special values that redirect to the
"job". One special value is *JOB, and the other is *JOBRUN. The *JOB
is resolved at run-time, but is then stored into any compiled objects

as

the resolved value from the job... at that moment; thus if the value

for

the job was SRTSEQ(*HEX), then the created object is tagged with
SRTSEQ(*HEX), having redirected to the job to get that value.

AFaIK only the special value *JOBRUN enables the CHGJOB
SRTSEQ(different) to have any impact on the SQL statements running in
the already established SQL environment [within a job]. When the SQL
OPTION of SRTSEQ is set to *JOBRUN vs *JOB, compiled objects will have
the unresolved *JOBRUN established, thus suggesting that the SQL must
delay resolution of the SRTSEQ value until run-time of the statement.
IIRC the SQL option set to *JOB resolves immediately from the job,
rather than for each statement, and thus any CHGJOB SRTSEQ(different)
request issued since starting that SQL environment [e.g. session or
program] has no effect on the sort sequence used at run-time of the

SQL

statements running within that already-established SQL environment....
So only use of *JOBRUN for the SRTSEQ option will enable the CHGJOB to
have any effect on the sort sequence used at run-time; i.e. each
statement must re-resolve from the job, to determine the current

SRTSEQ

in effect for the job.

I would not have known that by reading the manual, which says (from V5R3
on) '*JOB or *JOBRUN The SRTSEQ value for the job is used.'

http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzsoption.htm

If we assume that the OP has no SET OPTION SRTSEQ ('...*LANGIDSHR is
specified somewhere, but not explicitly by us.') then one may understand
why I suggested the system value as a place to begin understanding how
the current hierarchy may be set up. Of course it might also be an
initial program, unless it's a batch job, or it could be the user
profile. Which default to the system value.

In any event, thanks for this interesting insight into SRTSEQ(*JOBRUN)
vs *JOB. I need to do some testing on 7.1...
--buck