Hi

OS-Windows 2003 Server. DB2 Ver 8.2

I have a database containing around 100 tables in one table space. Some of these tables have very large data. My question is can I move some of my existing small tables to another table space on a different drive? If yes how can I do that and also can I create different buffer pool for the different table spaces.

Thanks

Rajeev

Hi,

1. create new bufferpool,
2. create new tablespace where you choose which bufferpool you what to use and which disk drives you want allocate containers to
3. export tables to ixf file
4. drop table
5. import table from ixf file into new tablespace.

Try using Control Center if you don't have experience using commands.

Hope this helps,
Grofaty

if u can import .ixf file in different TB Space
i think ixf format has ddl of table encrypted in it
if you import with import_create option it will gets created in same tablespace again.
Simple del files will be better
export
drop table
create table in new tablespace
import in new table

correct me if i am wrong
--Rahul Singh

If you use the IMPORT command to create the tables, you need to be aware that any indexes or foreign keys will NOT get created. That information is not in the IXF files. You will need to create these entities manually.

Andy

You are correct. It is required to create table before importing. My mistake. So:
create table table_name (col1 int ....) in tablespace

What kind of information is stored in IXF depents on db2 version depending on operating system. On db2 for Linux/Unix/Windows there are the following info in ixf format: column definitions, data and primary keys all other info (foreign key, indexes, triggers, etc) needs to be pulled from database catalog. On db2 for VSE/VM there is no info about primary key in ixf format.

But first of all if there is not a huge database and I assume it is not because it is running on Windows, I suggest creating automatic maintenance tablespace types (new feature in db2 v8.2). The only problem is you need to recreate database and export/import all the tables.

Syntax:
create database database_name automatic storage yes on c:, d:, e:

This command means database will automatically maintain disk storage on all volumes specified. There is no need of managing of disk space.

Hope this helps,
Grofaty

You should probably do a full "generate DDL" from the Control Center before starting with the other steps, to be sure that you don't miss any derived objects (esp. indexes and views). Save and edit that DDL (replacing the tablespace name) and execute it just before the IMPORT.