Results 1 to 4 of 4

Thread: SMS vs. DMS

  1. #1
    Join Date
    Jan 2011
    Posts
    24

    Unanswered: SMS vs. DMS

    Hello

    What do you recommend for temporary tablespaces? What do you recommend for indexes?

    Mike

  2. #2
    Join Date
    Jan 2011
    Posts
    24
    Forgot to mention, we're using DB2 9.7 on Windows and planning to migrate to 10.

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Mikky View Post
    Forgot to mention, we're using DB2 9.7 on Windows and planning to migrate to 10.
    Have you considered automated storage? If not, my two cents is sms for temp and dms for everything else.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you specify automatic storage (good idea to do that about 99% of the time) DB2 will automatically use SMS for system temporary tablespaces, and DMS for others.

    When a new table is created in a DMS tablespace, DB2 spends a lot of time looking for the optimal place inside the tablespace container(s) to put a new table. This is fine for most tables that stay around for awhile, but since a system temporary table gets created and dropped each time a query is run (if such a system temporary table is needed by DB2 for that SQL), then DMS is not suitable for such system temporary tablespaces.

    Indexes should be in their own tablespaces in order to specify (if desired) their own bufferpools (usually a good idea). Again, DB2 will choose DMS with automatic storage (and you should also even if you don't use automatic storage). But you will probably be making a mistake if not using automatic storage. When defining automatic storage, specify multiple file paths for automatic storage on different disks or arrays if possible if there is a lot of data in the database (up to about 4 is sufficient in most cases).
    Last edited by Marcus_A; 04-25-13 at 02:26.
    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
  •