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 ?