Re: Journaling

Good morning,

We don't have real SQL on our system. We have PDSQL, from Gumbo I think.

Thanx anyway.



Thanx,

Nick



Nick Radich
Sr. Programmer/Analyst
EPC Molding, Inc.
Direct (320) 679-6683
Toll free (800) 388-2155 ext. 6683
Fax (320) 679-4516
nick_radich@xxxxxxxxxxxxxx



"SJL" <sjl_abc@xxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
02/27/2008 09:41 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

Subject
Re: Journaling






Nick -

You can create column-level triggers with SQL, but I'm not sure that this
technique is supported on V4R5.
The example below was created on a V5R2 system.

Regards,
sjl

---------------------------------------------

-- ( Use RUNSQLSTM to run this...scan and replace YOURLIB with a valid
library name):
-- Review the COUNTERS table after running this to see the results...

Drop Trigger taxidtrg;
Drop Trigger taxidtrg2;
Drop Trigger ssntrg;
Drop Trigger ssntrg2;

Drop Table YOURLIB/address;
Drop Table YOURLIB/counters;

CREATE TABLE YOURLIB/ADDRESS
(
ABNO NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, TAXID CHAR (20 ) NOT NULL WITH DEFAULT
, SSN CHAR (10 ) NOT NULL WITH DEFAULT
, NAME CHAR (30 ) NOT NULL WITH DEFAULT
) ;

CREATE TABLE YOURLIB/COUNTERS
(
KEYFLD NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, ADDTAXID NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, CHGTAXID NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, DELTAXID NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, ADDSSN NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, CHGSSN NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, DELSSN NUMERIC (8 , 0) NOT NULL WITH DEFAULT
) ;


CREATE TRIGGER TAXIDTRG
AFTER UPDATE OF TAXID ON YOURLIB/ADDRESS
REFERENCING OLD AS OL
NEW AS NW
FOR EACH ROW MODE DB2SQL
WHEN ( OL.TAXID <> NW.TAXID and NW.TAXID <> ' ')
BEGIN ATOMIC
UPDATE YOURLIB/COUNTERS
SET CHGTAXID = CHGTAXID + 1
WHERE KEYFLD = 1;
END;


CREATE TRIGGER TAXIDTRG2
AFTER INSERT on YOURLIB/ADDRESS
referencing new as NW
FOR EACH ROW MODE DB2SQL
WHEN ( NW.TAXID <> ' ')
BEGIN ATOMIC
UPDATE YOURLIB/COUNTERS
SET ADDTAXID = ADDTAXID + 1
WHERE KEYFLD = 1;
END;


CREATE TRIGGER SSNTRG
AFTER UPDATE OF SSN ON YOURLIB/ADDRESS
REFERENCING OLD AS OL
NEW AS NW
FOR EACH ROW MODE DB2SQL
WHEN ( OL.SSN <> NW.SSN and NW.SSN <> ' ')
BEGIN ATOMIC
UPDATE YOURLIB/COUNTERS
SET CHGSSN = CHGSSN + 1
WHERE KEYFLD = 1;
END;


CREATE TRIGGER SSNTRG2
AFTER INSERT ON YOURLIB/ADDRESS
REFERENCING NEW AS NW
FOR EACH ROW MODE DB2SQL
WHEN (NW.SSN <> ' ')
BEGIN ATOMIC
UPDATE YOURLIB/COUNTERS
SET ADDSSN = ADDSSN + 1
WHERE KEYFLD = 1;
END

INSERT INTO YOURLIB/COUNTERS
VALUES(1,0,0,0,0,0,0);

INSERT INTO YOURLIB/ADDRESS
VALUES(1,'XXXXXXXXXX','1234567890','NAME1');

INSERT INTO YOURLIB/ADDRESS
VALUES(2,'YYYYYYYYYY','0123456789','NAME2');

INSERT INTO YOURLIB/ADDRESS
VALUES(3,'ZZZZZZZZZZ','9876543210','NAME3');

UPDATE YOURLIB/ADDRESS SET NAME = RTRIM(NAME) CONCAT 'A'
WHERE ABNO = 1;

UPDATE YOURLIB/ADDRESS SET NAME = RTRIM(NAME) CONCAT 'B'
WHERE ABNO = 2;

UPDATE YOURLIB/ADDRESS SET TAXID = '0000000000'
WHERE ABNO = 1;

UPDATE YOURLIB/ADDRESS SET TAXID = 'AAAAAAAAAA'
WHERE ABNO = 2;

UPDATE YOURLIB/ADDRESS SET SSN = '9999999999'
WHERE ABNO = 1;

UPDATE YOURLIB/ADDRESS SET SSN = 'ZZZZZZZZZZ'
WHERE ABNO = 2;