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

Database size

P: n/a
Dear all,

I would like to ask is there any DB2 SQL statement that can be used to show
the overall database size?
(i.e. display the allocated/used data files or tablespaces (either SMS or
DMS) size??)

Thanks!

Henry
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
I don't know of any SQL that will do that but the following command will:
list tablespace containers for n show detail
where n is the tablespace number (TSID) for the tablespace you are
interested in viewing. From my RHEL4 UDB 8.1 FP9 system after connecting
to database SAMPLE:

[.....]$ db2 list tablespace containers for 2 show detail

Tablespace Containers for Tablespace 2

Container ID = 0
Name =
/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLT0002.0
Type = Path
Total pages = 407
Useable pages = 407
Accessible = Yes
UDB 8.2 has a "Storage management tool" that will store snapshot data in
tables that are accessable with SQL. It can also "monitor" storage and
tack actions when user-defined threshholds have been reached. Check the
administration manuals for "Managing Storage".

Phil Sherman

kaming wrote:
Dear all,

I would like to ask is there any DB2 SQL statement that can be used to show
the overall database size?
(i.e. display the allocated/used data files or tablespaces (either SMS or
DMS) size??)

Thanks!

Henry

Nov 12 '05 #2

P: n/a
Check out get_dbsize_info procedure.....
Keith Ponnapalli
IBM Certified Advanced Database Administrator - DB2 UDB V8.1 for
Linux,UNIX, and Windows
INFORMIX Certified Database Administrator
Phil Sherman wrote:
I don't know of any SQL that will do that but the following command will:
list tablespace containers for n show detail
where n is the tablespace number (TSID) for the tablespace you are
interested in viewing. From my RHEL4 UDB 8.1 FP9 system after connecting
to database SAMPLE:

[.....]$ db2 list tablespace containers for 2 show detail

Tablespace Containers for Tablespace 2

Container ID = 0
Name =
/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLT0002.0
Type = Path
Total pages = 407
Useable pages = 407
Accessible = Yes
UDB 8.2 has a "Storage management tool" that will store snapshot data in
tables that are accessable with SQL. It can also "monitor" storage and
tack actions when user-defined threshholds have been reached. Check the
administration manuals for "Managing Storage".

Phil Sherman

kaming wrote:
Dear all,

I would like to ask is there any DB2 SQL statement that can be used to show
the overall database size?
(i.e. display the allocated/used data files or tablespaces (either SMS or
DMS) size??)

Thanks!

Henry


Nov 12 '05 #3

P: n/a
Check out get_dbsize_info procedure.....
Keith Ponnapalli
IBM Certified Advanced Database Administrator - DB2 UDB V8.1 for
Linux,UNIX, and Windows
INFORMIX Certified Database Administrator
Phil Sherman wrote:
I don't know of any SQL that will do that but the following command will:
list tablespace containers for n show detail
where n is the tablespace number (TSID) for the tablespace you are
interested in viewing. From my RHEL4 UDB 8.1 FP9 system after connecting
to database SAMPLE:

[.....]$ db2 list tablespace containers for 2 show detail

Tablespace Containers for Tablespace 2

Container ID = 0
Name =
/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLT0002.0
Type = Path
Total pages = 407
Useable pages = 407
Accessible = Yes
UDB 8.2 has a "Storage management tool" that will store snapshot data in
tables that are accessable with SQL. It can also "monitor" storage and
tack actions when user-defined threshholds have been reached. Check the
administration manuals for "Managing Storage".

Phil Sherman

kaming wrote:
Dear all,

I would like to ask is there any DB2 SQL statement that can be used to show
the overall database size?
(i.e. display the allocated/used data files or tablespaces (either SMS or
DMS) size??)

Thanks!

Henry


Nov 12 '05 #4

P: n/a
Thanks - I missed this one because it appears to be documented only in
the information center material. There is a one-liner reference to it in
the SQL Reference V1, buried in table 16 - supported functions. I also
noted that, in the documentation, it states "expensive to run" and that
the default is to update if the information is older than 30 min.

It would be very nice if these administrative routines were documented
in the administration manuals.

Phil Sherman

Keith wrote:
Check out get_dbsize_info procedure.....
Keith Ponnapalli
IBM Certified Advanced Database Administrator - DB2 UDB V8.1 for
Linux,UNIX, and Windows
INFORMIX Certified Database Administrator
Phil Sherman wrote:
I don't know of any SQL that will do that but the following command will:
list tablespace containers for n show detail
where n is the tablespace number (TSID) for the tablespace you are
interested in viewing. From my RHEL4 UDB 8.1 FP9 system after connecting
to database SAMPLE:

[.....]$ db2 list tablespace containers for 2 show detail

Tablespace Containers for Tablespace 2

Container ID = 0
Name =
/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLT0002.0
Type = Path
Total pages = 407
Useable pages = 407
Accessible = Yes
UDB 8.2 has a "Storage management tool" that will store snapshot data in
tables that are accessable with SQL. It can also "monitor" storage and
tack actions when user-defined threshholds have been reached. Check the
administration manuals for "Managing Storage".

Phil Sherman

kaming wrote:
Dear all,

I would like to ask is there any DB2 SQL statement that can be used to show
the overall database size?
(i.e. display the allocated/used data files or tablespaces (either SMS or
DMS) size??)

Thanks!

Henry


Nov 12 '05 #5

P: n/a
In article <Cx****************@newssvr33.news.prodigy.com>, Phil
Sherman (ps******@ameritech.net) says...
It would be very nice if these administrative routines were documented
in the administration manuals.

Phil Sherman


It's documented in the 8.2 manuals at http://tinyurl.com/95k8a
Nov 12 '05 #6

P: n/a
Thanks you very much for your help.

Henry

"Gert van der Kooij" <ge**@invalid.nl> wrote in message
news:MP************************@news.xs4all.nl...
In article <Cx****************@newssvr33.news.prodigy.com>, Phil
Sherman (ps******@ameritech.net) says...
It would be very nice if these administrative routines were documented
in the administration manuals.

Phil Sherman


It's documented in the 8.2 manuals at http://tinyurl.com/95k8a

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.