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

There's no command to move the tables, so unfortunately you have to EXPORT->DROP->CREATE->IMPORT/LOAD each one.

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.

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.

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

You probably would not see much performance benefit of switching to DMS unless your application does a lot of table scans on large tables.

If you are on PRE8.2 DB2 release, 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.

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.

Your point well taken.