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

|improve this question
  • The better way of doing it would be not doing it at all. There's no point in updating statistics or reorganizing the table each time you delete 50 rows (unless your table only has 51 rows). You can simply delete your rows in the trigger and safely run REORG weekly if it's warranted by reviewing results of REORGCHK. – mustaccio Dec 2 '13 at 15:27
  • If I delete rows and do not run REORG/RUNSTATS as you suggest, will the values in SYSIBMADM.ADMINTABINFO update? If not, then this is the reason why I need to run it every time, because otherwise the condition SOMECONDITION would be true too often and too many rows would be deleted. – Alexander Tobias Bockstaller Dec 2 '13 at 16:15
  • 1
    The physical free space will not be release until a REORG, that's right. I must say yours is one of the strangest approaches to space management that I've seen. Good luck (and at least consider using a FOR EACH STATEMENT trigger). – mustaccio Dec 2 '13 at 16:51
  • just some additional thought here. Assuming you can add RUNSTATS/REORGS to the trigger. My understanding is that your delete statement will not be finished until the RUNSTATS/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:29
  • You would be far better off performing this kind of maintenance using a scheduling tool like cron, Windows Scheduler or similar. – Ian Bjorhovde Dec 3 '13 at 16:21
4

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?

|improve this answer
  • I agree. REORG or RUNSTATS after every row inserted is OVERKILL. – Twinkles Dec 2 '13 at 13:40
  • Thanks for your comments. Please see the edit I made to my original post for some more background info. – Alexander Tobias Bockstaller Dec 2 '13 at 14:47
  • @mustaccio: I reduced the sample code to the absolute minimum and it appears, that I built in a bug - you're right that fetch first doesnt work with delete directly, but I fixed that in the example now. – Alexander Tobias Bockstaller Dec 2 '13 at 14:50
3

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.

|improve this answer
  • I'll add to this to say this is the easier way to keep track of table sizes. I still agree with mustaccio that you don't want to do this through triggers, etc. You would be better off scripting this and just checking say once a week or something. – Chris Aldrich Dec 3 '13 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.