Adsense

Wednesday, April 17, 2013

Creating Table Space in db2 using command line


Sometime table page size may exceed the default page size 4 K due to increase in the columns & column size which db2move dbname import may fail. for this you have to create table space with bigger page size.
For creating tablespace , First you have to create bufferpool with pagesize . default page size is 4 KB. You can create with the size of 8K, 16 K, 32 K with the following command. for example to create 32k size page give the following command

Linux : db2 create bufferpool testbufpool IMMEDIATE PAGESIZE 32K . This command may run on higher version like Db2 9.5 . Please check the following commad.

For Linux / Windows : db2 create bufferpool testbufpool SIZE 8000 PAGESIZE 32K - where testbufpool is the name of bufferpool.
Now give the following command to create table space .

Windows : db2 "CREATE REGULAR TABLESPACE tblspc PAGESIZE 32 K MANAGED BY SYSTEM USING ('tblspc') BUFFERPOOL testbufpool"
You can mention the path location where the table space to be created.

db2 "create tablespace tblspc pagesize 32k managed by system using ('/data/db2inst1/NODE0000/dbname/T0000002/')" bufferpool testbufpool
Now table space tblspc is created with the page size 32k

Checkout the table space info with the following commands

db2 LIST TABLESPACES SHOW DETAIL
db2 list tablespace containers for 4 - where 4 is the table space ID

2 comments:

  1. Great info. Thank you for sharing your technique. Looking forward for more updates.

    Vicky
    www.gofastek.com

    ReplyDelete
  2. will u please describe this part

    thanks in advance

    managed by system using ('/data/db2inst1/NODE0000/dbname/T0000002/')" bufferpool testbufpool

    ReplyDelete