IBM DB2 备忘录

I. Common Commands

1. Set Environment First

. ./db2profile

2. Create New Database

db2 create db dbname

3. Connect to Database

db2 connect to dbname user username using password

　

II. Utils

1. Configure db2 client

running command db2 and login the db2 command line. Then:

db2=> catalog tcpip node localnodename remote 192.168.0.10 server 50002

db2=> terminate

db2=> catalog database localdbname as remotedbname at node localnodename

db2=> termiante

　

2. Create New Table Space (and Buffer Pool)

CONNECT TO dbname;
CREATE BUFFERPOOL mypool IMMEDIATE SIZE 250 PAGESIZE 32 K ;
CONNECT RESET;


CONNECT TO TEST;
CREATE REGULAR TABLESPACE myspace PAGESIZE 32 K MANAGED BY SYSTEM USING ('/export/folder/filename' ) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL mypool DROPPED TABLE RECOVERY OFF;
CONNECT RESET;


CONNECT TO TEST;
CREATE SYSTEM TEMPORARY TABLESPACE myspacetemp PAGESIZE 32 K MANAGED BY SYSTEM USING ('/export/folder/tempfile' ) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL mypool ;
CONNECT RESET;

3. 列出所有数据库:

db2 list db directory

III. Limitations

1, Default page size of table space is 4k.

2, The length of index name is limited to less than 18.