-- |-------------------------------------------------| -- | | -- | DB2 CAPACITY REPORT | -- | U% = PERCENT OF CAPACITY USED | -- | A% = PERCENT OF CAPACITY ALLOCATED | -- | | -- |-------------------------------------------------| SELECT 'your subsys' AS "SSID" , SUBSTR(CORR1.TSDBNAME CONCAT '.' CONCAT CORR1.TSNAME CONCAT '.' CONCAT CHAR(DIGITS(CORR1.PART)),1,25) AS "DB.TS.PART" , DEC(DEC(NPAGES) / DEC(MAXDSSIZE/(400*PGMULT)),5,2) AS "U% OF MAX" , DEC(CURRSPACE/MAXDSSIZE * 100,5,2) AS "A% OF MAX" , DEC(ROUND(TOTALROWS / (DEC(DEC(NPAGES) / DEC(MAXDSSIZE/(4*PGMULT)),7,2)),0) - TOTALROWS,11,0) AS "ROOM TO ADD" , DEC(CURRSPACE / 48,7,0) AS "TRACKS" -- , MAXDSSIZE AS "MAX SIZE" -- , CURRSPACE , TOTALROWS FROM ( SELECT RTRIM(TS.DBNAME) AS "TSDBNAME" , RTRIM(TS.NAME) AS "TSNAME" , STATS.PARTITION AS "PART" , STATS.NPAGES AS "NPAGES" , DEC(STATS.SPACE,11,0) AS "CURRSPACE" , STATS.EXTENTS AS "CURREXTS" , CASE WHEN DSSIZE > 0 THEN DSSIZE WHEN SEGSIZE > 0 THEN 2097152 * 32 WHEN SEGSIZE = 0 AND PARTITIONS = 0 THEN 2097152 * 32 WHEN TYPE = 'L' THEN 4194304 WHEN TYPE = 'K' THEN 4194304 WHEN TYPE = 'O' THEN 99999999 WHEN TYPE = ' ' AND PARTITIONS BETWEEN 1 AND 16 THEN 4194304 WHEN TYPE = ' ' AND PARTITIONS BETWEEN 17 AND 32 THEN 2097152 WHEN TYPE = ' ' AND PARTITIONS BETWEEN 33 AND 64 THEN 1048576 WHEN TYPE = ' ' AND PARTITIONS > 64 THEN 4194304 ELSE 999999999 END AS "MAXDSSIZE" , UPDATESTATSTIME , DEC(TOTALROWS,13,0) AS "TOTALROWS" , DEC(TS.PGSIZE/4,5,1) AS "PGMULT" FROM SYSIBM.SYSTABLESPACE AS TS FULL OUTER JOIN SYSIBM.SYSTABLESPACESTATS AS STATS ON TS.DBNAME = STATS.DBNAME AND TS.NAME = STATS.NAME WHERE TS.DBNAME NOT LIKE 'WRK%' AND TS.DBNAME NOT LIKE 'TEMP%' AND TS.TYPE NOT IN ('G','O', 'P') AND TOTALROWS > 100 ) AS CORR1 WHERE DEC(DEC(NPAGES) / DEC(MAXDSSIZE/(400*PGMULT)),5,2) >= 80 --AND DEC(DEC(NPAGES) / DEC(MAXDSSIZE/4),5,2) * 100 < 102 ) --OR ( DEC(DEC(CURRSPACE) / DEC(MAXDSSIZE),5,2) * 100 > 95) ORDER BY 3 DESC, 2 DESC WITH UR ;