Re: SQL options precedence for SQLRPGLE

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

On 19 Jun 2013 10:37, Buck Calabro wrote:

On 6/19/2013 1:13 PM, Josh Diggs wrote:

<<SNIP>> The basic business problem is that the sort sequence
*LANGIDSHR is specified somewhere, but not explicitly by us. This
has performance implications of the negative kind, which business
users do not like.

WRKSYSVAL QSRTSEQ is where this starts, and it flows through CHGJOB
so you might be able to test *HEX easily enough.

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