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,

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

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 ?

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