1

I have to estimate the size of a DB2 DB; I used several methods and the problem is I get huge differences. In order to simplify the problem, I'll focus on a unique table.

  1. Use of the following SQL command :

      SELECT 
      TABNAME,
      TABSCHEMA, 
      SUM(DATA_OBJECT_P_SIZE) AS DATA_OBJECT_P_SIZE,
      SUM(INDEX_OBJECT_P_SIZE) AS INDEX_OBJECT_P_SIZE,
      SUM(LONG_OBJECT_P_SIZE) AS LONG_OBJECT_P_SIZE,
      SUM(LOB_OBJECT_P_SIZE) AS LOB_OBJECT_P_SIZE,
      SUM(XML_OBJECT_P_SIZE) AS XML_OBJECT_P_SIZE
      FROM
      SYSIBMADM.ADMINTABINFO
      WHERE TABNAME IN (‘TABLENAME')
      GROUP BY 
      TABNAME,
      TABSCHEMA
    

For table A, I get a total of 135MB

  1. Use of the "Estimate Size" functionnality in DB2 Control Center

For table A, I get a total of 3.5MB

At this point, the difference is REALLY huge. So i digged a little and decided to make an exact copy of table A (without indexes) with the 2 following commands

     CREATE TABLE B AS (SELECT *  FROM A) DEFINITION ONLY;         
     INSERT INTO B SELECT * FROM A;           

Next I mesured the size of B (which is a clone of table A) : both methodes give me 3.5MB for this table

So here is my problem : I would like to know why does the SQL command tells me data weighs 38 times more on the original table ? I suspect preallocation and/or fragmentation because the original table is used for insert/update/delete statements, but commands like REORG (with reclaim extent), ALTER TABLESPACE REDUCE (and RUNSTATS) don't change the result.

Any clue ?

4
  • Could it be that , in the first case, a same table name is used with several schema therefore returning more than one table ? – P. O. Jan 9 '15 at 13:01
  • Nope, I checked without the group by statement and adding a schema filter : still 135MB – FrenchGuy Jan 9 '15 at 13:11
  • I wouldn't rely on Control Center, it's been deprecated for a long time. What DB2 version do you have? – mustaccio Jan 9 '15 at 13:19
  • 9.7 running on Unix. I don't like Control Center either since I have no idea how it's mesuring the size... But I get same result on the copy of the table so it can't be so wrong ! – FrenchGuy Jan 9 '15 at 13:21
1

There is a handy dandy utility in DB2 LUW (in at least version 9.7 and higher). I'm not sure when it exactly entered the product.

Sizing tables! And stored procedures that help calculate size.

First you need to run the stored procedure CREATE_STORAGEMGMT_TABLES, specifying the tablespace where you wish to put these (I recommend SYSTOOLSPACE). Once you have those you can run the CAPTURE_STORAGEMGNT_INFO stored procedure. It is generally recommended that you first run RUNSTATS on all tables with RUNSTATS ON TABLE (TABLESCHEMA.TABLENAME) ON KEY COLUMNS AND INDEXES ALL. Then run call capture_storagemgmt_info(0,<SCHEMA_NAME>,<DATABASE_NAME>) at least the first time (as per recommendation from IBM). After that you can run it for specific objects. This gives you the size in bytes for everything, tablespaces, tables, indexes, partitions, your database itself....

You do need to prune these tables manually as they will grow over time with use. (The reason is they just INSERT new snapshots of what sizes are.) The nice thing is you can use these to trend growth for your objects and even the whole database. If you wish to prune the tables, I tend to use delete from systools.stmg_root_object where stmg_timestamp < (current timestamp - 30 day). There is a cascading DELETE from that parent object (at least right now) that takes care of cleaning out all the child tables.

Here is an example of a report I run across this

select
t.main_tbspace as tablespace,
o.obj_schema as schema,
o.obj_name as table,
(max(t.estimated_size))/1024/1024 as maximum_size_in_mb,
(min(t.estimated_size))/1024/1024 as minimum_size_in_mb,
(avg(t.estimated_size))/1024/1024 as average_size_in_mb,
count(t.stmg_timestamp) as snapshots
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
group by t.main_tbspace,o.obj_schema,o.obj_name
order by maximum_size_in_mb desc, average_size_in_mb desc, minimum_size_in_mb desc
with UR"

And if you ever don't want the tables anymore, there is always the DROP_STORAGEMGTM_TABLES stored procedure.

Hope this helps!

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.