SQL trigger issue on Iseries (I5)

Jag Raghu

SQL trigger issue on Iseries (I5)
Hi
We get the following msg.
" Commitment control not valid for open of member FILExxx"
when we applied the code stated below on AS/400 (ISERIES-V5R1), even though
we said commit= NONE, the trigger appears to apply commitment control.

CREATE TRIGGER DEVMODSF/SLP05_SQLTRG3
AFTER UPDATE OF
CNAM05,CAD105,CAD205,CAD305,CAD405,CAD505,PCD105,PCD205,
CGP405
ON DEVFILES/SLP05
REFERENCING NEW AS NEWSLP05
FOR EACH ROW MODE DB2ROW
SET OPTION COMMIT = *NONE
BEGIN
INSERT INTO DEVMODSF/LSCUSTCHG
(CONO,CUST,SEQ,RECSTS,SNTSTS,FNAME,CHGDATE,CHGTIME)
VALUES (NEWSLP05.CONO05,NEWSLP05.CUSN05,NEWSLP05.DSEQ05,
'U','N','SLP05',CURRENT DATE,CURRENT TIME);
END


Any help is highly appreciated.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ed Mullikin

Re: SQL trigger issue on Iseries (I5)
(in response to Dieter Schwarz)
I'll be out of the office from 12/5 thru 12/9. For any database issues, please contact Mai Anh at ex.4814.

Thanks, ED

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dieter Schwarz

Re: SQL trigger issue on Iseries (I5)
(in response to Jag Raghu)
a) If commitment-control is enabled for the PF, you cannot avoid it!
b) ... unless you extend your insert "insert .... with nc"
HTH

Dieter

----- Original Message -----
From: "Jag Raghu" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, December 01, 2005 10:53 PM
Subject: [DB2-L] SQL trigger issue on Iseries (I5)


> Hi
> We get the following msg.
> " Commitment control not valid for open of member FILExxx"
> when we applied the code stated below on AS/400 (ISERIES-V5R1), even
though
> we said commit= NONE, the trigger appears to apply commitment control.
>
> CREATE TRIGGER DEVMODSF/SLP05_SQLTRG3
> AFTER UPDATE OF
> CNAM05,CAD105,CAD205,CAD305,CAD405,CAD505,PCD105,PCD205,
> CGP405
> ON DEVFILES/SLP05
> REFERENCING NEW AS NEWSLP05
> FOR EACH ROW MODE DB2ROW
> SET OPTION COMMIT = *NONE
> BEGIN
> INSERT INTO DEVMODSF/LSCUSTCHG
> (CONO,CUST,SEQ,RECSTS,SNTSTS,FNAME,CHGDATE,CHGTIME)
> VALUES (NEWSLP05.CONO05,NEWSLP05.CUSN05,NEWSLP05.DSEQ05,
> 'U','N','SLP05',CURRENT DATE,CURRENT TIME);
> END
>
>
> Any help is highly appreciated.
>
> --------------------------------------------------------------------------
-------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm