By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

How to compute storage utilization per schema

P: n/a
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.

Oct 19 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Oct 19 '07 #2

P: n/a
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.
Oct 19 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.