IBM Support

How to revalidate Inoperative Triggers in DB2

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.

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140658