Converting from dms to sms

[Troels Arvin]

Hello,

With a large database with many tablespaces, primarily on raw devices, how
do I convert it to a sms-based-only database?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/

[Joachim Klassen]

On 26 Jan., 12:41, Troels Arvin <[e-mail]:

Hello,

With a large database with many tablespaces, primarily on raw devices, how
do I convert it to a sms-based-only database?

--
Regards,
Troels Arvin <[e-mail]>http://troels.arvin.dk/

With DB2 9 you might want to extract the DDL with db2look, change the
Tablespace characteristics and assignments and recreate the Objects in
a new database.
Next use db2move with the new copy funktion and LOAD_ONLY mode to copy
the data. You will have to run set integrity afterwards when
constrainst are defined. Also keep in mind that you will have to change
any Identity or sequence starting values in your new database (and very
likely a couple of other things i forgot). You see its not that easy to
migrate from DMS to SMS.
Another option would be to stay with DMS. Just convert your raw devices
to file containers: Add a new stripe set with file containers (large
enough to hold the data) to your existing tablespaces, then drop the
old containers. DB2 will rebalance the data in background into the new
file containers. You can do this one tablespace at at time. After your
done you can alter your tablespace to AUTORESIZE YES and they will
behave nearly like SMS in terms of autogrowing but still delivering
the advantages of DMS. You need to be at V8 FixPak 9 a least to use
AUTORESIZE.

HTH
Joachim

[Troels Arvin]

Hello,

On Fri, 26 Jan 2007 07:45:07 -0800, Joachim Klassen wrote:

>With a large database with many tablespaces, primarily on raw devices, how
do I convert it to a sms-based-only database?

With DB2 9 you might want to extract the DDL with db2look, change the
Tablespace characteristics and assignments and recreate the Objects in
a new database.
Next use db2move

[...]

Thanks. That sounds as complicated as I feared.

Another option would be to stay with DMS. Just convert your raw devices
to file containers: Add a new stripe set with file containers (large
enough to hold the data) to your existing tablespaces, then drop the old
containers. DB2 will rebalance the data in background into the new file
containers.

Hmm; I didn't realize that containers could be dropped. Nice hint.

After your done
you can alter your tablespace to AUTORESIZE YES and they will behave
nearly like SMS in terms of autogrowing but still delivering the
advantages of DMS.

But can the DMS tablespaces also auto-shrink?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/

[Joachim Klassen]

On 26 Jan., 21:38, Troels Arvin <[e-mail]:

Hello,

On Fri, 26 Jan 2007 07:45:07 -0800, Joachim Klassen wrote:

With a large database with many tablespaces, primarily on raw devices, how
do I convert it to a sms-based-only database?

With DB2 9 you might want to extract the DDL with db2look, change the
Tablespace characteristics and assignments and recreate the Objects in
a new database.
Next use db2move[...]

Thanks. That sounds as complicated as I feared.

Another option would be to stay with DMS. Just convert your raw devices
to file containers: Add a new stripe set with file containers (large
enough to hold the data) to your existing tablespaces, then drop the old
containers. DB2 will rebalance the data in background into the new file
containers.Hmm; I didn't realize that containers could be dropped. Nice hint.

After your done
you can alter your tablespace to AUTORESIZE YES and they will behave
nearly like SMS in terms of autogrowing but still delivering the
advantages of DMS.But can the DMS tablespaces also auto-shrink?

--
Regards,
Troels Arvin <[e-mail]>http://troels.arvin.dk/

They will not auto-shrink. If your DMS tablespaces are over-allocated
use ALTER TABLESPACE REDUCE/RESIZE to shrink them manually (beware the
high-watermark!).

To be honest - I've never seen a database which wasn't growing. So if
you have tables which grow large then shrink - put them into SMS
tablespaces. BTW , DMS is a bad choice for temporary tablespaces -
they should always use SMS.

cheers
Joachim