CREATE TRIGGER MY_TRIG AFTER INSERT ON MY_TBL FOR EACH ROW BEGIN IF SOMECONDITION THEN DELETE FROM MY_TBL WHERE PK IN (SELECT PK FROM MY_TBL WHERE SOMEOTHERCONDITION FETCH FIRST 50 ROWS ONLY); RUNSTATS ON TABLE MY_TBL WITH DISTRIBUTION AND DETAILED INDEXES ALL; REORG TABLE MY_TBL; RUNSTATS ON TABLE MY_TBL WITH DISTRIBUTION AND DETAILED INDEXES ALL; END IF; END#
Executing this through the control centre of DB2 9.4.7 (with
# as terminating char) this gives me error
An unexpected token "RUNSTATS" was found following "T 50 ROWS ONLY; ". Expected tokens may include: "COMMENT
I do not understand why. Am I not allowed to use commands such as
REORG inside trigger definitions? Executing the
RUNSTATS command separately works just fine.
A bit more about what I'm trying to achieve here: The table in question contains BLOBs and many of them. In order to limit the disk space used by this table I'd like to set a limit on how big the table may get. So I use the information from the
xxx_OBJECT_P_SIZE columns in
SYSIBMADM.ADMINTABINFO to determine the current size of the table and if it's beyond some limit I simply delete the oldest 50 records of the table. The more records I delete on such an occassion, the less frequently reorg/runstats would have to be executed. So the statements are not being executed for each row inserted - only the condition
SOMECONDITION would be evaluated every time.
Is there a better way of achieving this than by using a trigger?