DB2常用命令.docx

1、DB2常用命令导入数据db2 import from EBC_BILL_LIST_2007_7.ixf of ixf replace into EBC_BILL_LIST导出建库表的SQLdb2look -d ebank5 -a -e -x -o creatab.sql用db2move将数据备份出来db2move ebank5 export导入数据库所有的数据db2move ebank5 import将数据导入新库中db2move ebank5 load运行存储过程db2 call EB_UPDATE_STATESIGN(?) 其中？表示输入输出sql语句中终止符换成db2 tvf creat

2、edb.sql td解表db2 get snapshot for locks on ebilldb2 force application(上个语句等到的id)-连接数据库：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 ;创建缓冲池(

3、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 TRANSFERRA

4、TE 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 TA

5、BLESPACE 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 O

6、F 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 TR

7、ANSFERRATE 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 RECOVE

8、RY 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):db2start2. 停

9、止实例(db2inst1):db2stop3. 列出所有实例(db2inst1)db2ilist5.列出当前实例:db2 get instance4. 察看示例配置文件:db2 get dbm cfg|more5. 更新数据库管理器参数信息:db2 update dbm cfg using para_name para_value(最大连接数 MAX_CONNECTIONS、最大连接数值 MAX_COORDAGENTS)6. 创建数据库:db2 create db test7. 察看数据库配置参数信息db2 get db cfg for test|more8. 更新数据库参数配置信息db2 u

10、pdate db cfg for test using para_name para_value10.删除数据库:db2 drop db test11.连接数据库db2 connect to test12.列出所有表空间的详细信息。db2 list tablespaces show detail13.查询数据:db2 select * from tb114.删除数据:db2 delete from tb1 where id=115.创建索引:db2 create index idx1 on tb1(id);16.创建视图:db2 create view view1 as select id f

11、rom tb117.查询视图:db2 select * from view118.节点编目db2 catalog tcp node node_name remote server_ip server server_port19.察看端口号db2 get dbm cfg|grep SVCENAME20.测试节点的附接db2 attach to node_name21.察看本地节点db2 list node direcotry22.节点反编目db2 uncatalog node node_name23.数据库编目db2 catalog db db_name as db_alias at node

12、node_name24.察看数据库的编目db2 list db directory25.连接数据库db2 connect to db_alias user user_name using user_password26.数据库反编目db2 uncatalog db db_alias27.导出数据db2 export to myfile of ixf messages msg select * from tb128.导入数据db2 import from myfile of ixf messages msg replace into tb129.导出数据库的所有表数据db2move test e

13、xport30.生成数据库的定义db2look -d db_alias -a -e -m -l -x -f -o db2look.sql31.创建数据库db2 create db test132.生成定义(执行db2look.sql文件中的sql语句)db2 -tvf db2look.sql33.导入数据库所有的数据db2move db_alias import34.重组检查db2 reorgchk35.重组表tb1db2 reorg table tb136.更新统计信息db2 runstats on table tb137.备份数据库testdb2 backup db test38.恢复数据

14、库testdb2 restore db test399.列出容器的信息db2 list tablespace containers for tbs_id show detail40.创建表:db2 ceate table tb1(id integer not null,name char(10)41.列出所有表db2 list tables42.插入数据:db2 insert into tb1 values(1,sam);db2 insert into tb2 values(2,smitty);. 建立数据库DB2_GCBCREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCBUSING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 322. 连接数据库connect to sample1 user db2admin using 83012063. 建立别名create alias db2admin.tables fo