Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Posts
    28

    Unanswered: Tablespace conversion

    HI All,

    Our database is currently setup for SMS tablespaces and would like to convert these tablespaces to DMS as performance is much better on DMS, is it possible to this conversion from SMS to DMS, anybody can help answer this question?

    thanks to all

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    There's no command to move the tables, so unfortunately you have to EXPORT->DROP->CREATE->IMPORT/LOAD each one.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Oct 2005
    Posts
    109
    how about a redirected restore ?
    I thought I can change it there as well...

    Just out of interest: Please let us know what you gained in performance, also if you could shortly describe your setup would be nice.

    I always hear 'DMS is sooo much faster' versus 'Today on AIX there is nothing much to gain with DMS, esp. in a SAN' - so at least I would very much like to know what you managed.

    Thanks.
    Juliane

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Redirected RESTORE won't work either unfortunately, they have to be the same type (ie. SMS to SMS, DMS to DMS).

    I've seen a lot of set-ups, both SMS and DMS. The company I'm at now has SMS, and we've really seen no compelling reason to change. With RAID our data is already striped at the hardware level. Peformance is excellent, and unless you're using raw devices DMS doesn't have much advantage. Even the raw device advantages are supposed to be less significant now, according to performance gurus I've talked to.

    Others may have a different spin on it, it really depends on how well your OS handles I/O, your hardware, your app, etc.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Oct 2004
    Posts
    28
    Thanks guys for the valuable input, we will probably have to make a big decision whether its really worth going through the pain, I will update this page if any changes.

    Thanks again for your input

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You probably would not see much performance benefit of switching to DMS unless your application does a lot of table scans on large tables.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Sep 2003
    Posts
    237
    If you are on PRE8.2 DB2 release, you can speedup SMS by running db2empfa.
    mota

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dbamota
    you can speedup SMS by running db2empfa.
    By doing this you can only speed up inserts into an SMS tablespace. You'll still be suffering from double buffering, file fragmentation, file system overhead, etc.

  9. #9
    Join Date
    Sep 2003
    Posts
    237
    double buffering, file system overhead, etc. exists for DMS FILES too. RAW tablespaces avoid them.
    mota

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dbamota
    double buffering, file system overhead, etc. exists for DMS FILES too. RAW tablespaces avoid them.
    I'm sure you know that, but that's beside the point; I was correcting your statement about SMS tablespaces and multi-page allocation.

  11. #11
    Join Date
    Sep 2003
    Posts
    237
    Your point well taken.
    mota

Posting Permissions

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