Which size is correct? 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.


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



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 ?
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,&lt;SCHEMA_NAME&gt;,&lt;DATABASE_NAME&gt;) 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 &lt; (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!