Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    17

    Unanswered: How to know the tablespace size using command

    When I create the tablespace, I can only set the "pagesize" and the "extent size",but I can't set the tablespace physical size like oracle.
    Also , the command "list tablespace containers for tablespace_id show detail" can only show the "total pages " and "used pages",they are equals。Actually,the tablespace's size is 580M when I telnet the server to see the physical tablespace file。
    Please tell me how to get the actual tablespace used by db,also I want to knnow the fragment percentage in the tablespace file。
    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you use SMS (system managed storage) in your Create Tablespace, the size is indefinite and will grow as needed in the path you defined.

    If you use DMS (database managed storage) the size is finite and the list tablespace command (show detail) will tell you the size.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2004
    Posts
    17
    thanks,as to the SMS tablespace,I think I can get the snapshot for tablespace physical size, do you think so? How to ?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you do a list tablespaces show detail you will get the following information:

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

    Note that on SMS, the Total Pages is the same size Used Pages, since the tablespace increases and decreases as needed (although table/index reorgs may be needed to free up empty pages).

    DMS Example (Name assigned by DBA):
    Tablespace ID = 4
    Name = TBSPACE1
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 768000
    Useable pages = 767968
    Used pages = 253440
    Free pages = 514528
    High water mark (pages) = 476832
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1

    Note that on DMS, the Total Pages is the size specified by the DBA when the tablespace was created (actually the total of all the containers).
    Last edited by Marcus_A; 08-01-04 at 01:11.
    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
    Jun 2004
    Posts
    115
    Hi

    How can i get something like this

    Tablepsace-Name Free Space M bytes Used-Spcae in M bytes


    any syscat views that tell me this

    regards
    Hrishy

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Since you have not mentioned the version, I would assume you are in Version 8 ...

    db2 "select * from table( SNAPSHOT_TBS_CFG ('sample',-1)) as test"

    and
    db2 "select * from table( SNAPSHOT_CONTAINER ('sample',-1)) as test"

    If you are still on Version 7 (or lower) ,

    then a script in http://www.db2click.com/scripts.htm
    should help

    Cheers
    Sathyaram
    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
  •