By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,304 Members | 3,132 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,304 IT Pros & Developers. It's quick & easy.

Moving Tables from SMS to DMS tablespace

P: n/a
Hello all,

I have a database around 600GB and all tables were self-created by the
application. Hence, we have only one huge SMS tablespace where all the
tables are stored. To get better performance, we are planning to move
the tables to a DMS tablespace with multiple containers. There are LOTS
of tables and some tables have almost 40GB of data. Can you please
suggest and efficient way of doing this.

Also, I know that even a 32KB page size DMS tablespace can grow up to a
max of 512 GB, so since the current tablespace is already that big, I
wonder if I would be able to move to a DMS tablespace.

Does having multiple SMS containers help in performance??

Thannks.

May 25 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Here are some thoughts

1. If you have space to create new tablespace do like this
declare cursor/load from cursor
Once the data load is successful then drop the OLD one.

2. If your tablespace is reaching 512 GB not a single table then you
are o.k
Find the biggest tables in the Tablespace and then create seperate
tablespaces for these big tables.

cheers...
Shashi Mannepalli

May 25 '06 #2

P: n/a
To the last part of your question: Yes. Multiple containers will always
give you better performance for SMS and DMS. If the drives are in an RAID
configuration then one container per array.

As to moving your data from SMS to DMS there's probably only one way which
will lead you to alot of editing.
Use db2look to extract all the ddl for the database with the proper parms.
A db2look -h will help.
Edit the output file so that the create tablespace statements are changed to
create the proper DMS definitions. You can add statements to the file.
Edit the create table statements to point the right table at the right
tablespace.
Since you will be using DMS tablespaces, think about separating the index
from the data. Multiple DMS tablespaces can be used for this.

Once you have defined this. Backup your current db.
Use db2move command to extract the data to a target directory (big!!!).

Drop the current db.
Re-Create the db with normal defaults.
Run the output of the db2look file:
db2 connect to <dbname>
db2 -tf <oufilename>
Use db2move with a load option to load the data in the the newly created
tblspces and tables.

It may not be totally elegant, will require lots of keying, but it works.

Unfortunately, there's no way to alter SMS to DMS, nor is there to backup
from SMS to DMS. So, objects need to be re-created and data moved.
HTH, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"db2dude" <db******@gmail.com> a écrit dans le message de news:
11**********************@i39g2000cwa.googlegroups. com...
Hello all,

I have a database around 600GB and all tables were self-created by the
application. Hence, we have only one huge SMS tablespace where all the
tables are stored. To get better performance, we are planning to move
the tables to a DMS tablespace with multiple containers. There are LOTS
of tables and some tables have almost 40GB of data. Can you please
suggest and efficient way of doing this.

Also, I know that even a 32KB page size DMS tablespace can grow up to a
max of 512 GB, so since the current tablespace is already that big, I
wonder if I would be able to move to a DMS tablespace.

Does having multiple SMS containers help in performance??

Thannks.


May 25 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.