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

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

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

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.

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

Thanks to both of you. Now its clear to me.

Check if the scripts on this page help you

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

Cheers
Sathyaram