Technical Blog Post
Abstract
How to revalidate Inoperative Triggers in DB2
Body
When a table is dropped and recreated the triggers are marked inoperative, when you do an insert operation afterwards or call SYSPROC.ADMIN_REVALIDATE_DB_OBJECT the trigger remains inoperative, invalid triggers get revalidated with these operations but inoperative triggers remain inoperative until you recreate the trigger.
CREATE TRIGGER statement
http://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000931.html
Modifying and dropping triggers
http://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/t0020253.html
ADMIN_REVALIDATE_DB_OBJECTS procedure - Revalidate invalid database objects
http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0053626.html
I just wanted to share a simple test which I have done recently.
$ db2level
DB21085I This instance or install (instance name, where applicable: "mkassey")
uses "64" bits and DB2 code release "SQL11010" with level identifier
"0201010F".
Informational tokens are "DB2 v11.1.0.0", "s1606081100", "DYN1606081100AIX",
and Fix Pack "0".
Product is installed at "/view/db2_v111_aix64_s1606081100/vbs/INST".
Auto-Revalidation (AUTO_REVAL) = DEFERRED
I had to drop and recreate the trigger, which then got revalidated, then upon executing INSERT2.sql, row 3 got added.
$ db2level
DB21085I This instance or install (instance name, where applicable: "mkassey")
uses "64" bits and DB2 code release "SQL11010" with level identifier
"0201010F".
Informational tokens are "DB2 v11.1.0.0", "s1606081100", "DYN1606081100AIX",
and Fix Pack "0".
Product is installed at "/view/db2_v111_aix64_s1606081100/vbs/INST".
Auto-Revalidation (AUTO_REVAL) = DEFERRED
$db2 -tvf DDL1.sql
CREATE TABLE DB2ADMIN.EMPLOYEE_2 (
EMPNO CHARACTER(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
SALARY DECIMAL(9, 2),
C1 INTEGER
)
ORGANIZE BY ROW;
ALTER TABLE DB2ADMIN.EMPLOYEE_2
DATA CAPTURE NONE
PCTFREE 0
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
CREATE TABLE DB2ADMIN.EMPLOYEE_2_TRIGGER_LOG (
EMPNO CHARACTER(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL
)
IN USERSPACE1
ORGANIZE BY ROW;
SET CURRENT SCHEMA = ADMINISTRATOR;
SET CURRENT PATH = SYSIBM,SYSFUN,SYSPROC,SYSIBMADM,ADMINISTRATOR;
CREATE OR REPLACE TRIGGER DB2ADMIN.EMPLOYEE_2_TRIG1 NO CASCADE BEFORE INSERT ON DB2ADMIN.EMPLOYEE_2 REFERENCING NEW AS NEWR FOR EACH ROW BEGIN ATOMIC CALL DBMS_OUTPUT.PUT_LINE('Trig1 Inserting employee ' || NEWR.EMPNO); CALL DBMS_OUTPUT.PUT_LINE('Trig1 ..New salary: ' || NEWR.SALARY); END
CREATE OR REPLACE TRIGGER DB2ADMIN.EMPLOYEE_2_TRIG2 AFTER INSERT ON DB2ADMIN.EMPLOYEE_2 REFERENCING NEW AS NEWR FOR EACH ROW BEGIN ATOMIC INSERT INTO DB2ADMIN.EMPLOYEE_2_TRIGGER_LOG VALUES (NEWR.EMPNO, NEWR.FIRSTNME); END
$ db2 -tvf INSERT1.sql
INSERT INTO EMPLOYEE_2 (EMPNO, FIRSTNME, SALARY, C1) VALUES ('1', 'F1', 1, 1)
DB20000I The SQL command completed successfully.
INSERT INTO EMPLOYEE_2 (EMPNO, FIRSTNME, SALARY, C1) VALUES ('2', 'F2', 2, 2)
DB20000I The SQL command completed successfully.
SELECT * FROM db2admin.EMPLOYEE_2_TRIGGER_LOG
EMPNO FIRSTNME
------ ------------
1 F1
2 F2
2 record(s) selected.
$ db2 -tvf ALTER.sql
-- DROP AND RECREATE TABLE
DROP TABLE DB2ADMIN.EMPLOYEE_2;
CREATE TABLE DB2ADMIN.EMPLOYEE_2 (
EMPNO CHARACTER(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
SALARY DECIMAL(9, 2),
N1 INTEGER,
C1 INTEGER
)
ORGANIZE BY ROW;
ALTER TABLE DB2ADMIN.EMPLOYEE_2
DATA CAPTURE NONE
PCTFREE 0
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
COMMIT;
$ db2 -tvf INSERT2.sql
INSERT INTO DB2ADMIN.EMPLOYEE_2 (EMPNO, FIRSTNME, SALARY, C1) VALUES ('3', 'F3', 3, 3);
$ db2 "SELECT * FROM db2admin.EMPLOYEE_2_TRIGGER_LOG"
EMPNO FIRSTNME
------ ------------
1 F1
2 F2
2 record(s) selected.
$ db2 "select TRIGNAME,valid from syscat.triggers"
TRIGNAME VALID
-------------------------------------------------------------------------------------------------------------------------------- -----
POLICY_IR Y
POLICY_IV Y
POLICY_UV Y
POLICY_DR Y
EMPLOYEE_2_TRIG2 X
EMPLOYEE_2_TRIG1 X
6 record(s) selected.
$ db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('TRIGGER','DB2ADMIN','EMPLOYEE_2_TRIG2')"
Return Status = 0
$ db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('TRIGGER','DB2ADMIN','EMPLOYEE_2_TRIG1')"
Return Status = 0
$ db2 "select TRIGNAME,valid from syscat.triggers"
TRIGNAME VALID
-------------------------------------------------------------------------------------------------------------------------------- -----
POLICY_IR Y
POLICY_IV Y
POLICY_UV Y
POLICY_DR Y
EMPLOYEE_2_TRIG1 X
EMPLOYEE_2_TRIG2 X
6 record(s) selected.
$ db2 drop trigger DB2ADMIN.EMPLOYEE_2_TRIG1
DB20000I The SQL command completed successfully.
$ db2 drop trigger DB2ADMIN.EMPLOYEE_2_TRIG2
DB20000I The SQL command completed successfully.
$ db2 "CREATE OR REPLACE TRIGGER DB2ADMIN.EMPLOYEE_2_TRIG1 NO CASCADE BEFORE INSERT ON DB2ADMIN.EMPLOYEE_2 REFERENCING NEW AS NEWR FOR EACH ROW BEGIN ATOMIC CALL DBMS_OUTPUT.PUT_LINE('Trig1 Inserting employee ' || NEWR.EMPNO); CALL DBMS_OUTPUT.PUT_LINE('Trig1 ..New salary: ' || NEWR.SALARY); END
> "
DB20000I The SQL command completed successfully.
$ db2 "CREATE OR REPLACE TRIGGER DB2ADMIN.EMPLOYEE_2_TRIG2 AFTER INSERT ON DB2ADMIN.EMPLOYEE_2 REFERENCING NEW AS NEWR FOR EACH ROW BEGIN ATOMIC INSERT INTO DB2ADMIN.EMPLOYEE_2_TRIGGER_LOG VALUES (NEWR.EMPNO, NEWR.FIRSTNME); END"
DB20000I The SQL command completed successfully.
$ "
> > TRIGNAME VALID
> -------------------------------------------------------------------------------------------------------------------------------- -----
> POLICY_IR Y
> POLICY_IV Y
> POLICY_UV
$ db2 "select TRIGNAME,valid from syscat.triggers"
TRIGNAME VALID
-------------------------------------------------------------------------------------------------------------------------------- -----
POLICY_IR Y
POLICY_IV Y
POLICY_UV Y
POLICY_DR Y
EMPLOYEE_2_TRIG1 Y
EMPLOYEE_2_TRIG2 Y
6 record(s) selected.
$ db2 "SELECT * FROM DB2ADMIN.EMPLOYEE_2_TRIGGER_LOG"
EMPNO FIRSTNME
------ ------------
1 F1
2 F2
2 record(s) selected.
$ db2 -tvf INSERT2.sql
INSERT INTO DB2ADMIN.EMPLOYEE_2 (EMPNO, FIRSTNME, SALARY, C1) VALUES ('3', 'F3', 3, 3)
DB20000I The SQL command completed successfully.
$ db2 "SELECT * FROM DB2ADMIN.EMPLOYEE_2_TRIGGER_LOG"
EMPNO FIRSTNME
------ ------------
1 F1
2 F2
3 F3
3 record(s) selected.
$
Thanks for reading!
Please post your questions or feedback in the comments section.
UID
ibm11140658