Triggers - DB2 for z/OS vs LUW

John Piccoli

Triggers - DB2 for z/OS vs LUW
Hello,



We have a trigger(identified below) that currently runs in DB2 for LUW(note
that it contains a declared variable and nested IF statements). Is there a
method of re-writing it with DB2 for z/OS supported constructs?





CREATE TRIGGER ISIN.ITEM_CMT_LOG AFTER UPDATE ON ISIN.ITEM

REFERENCING OLD AS o NEW AS n FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

DECLARE logTxt VARCHAR(16000) ;

SET logTxt = '';



IF N.ITEM_STATUS <> 'SIDR' THEN



IF COALESCE(o.ITEM_STATUS,'') <> COALESCE(N.ITEM_STATUS,'') THEN

SET logTxt = logTxt || CHR(13) || 'Item status changed from ''' ||

COALESCE(LTRIM(RTRIM(o.ITEM_STATUS)),'') || ''' to ''' ||

COALESCE(LTRIM(RTRIM(n.ITEM_STATUS)),'') || '''';

END IF;



IF COALESCE(o.ISSR_SHORT_NM,'') <> COALESCE(n.ISSR_SHORT_NM,'') THEN

SET logTxt = logTxt || CHR(13) || 'Issuer short name changed from ''' ||

COALESCE(LTRIM(RTRIM(o.ISSR_SHORT_NM)),'') || ''' to ''' ||

COALESCE(LTRIM(RTRIM(n.ISSR_SHORT_NM)),'') || '''';

END IF;



....





IF logTxt <> '' THEN

INSERT INTO ISIN.CMT_LOG


VALUES(n.REQUEST_ID,n.ITEM_ID,'LTLG',SUBSTR(RTRIM(LEFT(logTxt,3000)),2),

n.LAST_CHG_USER_ID,n.LAST_CHG_IP,n.TM_STAMP);

END IF;



END IF;



END^





John



________________________________________


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Vanroose

Re: Triggers - DB2 for z/OS vs LUW
(in response to John Piccoli)
Maybe something in the style of:

CREATE TRIGGER ISIN.ITEM_CMT_LOG AFTER UPDATE ON ISIN.ITEM
REFERENCING OLD AS o NEW AS n FOR EACH ROW MODE DB2SQL
WHEN (N.ITEM_STATUS <> 'SIDR')
INSERT INTO ISIN.CMT_LOG
VALUES(n.REQUEST_ID,n.ITEM_ID,'LTLG',
SUBSTR(RTRIM(LEFT(
CASE WHEN o.ITEM_STATUS is different from n.ITEM_STATUS)
THEN CHR(13) || 'Item status changed from ''' ||
COALESCE(LTRIM(RTRIM(o.ITEM_STATUS)),'') || ''' to ''' ||
COALESCE(LTRIM(RTRIM(n.ITEM_STATUS)),'') || ''''
ELSE ''
END ||
CASE WHEN ISSR_SHORT_NM is different from n.ISSR_SHORT_NM
THEN CHR(13) || 'Issuer short name changed from ''' ||
COALESCE(LTRIM(RTRIM(o.ISSR_SHORT_NM)),'') || ''' to ''' ||
COALESCE(LTRIM(RTRIM(n.ISSR_SHORT_NM)),'') || ''''
END || ....
,3000)),2),n.LAST_CHG_USER_ID,n.LAST_CHG_IP,n.TM_STAMP)



-- Peter Vanroose
ABIS Training & Consulting.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms