Does server job auto start commitment control?

[Steve Richter]

I have PHP code that calls an SQL procedure which is compiled with COMMIT(*CHG). I am getting a CPI8350 message in the joblog which I think has to do with ENDCMTCTL being run when there are COMMITs pending.

Does the system automatically STRCMTCTL for server jobs that run SQL procedures compiled with COMMIT(*CHG) ? How to change the scope of the STRCMTCTL to *JOB instead of *ACTGRP?

thanks,

[CRPence]

On 10-Jul-2016 23:32 -0500, Steve Richter wrote:
> I have PHP code that calls an SQL procedure which is compiled with
> COMMIT(*CHG). I am getting a CPI8350 message in the joblog which I
> think has to do with ENDCMTCTL being run when there are COMMITs
> pending.

For lack of the spooled joblog with second level details, difficult
to know, of what the message informs; messages often offer significant
context that is simply unavailable with just the MsgId, but context that
is revealed in a spooled joblog taken with LOG(4 0 *SECLVL). The most
likely reason for the message, I suppose, per an "SQL procedure" being
ILE, is per the Rc03 suggesting "03 -- The activation group level
commitment definition is being ended normally with pending changes. An
implicit commit is performed." That implies the application had failed
to issue the expected COMMIT [or ROLLBACK], but per /normal/ exit from
the activation, the feature had automatically committed the pending
changes /assuming/ that was a desirable effect -- thus the information
message to log that assumption had been made.

IBM i 7.1->Programming->Programming languages->ILE Concepts->Data
Management Scoping->Commitment Control Scoping
[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/ilec/sc415606199.htm]

>
> Does the system automatically STRCMTCTL for server jobs that run SQL
> procedures compiled with COMMIT(*CHG) ?

IBM i 7.1->Database->Commitment control->Commitment control
concepts->Commitment definition->Scope for a commitment definition
[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzakj/rzakjscope.htm]
"… implicitly by the system when an SQL application runs with an
isolation level other than *NONE."

> How to change the scope of the STRCMTCTL to *JOB instead of *ACTGRP?

Explicitly specify the special value *JOB [vs leaving the default
*ACTGRP] for the Commitment Definition Scope (CMTSCOPE) on a\the Start
Commitment Control (STRCMTCTL) request used to start a job-scoped
isolation. Or an Old Program Model (OPM) [and IIRC a Default Activation
Group (DFTACTGRP) Integrated Language Environment (ILE)] program AFaIK
will implicitly start using job-scoped because there is actually [or
effectively] no "activation group associated with the program issuing
the [STRCMTCTL] command."

IBM i 7.1->Programming->Programming languages->ILE Concepts->Data
Management Scoping->Commitment Control Scoping->Commitment Definitions
and Activation Groups
[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/ilec/sc415606200.htm]


--
Regards, Chuck

[Steve Richter]

On Monday, July 11, 2016 at 12:32:21 AM UTC-4, Steve Richter wrote:

> Explicitly specify the special value *JOB [vs leaving the default
> *ACTGRP] for the Commitment Definition Scope (CMTSCOPE) on a\the Start
> Commitment Control (STRCMTCTL) request used to start a job-scoped
> isolation.

but I am not running a STRCMTCTL command. The PHP web page is using ODBC to call the stored procedure. The stored procedure is written in SQL.

the procedure runs on the IBM i in job QSQSRVR. The stored procedure is created using the RUNSQLSTM command with parm COMMIT(*CHG).

here is the info message from the joblog:
From user . . . . . . . . . : QTMHHTTP
Message . . . . : Commit operation performed before ending commitment
control; reason code 03.

Is SQL automatically starting commitment control? When I run the STRCMTCTL command from the procedure I get a CPF8351 error saying commitment control is already started.

set vmsg = 'STRCMTCTL LCKLVL(*CHG) CMTSCOPE(*JOB)' ;
CALL QCMDEXC(VMSG,80.00) ;

Does the QSQSRVR job automatically start commitment control ?

[CRPence]

On 11-Jul-2016 10:15 -0500, Steve Richter wrote:

> On 11-Jul-2016 08:11 -0500, CRPence wrote:
>> On 10-Jul-2016 23:32 -0500, Steve Richter wrote:

>>> […] How to change the scope of the STRCMTCTL to *JOB instead of
>>> *ACTGRP? […]
>>
>> […] Explicitly specify the special value *JOB [vs leaving the

>> default *ACTGRP] for the Commitment Definition Scope (CMTSCOPE) on
>> a\the Start Commitment Control (STRCMTCTL) request used to start a
>> job-scoped isolation. […]
>>
>
> but I am not running a STRCMTCTL command.

So lacking "the" STRCMTCTL request that already exists for which a
modification would be required for the alternate effect, "a" STRCMTCTL
request could be "used to start a job-scoped isolation".

IBM i 7.1->Database->Commitment control->Commitment control
concepts->Commitment definition->Scope for a commitment definition
[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzakj/rzakjscope.htm]
"…

_Job-level commitment definition_

A commitment definition can be scoped to the job only by issuing
STRCMTCTL CMTSCOPE (*JOB). Any program running in an activation group
that does not have an activation-group-level commitment definition
started uses the job-level commitment definition, if [the job-scoped
CmtDfn] has already been started by another program for the job. …"

The following external stored procedure could be called to effect
that; this is repeated again as recap:

create procedure job_scoped_cmtctl
language sql
set option commit=*none
call qsys2.qcmdexc('STRCMTCTL LCKLVL(*CHG) CMTSCOPE(*JOB)')

>
> The PHP web page is using ODBC to call the stored procedure. The
> stored procedure is written in SQL.
> the procedure runs on the IBM i in job QSQSRVR. The stored procedure
> is created using the RUNSQLSTM command with parm COMMIT(*CHG).

I do not recall the implementations of the various methods to invoke
the DB2 SQL from PHP [and the OP does not show the connection details
and actual invocations], but a purely\true client ODBC interface should
enable starting a job-scoped commit definition, after the connection has
been established, given the connection was requested with no isolation
nor auto-commit; or I expect instead, implicitly, that a job-scoped
commit definition would reflect what isolation had been requested for
the connection -- I can not test. As the prior reply suggested, prior
to any commitment control starting implicitly for any activations, the
job-scoped commit definition can be created with the aforementioned
STRCMTCTL CMTSCOPE(*JOB)

>
> here is the info message from the joblog:
> From user . . . . . . . . . : QTMHHTTP
> Message . . . . : Commit operation performed before ending
> commitment control; reason code 03.

FWiW, that is still not a spooled joblog with second-level text, and
thus still missing much context [now also missing the msgid]. Combined
with the OP however, understood is that the effect is indeed the msg
CPI8350 rc03 for which an "implicit COMMIT is performed" per the

"activation group level commitment definition is being ended normally

with pending changes." Yet conspicuously absent, per that missing
context, is the /name/ of the Commitment Definition. I suspect the
value is *DFTACTGRP, so the effect is likely very similar [very little
different than] to what would be experienced, had the commit definition
been job-scoped.

>
> Is SQL automatically starting commitment control?

Yes, "implicitly by the system when an SQL application runs with an
isolation level other than *NONE", scoped to the activation group for
the ILE procedure being run; as described by the prior reply and the
docs. AFaIK that instead could be a job-scoped commitment control
having been established, if started according to the setting specified
for the /default transaction isolation level/ for an ODBC connection.
If the job-scoped CmtDfn exists, I expect [as verified outside PHP\ODBC]
that the SQL application will run in the job-scoped CmtDfn with the same
isolation-level -- as the doc links suggest.

> When I run the STRCMTCTL command from the procedure I get a CPF8351
> error saying commitment control is already started.
>
> set vmsg = 'STRCMTCTL LCKLVL(*CHG) CMTSCOPE(*JOB)' ;
> CALL QCMDEXC(VMSG,80.00) ;

That would be expected, because _that SQL procedure_ [per noted as
having been created implicitly as SET OPTION COMMIT=*CHG] has already
effected the implicit commitment control scoped to the activation group;
presumed earlier, scoped to *DFTACTGRP. The CL request would have to
have been issued outside of and before running that procedure in order
to avoid that error; that procedure implicitly would have issued upon
activation, effectively, STRCMTCTL LCKLVL(*CHG) CMTSCOPE(*ACTGRP).

>
> Does the QSQSRVR job automatically start commitment control ?
>

Again, I expect a commit definition would be established, according
to the [ODBC] connection commit\isolation specifications. But if there
is no true autocommit and isolation level is *NONE, then there should be
no commitment control started for the server job\process, initially.
Yet even without a job-scoped commit definition, activation-scoped
commitment control would be implicitly started for any invoked SQL
application(s) that are defined to run with isolation other than *NONE;
after which, a cmtctl level scoped to the job is not possible, just as
the CPF8351 seems to have diagnosed for the above failing scenario, at
least when the prior scoping was for the *DFTACTGRP.

Thus the following SQL procedure, if called before any other, I
expect should be able to establish a job-scoped commit-definition, given
the [ODBC] connection did not already start with isolation; NB, the SQL
procedure explicitly establishes a commit-level of *NONE, using the SET
OPTION declarative statement, so as not to be influenced by the COMMIT()
specification under which the routine is being compiled:

create procedure job_scoped_cmtctl
language sql
set option commit=*none
call qsys2.qcmdexc('STRCMTCTL LCKLVL(*CHG) CMTSCOPE(*JOB)')

--
Regards, Chuck