DB2: Cannot use RUNSTATS/REORG in trigger definitions?

Question

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------

Answer 1

You can only use SQL statements in a trigger. RUNSTATS and REORG are not SQL statements; they are DB2 CLP commands, and only the command line processor understands them. You can try using the system stored procedure ADMIN_CMD() to invoke those utilities, however, 1) it still may not work because of the nature of the utilities and 2) even it does work it would not be a good idea.

There are other problems with your trigger. You cannot use FETCH FIRST in a delete statement, for example. And what is the point of updating statistics just before a REORG?

Answer 2

You may wish to use the SYSTOOLS tables that come with DB2 to check up on table sizes.

You first need to create the tables running the following stored procedure:

db2 call create_storagemgmt_tables('SYSTOOLSPACE')

This will create the tables. If you find you don't need them, you can always drop them later using

db2 call drop_storagemgmt_tables()

Once the tables are created, run RUNSTATS on all tables at least once. Then run the following against the whole database:

db2 call capture_storagemgmt_info(0,NULL,'<db_name>')

This will populate everything the first time. You can always take care of just the table you care about later with running RUNSTATS over just that one table and then running

db2 call capture_storagemgmt_info(4,'<schema_name>','<table_name>')

Now you can query these tables to get sizing info on your table.

I then tend to use the following to query:

select a.obj_schema as "Schema",    
a.obj_name as "Table Name", 
a.estimated_size/1024/1024 as "Estimated Size in MB",
a.main_tbspace as "Tablespace", 
a.stmg_timestamp as "Time Last Updated“from (
  select o.obj_schema, 
  o.obj_name, 
  t.estimated_size, 
  t.main_tbspace,
  t.stmg_timestamp, 
  rownumber() over(
    partition by o.obj_schema, o.obj_name 
    order by t.stmg_timestamp desc) as rn     
  from systools.stmg_object as o, 
  systools.stmg_table as t  
  where  o.obj_id = t.obj_id and 
  o.stmg_timestamp = t.stmg_timestamp
) as a
where a.rn = 1
with UR

or this for top twenty offenders

select b.obj_schema as "Schema",
b.obj_name as "Table Name",
b.estimated_size/1024/1024 as "Estimated Size in MB",
b.main_tbspace as "Tablespace", 
b.stmg_timestamp as "Time Last Updated"
from (
  select a.obj_schema,  
  a.obj_name, 
  a.estimated_size, 
  a.main_tbspace, 
  a.stmg_timestamp,  
  rank() over(order by a.estimated_size desc) as top_offenders  
  from  (
    select o.obj_schema, 
    o.obj_name, 
    t.estimated_size, 
    t.main_tbspace, 
    t.stmg_timestamp,       
    rownumber() over( 
      partition by o.obj_schema, o.obj_name  
      order by t.stmg_timestamp desc) as rn    
    from systools.stmg_object as o, 
    systools.stmg_table as t            
    where  o.obj_id = t.obj_id and 
    o.stmg_timestamp = t.stmg_timestamp   
  ) as a
  where a.rn = 1
) as b
where b.top_offenders < 21
with UR

Over time you do need to prune these tables. I tend to use the following to prune them:

delete from systools.stmg_root_object
where stmg_timestamp < (current timestamp - 30 day)

You can always tweak what I have above to look at specific tablespaces. Check out information center for more information about this.