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 SQL0104N
:
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 RUNSTATS
/REORG
inside trigger definitions? Executing the RUNSTATS
command separately works just fine.
------Edit------
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?
------Edit------
REORG
weekly if it's warranted by reviewing results ofREORGCHK
. – mustaccio Dec 2 '13 at 15:27REORG
/RUNSTATS
as you suggest, will the values inSYSIBMADM.ADMINTABINFO
update? If not, then this is the reason why I need to run it every time, because otherwise the conditionSOMECONDITION
would be true too often and too many rows would be deleted. – Alexander Tobias Bockstaller Dec 2 '13 at 16:15FOR EACH STATEMENT
trigger). – mustaccio Dec 2 '13 at 16:51RUNSTATS
/REORGS
to the trigger. My understanding is that your delete statement will not be finished until theRUNSTATS
/REORGS
are done. Thus increasing the running time of the delete statement. Consider setting a flag when you run your trigger and have a different process check that flag on a regular basis. – Peter Schuetze Dec 3 '13 at 15:29cron
, Windows Scheduler or similar. – Ian Bjorhovde Dec 3 '13 at 16:21