How to compute storage utilization per schema

[nbharatha]

Hi,

Environment:
Only one database per instance however each application is given it's
own schema.

My objective is to compute the space utilized by each application on
the DB2 Database.
Following is my approach and request you to suggest / advise for
correctness.

SELECT T.TABSCHEMA AS SCHEMA_NAME
, count(T.TABNAME ) AS NUM_TABLES
, sum(T.NPAGES) AS TOT_NPAGES
FROM
SYSCAT.TABLES T where T.NPAGES 0 group by T.TABSCHEMA ;

DBSIZE=SUM of all NPAGES ( from above query) * PAGE SIZE

Summing up all the NPAGES for all schema from above result set and
multiplying by Page Size.
The DBSIZE as computed above is no where close enough to the Database
size given by DB2 procedure GET_DBSIZE_INFO .

Would appreciate if someone can inform what i'm missing or my approach
is incorrect.

Thanks,
Naveen.

[Joachim Klassen]

On 19 Okt., 04:02, nbhara...@gmail .com wrote:

Hi,

Environment:
Only one database per instance however each application is given it's
own schema.

My objective is to compute the space utilized by each application on
the DB2 Database.
Following is my approach and request you to suggest / advise for
correctness.

SELECT T.TABSCHEMA AS SCHEMA_NAME
, count(T.TABNAME ) AS NUM_TABLES
, sum(T.NPAGES) AS TOT_NPAGES
FROM
SYSCAT.TABLES T where T.NPAGES 0 group by T.TABSCHEMA ;

DBSIZE=SUM of all NPAGES ( from above query) * PAGE SIZE

Summing up all the NPAGES for all schema from above result set and
multiplying by Page Size.
The DBSIZE as computed above is no where close enough to the Database
size given by DB2 procedure GET_DBSIZE_INFO .

Would appreciate if someone can inform what i'm missing or my approach
is incorrect.

Thanks,
Naveen.

IIRC, pages where LOB's are stored are not counted for NPAGES

[Jurgen Haan]

Joachim Klassen wrote:

>
IIRC, pages where LOB's are stored are not counted for NPAGES

If your schema contains exclusive tablespaces (SMS or DMS in a file) you
can also just query the size of the tablespace(s) on OS level (du -hs
--max-depth=1). At least, that's how I do it. Not really fancy, but
effective nevertheless. CRON executes a script every night that gives a
sorted directory overview for each tablespace (every client of ours has
his own tablespace) and puts that in a table.