If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > How to find my database size?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: Calcutta, India. Currently in London
Posts: 52
Red face How to find my database size?

Hi All,

How to find my current database size in DB2. I am looking for something similar to the following in Oracle:

select sum(bytes/1024) from dba_data_files
select sum(bytes/1024) from dba_segments
select sum(bytes/1024) from dba_free_space

Thanks
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: Copenhagen
Posts: 220
Re: How to find my database size?

You have to calculate
your DB size based on the
collective size and utilization
degree of your tablespaces

'db2 list tablespaces show detail'

Will list them for you.

Someone might have a script,
depending on your platform.

Please see the top to posts in this forum.

BOW
__________________
Kristian K. Hansen
Project Supervisor
National Board of Health
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: Calcutta, India. Currently in London
Posts: 52
Re: How to find my database size?

Thanks.. I got this. I am using DB2UDB V8.1 on WIN2000. Now could u please tell me, which parameters denote my database size in MB?

db2 => list tablespaces show detail

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 4629
Useable pages = 4629
Used pages = 4629
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 16
Number of containers = 1

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 16
Number of containers = 1

Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 82
Useable pages = 82
Used pages = 82
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 16
Number of containers = 1
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,702
If you only have SMS tablespaces (system managed tablespaces) then you can take the total pages times the page size. If you had any DMS tablespaces, there is a difference between allocated space (total pages) and used space (used pages), but for your database that is not a factor.

Temporary tablespaces grow and shrink as they are used by DB2 for queries, sorts, utlilities, etc.

In addtion to the tablespaces, there is disk space used for the logs. If you have circular logging, you can calculate that based on the database config parms for number of primary and seconday log files, and the size of each file. For archive logging (need for roll forward recovery), you may also have archived logs stored on disk.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: Copenhagen
Posts: 220
Re: How to find my database size?

Well I can see that you haven't
created any tablespaces besides
the required ones.

But if you take your used pages
inside every TS and multiply them
by the page size, you will get a tota
per TS, that you can sum across to
get the grand total. I your case:

(4629 x 4096 bytes) +
(1 x 4096 bytes) +
(82 x 4096 bytes)

= 19.300.352 bytes = 18,4 MB
__________________
Kristian K. Hansen
Project Supervisor
National Board of Health
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: Calcutta, India. Currently in London
Posts: 52
Re: How to find my database size?

Thanks to both of you. Now its clear to me.
Reply With Quote
  #7 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
Re: How to find my database size?

Check if the scripts on this page help you

http://www.db2click.com/scripts.htm

Cheers
Sathyaram

Quote:
Originally posted by gpeee
Thanks to both of you. Now its clear to me.
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On