Reporting Total Disk Space Usage for All Databases.

Question

Reporting the total amount of disk space used for each database in an instance - How to find?

Answer

The query in the CODE section is used to allow the database administrator to use system catalog tables to find how much space is being used by each database in an instance.

The results are interpreted as follows:

dbspace
name of the dbspace

database
name of the database

total
total pages used for a database

used
total pages used for both data and indexes

data
total pages used for data

idx
total pages used for indexes

CODE

DATABASE sysmaster;
SELECT s.name dbspace,
n.dbsname database,
SUM(ti_nptotal) total, 
SUM(ti_npused) used,
SUM(ti_npdata) data, 
SUM(ti_npused) - SUM(ti_npdata) idx
FROM systabinfo i,systabnames n, sysdbspaces s
WHERE i.ti_partnum = n.partnum 
AND partdbsnum(i.ti_partnum)=s.dbsnum 
GROUP BY 1,2
ORDER BY 1,2

SAMPLE OUTPUT

dbspace   rootdbs930uc1
database  central
total     680
used      449
data      263
idx       186

dbspace   rootdbs930uc1
database  client
total     664
used      447
data      262
idx       185


NOTE : The output values are in pages.

https://www-304.ibm.com/support/docview.wss?uid=swg21104838