DB2 Size of Table

Question

I'm trying to figure out the size in kb of each table in a schema. I have a query set up, but I'm not sure if I'm getting the correct output. I'm running DB2 v9 LUW.

My Query:

SELECT T.TABNAME, T.TABSCHEMA, COLCOUNT, TYPE, (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) AS TOTAL_SIZE 
FROM SYSCAT.TABLES AS T, SYSIBMADM.ADMINTABINFO AS A 
WHERE T.TABNAME = A.TABNAME 

It works and all, but I am fairly sure that division is required in this calculation. Any suggestions?

Answer 1

All columns in your query is in KB.

If you want to see the size in KB then leave it as is but if you want them in MB, divide the result by 1024.

Alternatively, you can use this query:

SELECT 
  T.TABNAME, 
  T.TABSCHEMA, 
  COLCOUNT, 
  TYPE, 
  (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) AS TOTAL_SIZE_IN_KB,
  (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/1024 AS TOTAL_SIZE_IN_MB,  
  (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) / (1024*1024) AS TOTAL_SIZE_IN_GB
FROM SYSCAT.TABLES AS T, SYSIBMADM.ADMINTABINFO AS A 
WHERE T.TABNAME = A.TABNAME