I'm using STRCMTCTL with the default commitment scope (*ACTGRP). All my service programs have an activation group of *CALLER, so they are running in the same activation group. A rollback called on Embedded SQL in the service program works with no worries at all.
As for the SQL stored procedures, the IBM documentation states that this will always be created with an activation group of *CALLER. As far as I know there isn't any way to change the activation group for an SQL stored procedure either. That is what lead me to believe that the stored procedure would simply run in the commitment scope of the calling RPG program.
From my brief bit of testing though it seems that if the SQL Stored Procedure doesn't have the SET OPTIONS COMMIT = *CS command (or anything either than *NONE), the stored procedures SQL will run in the systems QDBCMTDFN commitment definition, as opposed to the activation groups commitment definition... that does seem strange to me. Adding the SET OPTIONS command results in the Stored Procedure running in the activation groups commitment scope as I would have expected...
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Thursday, 6 January 2011 3:31 PM
To: 'RPG programming on the IBM i / System i'
Subject: AW: Commitment Control with SQL...
Hi,
what is the commitment scope when starting commitment control (executing
STRCMTCTL).
The default value for commitment scope is *ACTGRP (Activation group).
When using *ACTGRP please check if the RPG service program and the SQL
procedures run within the same activation group otherwise the transaction
(Commit and rollback) is restricted to the activation group.
It seems your RPG program and stored procedure run in different activation
groups and commitment scope is *ACTGRP.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Steven Harrison
Gesendet: Thursday, 06. January 2011 07:10
An: RPG programming on the IBM i / System i
Betreff: RE: Commitment Control with SQL...
The SQLRPGLE modules are compiled with a isolation level of *CS. This
shouldn't matter though as this is used when commitment control is
activated implicitly by SQL... The prior STRCMTCTL command overrides
this. If the program was trying to open a file without the COMMIT
keyword while commitment control was active it would just crash.
Commitment control is active... it is just not starting a commitment
cycle...
I tried the following which is leading me to believe that the SQL in the
stored procedures are not being included in the commitment transaction:
STRCMTCTL (Via QCMDEXC)
Exec SQL
Call AddProduct(:productId, :productName, :unitCost);
Exec SQL
UPDATE Products SET UnitCost = 10.00
WHERE ProductId = :productId;
RolBk;
I would assume that the rollback would rollback both the UPDATE and the
INSERT (which is called in the AddProduct stored procedure)
Instead the UPDATE is rolled back but the INSERT from the stored
procedure is still stored in the DB? There is no transaction SQL code in
the stored procedure so my understanding was that it would fall in the
commitment scope of the calling program. This doesn't seem to be
happening though?
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Thursday, 6 January 2011 1:32 PM
To: RPG programming on the IBM i / System i
Subject: Re: Commitment Control with SQL...
You have to specify that the files are under commitment control. For
SQL, it's either in the compile command or in the SET OPTIONS command in
the program itself. For traditional I/O, it's the COMMIT keyword on the
file specification.
Joe
Hi All,
I am using SQL stored procedures to perform the CRUD operations on my
database tables. These are called from an RPG service program. There
are
also still legacy files involved so I start commitment control using
the
STRCMTLCTL command in the main program before any files are opened or
SQL statements are performed. The SQL tables do not seem to be opening
under commitment control though? Changes made to the SQL database
tables
are permanently stored regardless of whether a commit or rollback is
called. And during execution if I view the resources under commitment
control in the activation group the SQL tables do not appear? Does
anyone know if I'm missing something I should be aware of? I was under
the impression that if I started commitment control manually before
any
SQL statements were performed then all transactions in the activation
group, regardless of whether native IO or SQL should run under the
same
commitment definition?
Cheers,
Steve
midrange.com
