Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Tablespace utilization rate is 80% but only few small tables there.

Status
Not open for further replies.

handle907

MIS
Aug 27, 2008
2
HK
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';
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top