CODEWITHSUNDEEP
sqldb2db2-luw
user1527185
user1527185

Reputation:

DB2 Size of Table

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?

Upvotes: 0

Views: 6679

Answers (1)

ramazan polat
ramazan polat

Reputation: 7880

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

Upvotes: 3

Related Questions