Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: Changing an SMS table space to a DMS table space

    we are running DB2 9.5 on windows. Is there anyway to Change a System Managed Tablespace to a Database Managed Tablespace?

    We have one Database where whoever created the DB made the Userspace1 Tablespace System Managed. I would like to make it a DMS tablespace so that I can reclaim over 30GB in storage!!! Does anyone have any suggestions? Change be done on a restore? Is there any other way to reclaim this space??

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is no way to directly change a SMS tablespace to DMS. You will have to create the DMS tablespace, then move all the objects in the SMS tablespace to the ne one.

    What about doing a REORG to reclaim the space?

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by itsonlyme44
    I would like to make it a DMS tablespace so that I can reclaim over 30GB in storage!!!
    SMS (unlike DMS) automatically reclaims storage (but like DMS you may want to do a reorg).

    The advantage of DMS is not space utilization, but slightly better performance in certain situations (but do not use DMS for system temporary tablespaces).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2007
    Posts
    288
    Do you mean reorging all the tables in the the tablespace to reclaim the space?? The problem is that there is 32 GB allocated to this SMS tablespace and unlike a DMS, I cannot alter and pare the size down.. So is my only option to create a new DMS tablespace and move all the object to it??

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by itsonlyme44
    Do you mean reorging all the tables in the the tablespace to reclaim the space?? The problem is that there is 32 GB allocated to this SMS tablespace and unlike a DMS, I cannot alter and pare the size down.. So is my only option to create a new DMS tablespace and move all the object to it??
    An SMS tablespace does not have specific amount of space allocated to it. It resides on a specified path (or paths) and automatically grows and shrinks as needed.

    However, in for a page to be freed up, and for the tablespace size to shrink, all rows in the page must be deleted. If you reorg the table, then that will take care of the problem. Just do it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Dec 2007
    Posts
    288
    Thanks! I'll give it a shot!!!

  7. #7
    Join Date
    Dec 2007
    Posts
    288
    I was wrong about this being an SMS tablespace:

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

    I reorged all the tables in the Tablespace and I did a new snapshot. I still have 32GB allocated and only 7% utilization in the tablespace.. Still trying to figure out how to reclaim this 30GB ???

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You need to resize the tablespace container(s), obviously. You may need to run db2dart with the /lhwm switch - it will suggest the ways to lower the high watermark of data in the tablespace.

    By the way, this is the reason why you don't want your temporary tablespaces to be DMS.
    Last edited by n_i; 01-22-09 at 14:48.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by n_i
    By the way, this is the reason why you don't want your temporary tablespaces to be DMS.
    Yes, and another important reason is that when DB2 creates a table in a DMS tablespace it spend extra time looking around to find the best place to put it to have optimum performance. This works well for regular tables that are created once and used many times, but the extra time to optimize the placement is a performance issue when DB2 creates temporary tables for sorting, etc during the execution of a query.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by itsonlyme44
    I was wrong about this being an SMS tablespace:

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

    I reorged all the tables in the Tablespace and I did a new snapshot. I still have 32GB allocated and only 7% utilization in the tablespace.. Still trying to figure out how to reclaim this 30GB ???
    Do a "list tablespaces show detail" to determine the high water mark of the tablespace. You can resize the tablespace to 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
    Please excuse my ignorance.. but how to do resize to the high water mark?? When I try and alter the tablespace via the control center... the option to change the size is not there like it is on the other DMS tablespaces....

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Check out the SQL Reference Vol 2 manual for alter tablespace. You can use InfoCenter or get the PDF manuals for free.
    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
    alter tablespace userspace1 resize (all 10000)

    returns this:

    Table space "USERSPACE1" of type "AUTOMATIC STORAGE" cannot be altered using the "RESIZE

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Just drop it and create a new SMS tablespace.
    ---
    "It does not work" is not a valid problem statement.

  15. #15
    Join Date
    Dec 2007
    Posts
    288
    I guess you mean backup, drop then restore the tablespace?? Can I chance DMS to SMS and vice versa on a restore?

Posting Permissions

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