db2常用命令.docx

1、db2常用命令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

2、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

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

4、GESIZE 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 exoatbs3

5、2k 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 TABLES

6、PACE 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 PARTI

7、TION 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. 停止实例(db2inst1):db2stop3. 列出所有实例(db2inst1)db2ilist5.列出当前实例:db2 get i

8、nstance4. 察看示例配置文件:db2 get dbm cfg|more5. 更新数据库管理器参数信息:db2 update dbm cfg using para_name para_value6. 创建数据库:db2 create db test7. 察看数据库配置参数信息db2 get db cfg for test|more8. 更新数据库参数配置信息db2 update db cfg for test using para_name para_value10.删除数据库:db2 drop db test11.连接数据库db2 connect to test12.列出所有表空间的详

9、细信息。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 from tb117.查询视图:db2 select * from view118.节点编目db2 catalog tcp node node_name remote server_ip server server_port1

10、9.察看端口号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 node_name24.察看数据库的编目db2 list db directory25.连接数据库db2 connect to db_alias user user_name using user_password26.数据

11、库反编目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 export30.生成数据库的定义db2look -d db_alias -a -e -m -l -x -f -o db2look.sql31.创建数据库db2 create db test132.生成定义db2 -tvf d

12、b2look.sql33.导入数据库所有的数据db2move db_alias import34.重组检查db2 reorgchk35.重组表tb1db2 reorg table tb136.更新统计信息db2 runstats on table tb137.备份数据库testdb2 backup db test38.恢复数据库testdb2 restore db test399.列出容器的信息db2 list tablespace containers for tbs_id show detail40.创建表:db2 ceate table tb1(id integer not null,n

13、ame 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. 建立别名

14、create alias db2admin.tables for sysstat.tables;CREATE ALIAS DB2ADMIN.VIEWS FOR SYSCAT.VIEWScreate alias db2admin.columns for syscat.columns;create alias guest.columns for syscat.columns;4. 建立表create table zjt_tables as(select * from tables) definition only;create table zjt_views as(select * from vi

15、ews) definition only;5. 插入记录insert into zjt_tables select * from tables;insert into zjt_views select * from views;6. 建立视图create view V_zjt_tables as select tabschema,tabname from zjt_tables;7. 建立触发器CREATE TRIGGER zjt_tables_delAFTER DELETE ON zjt_tablesREFERENCING OLD AS OFOR EACH ROW MODE DB2SQLIns

16、ert into zjt_tables1 values(substr(o.tabschema,1,8),substr(o.tabname,1,10)8. 建立唯一性索引CREATE UNIQUE INDEX I_ztables_tabnamesize=3ON zjt_tables(tabname);9. 查看表select tabname from tableswhere tabname=ZJT_TABLES;10. 查看列select SUBSTR(COLNAME,1,20) as 列名,TYPENAME as 类型,LENGTH as 长度from columnswhere tabname

17、=ZJT_TABLES;11. 查看表结构db2 describe table user1.departmentdb2 describe select * from user.tables12. 查看表的索引db2 describe indexes for table user1.department13. 查看视图select viewname from viewswhere viewname=V_ZJT_TABLES;14. 查看索引select indname from indexeswhere indname=I_ZTABLES_TABNAME;15. 查看存贮过程SELECT SUB

18、STR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15)FROM SYSCAT.PROCEDURES;16. 类型转换(cast)ip datatype:varcharselect cast(ip as integer)+50 from log_comm_failed17. 重新连接connect reset18. 中断数据库连接disconnect db2_gcb19. view applicationLIST APPLICATION;20. kill applicationFORCE APPLICATION(0);db2 force applications a

19、ll (强迫所有应用程序从数据库断开)21. lock tablelock table test in exclusive mode22. 共享lock table test in share mode23. 显示当前用户所有表list tables24. 列出所有的系统表list tables for system25. 显示当前活动数据库list active databases26. 查看命令选项list command options27. 系统数据库目录LIST DATABASE DIRECTORY28. 表空间list tablespaces29. 表空间容器LIST TABLES

20、PACE CONTAINERS FORExample: LIST TABLESPACE CONTAINERS FOR 130. 显示用户数据库的存取权限GET AUTHORIZATIONS31. 启动实例DB2START32. 停止实例db2stop33. 表或视图特权grant select,delete,insert,update on tables to usergrant all on tables to user WITH GRANT OPTION34. 程序包特权GRANT EXECUTEON PACKAGE PACKAGE-nameTO PUBLIC35. 模式特权GRANT C

21、REATEIN ON SCHEMA SCHEMA-name TO USER36. 数据库特权grant connect,createtab,dbadm on database to user37. 索引特权grant control on index index-name to user38. 信息帮助 (? XXXnnnnn )例:? SQL3008139. SQL 帮助(说明 SQL 语句的语法)help statement例如，help SELECT40. SQLSTATE 帮助(说明 SQL 的状态和类别代码)? sqlstate 或 ? class-code41. 更改与管理服务器相

22、关的口令db2admin setid username password42. 创建 SAMPLE 数据库db2sampldb2sampl F:(指定安装盘)43. 使用操作系统命令! dir44. 转换数据类型 (cast)SELECT EMPNO, CAST(RESUME AS VARCHAR(370)FROM EMP_RESUMEWHERE RESUME_FORMAT = ascii45. UDF要运行 DB2 Java 存储过程或 UDF，还需要更新服务器上的 DB2 数据库管理程序配置，以包括在该机器上安装 JDK 的路径db2 update dbm cfg using JDK11_

23、PATH d:sqllibjavajdkTERMINATEupdate dbm cfg using SPM_NAME sample46. 检查 DB2 数据库管理程序配置db2 get dbm cfg47. 检索具有特权的所有授权名SELECT DISTINCT GRANTEE, GRANTEETYPE, DATABASE FROM SYSCAT.DBAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, TABLE FROM SYSCAT.TABAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, PACKAGE

24、FROM SYSCAT.PACKAGEAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, INDEX FROM SYSCAT.INDEXAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, COLUMN FROM SYSCAT.COLAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, SCHEMA FROM SYSCAT.SCHEMAAUTHUNIONSELECT DISTINCT GRANTEE, GRANTEETYPE, SERVER FROM SYSCAT.PASS

25、THRUAUTHORDER BY GRANTEE, GRANTEETYPE, 3create table yhdab(id varchar(10),password varchar(10),ywlx varchar(10),kh varchar(10);create table ywlbb(ywlbbh varchar(8),ywmc varchar(60)48. 修改表结构alter table yhdab ALTER kh SET DATA TYPE varchar(13);alter table yhdab ALTER ID SET DATA TYPE varchar(13);alter

26、 table lst_bsi alter bsi_money set data type int;insert into yhdab values(20000300001,123456,user01,20000300001),(20000300002,123456,user02,20000300002);49. 业务类型说明insert into ywlbb values(user01,业务申请),(user02,业务撤消),(user03,费用查询),(user04,费用自缴),(user05,费用预存),(user06,密码修改),(user07,发票打印),(gl01,改用户基本信息),

27、(gl02,更改支付信息),(gl03,日统计功能),(gl04,冲帐功能),(gl05,对帐功能),(gl06,计费功能),(gl07,综合统计)备份数据库：CONNECT TO EXOA;QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;CONNECT RESET;BACKUP DATABASE EXOA TO /home/exoa2/db2bak/ WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;CONNECT TO EXOA;UNQUIESCE DATABASE;CONNECT

28、 RESET;以下是小弟在使用db2move中的一些经验，希望对大家有所帮助。db2 connect to YOURDB 连接数据库db2look -d YOURDB -a -e -x -o creatab.sql导出建库表的SQLdb2move YOURDB export用db2move将数据备份出来vi creatab.sql如要导入的数据库名与原数据库不同，要修改creatab.sql中CONNECT 项如相同则不用更改db2move NEWDB load将数据导入新库中在导入中可能因为种种原因发生中断，会使数据库暂挂db2 list tablespaces show detail如：详细说明：装入暂挂总页数 = 1652可用页数 = 1652已用页数 = 1652空闲页数 = 不适用高水位标记（页） = 不适用页大小（字节） = 4096盘区大小（页） = 32预读取大小（页） = 32容器数 = 1状态更改表空间标识 = 2状态更改对象标识 = 59db2 select tabname,tableid from syscat.tables where tableid=59查看是哪张表挂起表名知道后到db2move.lst(在db2move YOURDB export的目录中)中找到相应的.ixf文件db2 load from