Re: SQL options precedence for SQLRPGLE

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.