Re: Transaction runs under commit while it should not

Hi,

I have a commitment control issue which we can't resolve and in my
opinion, it should not work in that way.
Even if there is some wrong logic in below code, I can't understand why
some transaction runs under commit while they should not.
The problem here is that update on TESTTABLE run under commit but actual
trigger has commit=*none. Next time it run's properly without commit and in
another loop it run under commit again(in TESTPGM I do updates in loop and
it looks every run it behave differently).
The weirdest thing is that it works just fine if I'll simply remove exec
sql select... from program TESTPGM
After I've added exec sql select... to pgm TESTPGM, weird things start to
happen with some transaction's run under commit why they should not because
of triggers compiled with commit=*none
I can't understand why this one simple sql select break the whole setup.
Everything works just fine without embeded sql used in program.

DROP TABLE TESTLIB.TESTTABLE;

CREATE TABLE TESTLIB.TESTTABLE (
ID INTEGER GENERATED ALWAYS AS IDENTITY,
CODE INTEGER NOT NULL,
DESC CHAR(10) NOT NULL)

INSERT
INTO TESTLIB.TESTTABLE (CODE, DESC)
VALUES (1, ''), (2, '');

CREATE TABLE TESTLIB.TESTMQT ( TESTCODE, TESTDESC ) AS
(SELECT DECIMAL(CODE, 5), DESC
FROM TESTLIB.TESTTABLE)
DATA INITIALLY IMMEDIATE
REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION
MAINTAINED BY USER
RCDFMT TESTMQT ;

CREATE UNIQUE INDEX TESTLIB.TESTIDX
ON TESTLIB.TESTMQT (TESTCODE);

CREATE OR REPLACE TRIGGER TESTLIB.TESTTRGUP
NO CASCADE BEFORE Update On TESTLIB.TESTMQT
REFERENCING OLD As vOld
NEW As vNew
For Each Row Mode DB2ROW
SET OPTION COMMIT=*NONE
BEGIN ATOMIC
UPDATE TESTLIB.TESTTABLE
SET
CODE = vNew.TESTCODE,
DESC = vNew.TESTDESC
WHERE
CODE = vOld.TESTCODE;
END;

CREATE TABLE TESTLIB.TESTPF ( TESTCOL CHAR(10) NOT NULL)
RCDFMT TESTRF;

CREATE TABLE TESTLIB.TESTTBL(
ID INTEGER GENERATED ALWAYS AS IDENTITY,
COLUMN CHAR(10) NOT NULL)

CREATE OR REPLACE TRIGGER TESTLIB.TESTPFTRGUP
NO CASCADE BEFORE Update On TESTLIB.TESTPF
REFERENCING OLD As vOld
NEW As vNew
For Each Row Mode DB2ROW
SET OPTION COMMIT=*NONE
BEGIN ATOMIC
UPDATE TESTLIB.TESTTBL
SET
COLUMN = vNew.TESTCOL
WHERE
COLUMN = vOld.TESTCOL;
END;

INSERT INTO TESTLIB.TESTPF VALUES '';



TESTCL.CLLE

PGM
STRCMTCTL LCKLVL(*CHG) CMTSCOPE(*JOB)
CALL TESTPGM
COMMIT
ENDCMTCTL
ENDPGM


TESTPGM.RPGLE

**FREE
Ctl-Opt DATEDIT(*DMY/) Option(*nodebugio:*srcstmt);

Dcl-F TESTPF Usage(*Update:*Delete:*Output) Commit;
Dcl-F TESTIDX Usage(*Update:*Delete:*Output) Keyed ;
Dcl-S Index Packed(4) Inz(0);
dcl-s id int(5);

exec sql set option commit=*none;
exec sql select id into :id from testtable limit 1;

Dow Index < 100;
Setll(e) *start TESTPF;
Read(e) TESTPF;
TESTCOL = %Char(Index+1);
Update(e) TESTRF;

Chain(e) (2) TESTIDX;
TESTDESC = %Char(Index);
Update(e) TESTMQT;

Chain(e) (1) TESTIDX;
TESTDESC = %Char(Index);
Update(e) TESTMQT;

Setll(e) *start TESTPF;
Read(e) TESTPF;
TESTCOL = %Char(Index);
Update(e) TESTRF;

Index+=1;
EndDo;

*INLR = *on;
Filip Drzewiecki
RPG Developer

e-mail: fdrzewiecki@xxxxxxxxxxxxxxxxx<mailto:fdrzewiecki@xxxxxxxxxxxxxxxxx

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com