db2基础知识

连接数据库：
  connect to [数据库名] user [操作用户名] using [密码]

创建缓冲池(8K)：

  create bufferpool ibmdefault8k IMMEDIATE  SIZE 5000 PAGESIZE 8 K ;
创建缓冲池(16K)(OA_DIVERTASKRECORD)：
  create bufferpool ibmdefault16k IMMEDIATE  SIZE 5000 PAGESIZE 16 K ;
创建缓冲池(32K)(OA_TASK)：
  create bufferpool ibmdefault32k IMMEDIATE  SIZE 5000 PAGESIZE 32 K ;

创建表空间：

  CREATE TABLESPACE exoatbs IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8K MANAGED BY SYSTEM USING (/home/exoa2/exoacontainer) EXTENTSIZE 32 PREFETCHSIZE 16  BUFFERPOOL IBMDEFAULT8K  OVERHEAD 24.10 TRANSFERRATE 0.90  DROPPED TABLE RECOVERY OFF;

  CREATE TABLESPACE exoatbs16k  IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 16K MANAGED BY SYSTEM USING (/home/exoa2/exoacontainer16k   ) EXTENTSIZE 32  PREFETCHSIZE 16  BUFFERPOOL IBMDEFAULT16K  OVERHEAD 24.1 TRANSFERRATE 0.90  DROPPED TABLE RECOVERY OFF;

  CREATE TABLESPACE exoatbs32k  IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32K MANAGED BY SYSTEM USING (/home/exoa2/exoacontainer32k   ) EXTENTSIZE 32  PREFETCHSIZE 16  BUFFERPOOL IBMDEFAULT32K  OVERHEAD 24.1 TRANSFERRATE 0.90  DROPPED TABLE RECOVERY OFF;

GRANT USE OF TABLESPACE exoatbs TO PUBLIC;
GRANT USE OF TABLESPACE exoatbs16k TO PUBLIC;
GRANT USE OF TABLESPACE exoatbs32k TO PUBLIC;

创建系统表空间：

  CREATE TEMPORARY TABLESPACE exoasystmp IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 8K  MANAGED BY SYSTEM USING (/home/exoa2/exoasystmp   ) EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT8K  OVERHEAD 24.10 TRANSFERRATE 0.90  DROPPED TABLE RECOVERY OFF;

  CREATE TEMPORARY TABLESPACE exoasystmp16k IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 16K MANAGED BY SYSTEM USING (/home/exoa2/exoasystmp16k  ) EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT16K OVERHEAD 24.10 TRANSFERRATE 0.90  DROPPED TABLE RECOVERY OFF;

  CREATE TEMPORARY TABLESPACE exoasystmp32k IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 32K MANAGED BY SYSTEM USING (/home/exoa2/exoasystmp32k) EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT32K OVERHEAD 24.10 TRANSFERRATE 0.90  DROPPED TABLE RECOVERY OFF;

1. 启动实例(db2inst1):

db2start

2. 停止实例(db2inst1):

db2stop

3. 列出所有实例(db2inst1)

db2ilist

5.列出当前实例:

db2 get instance

4. 察看示例配置文件:

db2 get dbm cfg|more

5. 更新数据库管理器参数信息:

db2 update dbm cfg using para_name para_value

6. 创建数据库:

db2 create db test

7. 察看数据库配置参数信息

db2 get db cfg for test|more

8. 更新数据库参数配置信息

db2 update db cfg for test using para_name para_value

10.删除数据库:

db2 drop db test

11.连接数据库

db2 connect to test

12.列出所有表空间的详细信息。

db2 list tablespaces show detail

13.查询数据:

db2 select * from tb1

14.删除数据:

db2 delete from tb1 where id=1

15.创建索引:

db2 create index idx1 on tb1(id);

16.创建视图:

db2 create view view1 as select id from tb1

17.查询视图:

db2 select * from view1

18.节点编目

db2 catalog tcp node node_name remote server_ip server server_port

19.察看端口号

db2 get dbm cfg|grep SVCENAME

20.测试节点的附接

db2 attach to node_name

21.察看本地节点

db2 list node direcotry directory

22.节点反编目

db2 uncatalog node node_name

23.数据库编目

db2 catalog db db_name as db_alias at node node_name

24.察看数据库的编目

db2 list db directory

25.连接数据库

db2 connect to db_alias user user_name using user_password

26.数据库反编目

db2 uncatalog db db_alias

27.导出数据

db2 export to myfile of ixf messages msg select * from