Results 1 to 7 of 7
  1. #1
    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

  2. #2
    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

  3. #3
    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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  5. #5
    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

  6. #6
    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.

  7. #7
    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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •