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