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.

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

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" <db2inaix (AT) gmail (DOT) com> a écrit dans le message de news:
1148524115.323565.234200 (AT) i39g20...oglegroups.com...