Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    15

    Exclamation Unanswered: Can tables can be moved out of existing table space to another table space

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Quote Originally Posted by grofaty

    5. import table from ixf file into new tablespace.

    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by rahul_s80
    if you import with import_create option it will gets created in same tablespace again.
    You are correct. It is required to create table before importing. My mistake. So:
    create table table_name (col1 int ....) in tablespace

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by ARWinner
    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.
    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

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •