Hello,
I am using DB2/AIX64 10.5.6.
One of my tablespace utilization rate is 79%. But I found there are only few tables there and their total table size is less than 1GB.
Could you help me to find out the reason of it?
Thanks.
$ db2 "SELECT SUBSTR(TBSP_NAME,1,20) AS TBSP_NAME, DBPARTITIONNUM, TBSP_TOTAL_SIZE_KB / 1024 AS SIZE_MB, TBSP_UTILIZATION_PERCENT FROM sysibmadm.TBSP_UTILIZATION WHERE TBSP_TYPE = 'DMS' AND tbsp_name = 'xxxyyy'"
It shows me the utilization rate is 79%.
However, I found there are only 5 tables and they totally consumed about less than 1GB disk space.
SELECT TABSCHEMA, TABNAME, OWNER, TBSPACEID FROM SYSCAT.TABLES WHERE TABSCHEMA='BIDWUSER' AND TBSPACEID=28;
--- xxxyyy tablespace ID is 28.
Run this SQL statement to see the table size:
SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA, SUBSTR(TABNAME,1,30) TABNAME,
(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 SYSIBMADM.ADMINTABINFO WHERE TABNAME = 'aaaaaaa';
I am using DB2/AIX64 10.5.6.
One of my tablespace utilization rate is 79%. But I found there are only few tables there and their total table size is less than 1GB.
Could you help me to find out the reason of it?
Thanks.
$ db2 "SELECT SUBSTR(TBSP_NAME,1,20) AS TBSP_NAME, DBPARTITIONNUM, TBSP_TOTAL_SIZE_KB / 1024 AS SIZE_MB, TBSP_UTILIZATION_PERCENT FROM sysibmadm.TBSP_UTILIZATION WHERE TBSP_TYPE = 'DMS' AND tbsp_name = 'xxxyyy'"
It shows me the utilization rate is 79%.
However, I found there are only 5 tables and they totally consumed about less than 1GB disk space.
SELECT TABSCHEMA, TABNAME, OWNER, TBSPACEID FROM SYSCAT.TABLES WHERE TABSCHEMA='BIDWUSER' AND TBSPACEID=28;
--- xxxyyy tablespace ID is 28.
Run this SQL statement to see the table size:
SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA, SUBSTR(TABNAME,1,30) TABNAME,
(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 SYSIBMADM.ADMINTABINFO WHERE TABNAME = 'aaaaaaa';