Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: reclaiming tablespace userspace1 space

    DB2 UDB v9

    The userspace1 tablespace is constantly running at 95% and is about 20GB .

    How can I reclaim the space?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What does that exactly mean "running at 95%" ?

    Also, please provide the details of the tablespace configuration (DMS, SMS. containers, Auto storage, etc).
    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
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Maybe 95% of 20GB means that the tablespace is 20GB in size and 95% of that are allocated? If so, why bother with those 5%?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Dec 2007
    Posts
    288
    dropped a bunch of tables from USERSPACE1. Can't figure out how to reclaim the space. Is this a DB2 System Managed tablespace? normally I can rezise a tablespace that I've created, this one I can't..

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    We cannot tell you which type your tablespace has. You may want to use commands like LIST TABLESPACES and related no collect the necessary details and provide them to us - like Marcus requested.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Dec 2007
    Posts
    288
    MY TABLESPACES

    Database Connection Information

    Database server = DB2/NT 9.5.0
    SQL authorization ID = ISJXR18
    Local database alias = UDBDWDUT


    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    list tablespaces;
    ------------------------------------------------------------------------------
    list tablespaces

    Tablespaces for Current Database

    Tablespace ID = 0
    Name = SYSCATSPACE
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 1
    Name = TEMPSPACE1
    Type = System managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 2
    Name = USERSPACE1
    Type = Database managed space
    Contents = All permanent data. Large table space.
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 3
    Name = SYSTOOLSPACE
    Type = Database managed space
    Contents = All permanent data. Large table space.
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 4
    Name = PAGEPERF
    Type = System managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 5
    Name = INDSP16K
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 6
    Name = INDSP32K
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 7
    Name = INDSP4K
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 8
    Name = TBLSP16K
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 9
    Name = TBLSP4K
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 10
    Name = TBLSP32K
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 11
    Name = SYSTOOLSTMPSPACE
    Type = System managed space
    Contents = User Temporary data
    State = 0x0000
    Detailed explanation:
    Normal

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Please post results of:

    db2 connect to <db-name>
    db2 list tablespaces show detail
    db2 list tablespace containers for 2 show detail
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    To answer one of your questions above: USERSPACE1 is not a "system managed space":
    Tablespace ID = 2
    Name = USERSPACE1
    Type = Database managed space
    Contents = All permanent data. Large table space.
    State = 0x0000
    Detailed explanation:
    Normal
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Dec 2007
    Posts
    288
    list tablespaces show detail

    list tablespace containers for 2 show detail

    See attached : )
    Attached Files Attached Files

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The report show that you have about 76% utilization of the userspace1 tablespace. But the high water mark is at about 99%, which means you can not reclaim space below that (until you reduce the high water mark).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Dec 2007
    Posts
    288
    Thank you. Can you point me to where I might read up on this?

    I find it difficult to find anything on how to manage the high water mark in the IBM infocenter

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You don't need to "reclaim" space unless you want to make the container smaller. With the utilizaiton at 76% I am not sure why you want to do that.

    Keep in mind that DB2 will reuse (reclaim) any space on a partially used page that has enough room to insert a row, or on an empty page, unless you have "append" set on the table (ehich will only insert at the end of the table).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Dec 2007
    Posts
    288
    I guess my thinking is that the utilization is really NOT at 76%. There were many objects moved that used this tablespace errorenously. Now the high-water mark remains at 19GB and we'de like to shrink it down..

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The 76% is based on pages used. If the page only has one row on it, it is still considered used, even though additional rows can be inserted into the page (unless the table is set to append). You can fix this part with a reorg of the table and indexes (indexes automatically reorged with offline table reorg).

    As far as the high water mark, check out the db2dart command (Command Reference manual). This will mention some things you can do to reduce the high water mark on that tablespace.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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