一、常用命令
1. 建立数据库DB2_GCB
CREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCB
USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 32
2. 连接数据库
connect to sample1 user db2admin using 8301206
3. 建立别名
create alias db2admin.tables for sysstat.tables;
CREATE ALIAS DB2ADMIN.VIEWS FOR SYSCAT.VIEWS
create 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 views) 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_del
AFTER DELETE ON zjt_tables
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
Insert into zjt_tables1 values(substr(o.tabschema,1,8),substr(o.tabname,1, 10))
8. 建立唯一性索引
CREATE UNIQUE INDEX I_ztables_tabname
ON zjt_tables(tabname);
9. 查看表
select tabname from tables
where tabname='ZJT_TABLES';
10. 查看列
select SUBSTR(COLNAME,1,20) as 列名,TYPENAME
as 类型,LENGTH as 长度
from columns
where tabname='ZJT_TABLES';
11. 查看表结构
db2 describe
table user1.department
db2 describe
select * from user.tables
12. 查看表的索引
db2 describe
indexes for table user1.department
13. 查看视图
select viewname from views
where viewname='V_ZJT_TABLES';
14. 查看索引
select indname from indexes
where indname='I_ZTABLES_TABNAME';
15. 查看存贮过程
SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15)
FROM SYSCAT.PROCEDURES;
16. 类型转换(cast)
select cast(ip as integer)+50 from log_comm_failed
17. 重新连接
connect reset
18. 中断数据库连接
disconnect db2_gcb
19. 查看应用的连接。
LIST APPLICATION show detail;
20. 杀掉应用的连接
db2 force
application (进程号,进程号....)
FORCE APPLICATION(0);
db2 force
applications all (强迫所有应用程序从数据库断开)
21. 锁表
lock table test in exclusive mode
22. 共享
lock table test in share mode
23. 显示当前用户所有表
list tables
24. 列出所有的系统表
list tables for system
25. 显示当前活动数据库
list active databases
26. 查看命令选项
list command options
27. 系统数据库目录
LIST DATABASE DIRECTORY
28. 表空间
list tablespaces
29. 表空间容器
LIST TABLESPACE CONTAINERS FOR
Example: LIST TABLESPACE CONTAINERS FOR 1
30. 显示用户数据库的存取权限
GET AUTHORIZATIONS
31. 启动实例
DB2START
32. 停止实例
db2stop
33. 表或视图特权
grant select,delete,insert,update on tables to user
grant all on tables to user WITH GRANT OPTION
34. 程序包特权
GRANT EXECUTE ON PACKAGE PACKAGE-name TO PUBLIC
35. 模式特权
GRANT CREATEIN ON SCHEMA SCHEMA-name TO USER
36. 数据库特权
grant connect,createtab,dbadm on database to user
37. 索引特权
grant control on index index-name to user
38. 转换数据类型 (cast)
SELECT EMPNO, CAST(RESUME AS VARCHAR(370))
FROM EMP_RESUME
WHERE RESUME_FORMAT = 'ascii'
39. 要运行 DB2 Java
存储过程或 UDF，还需要更新服务器上的
DB2 数据库管理程序配置，以包括在该机器上安装
JDK 的路径
db2 update
dbm cfg using JDK11_PATH d:\sqllib\java\jdk
TERMINATE
update dbm cfg using SPM_NAME sample
40. 检查 DB2 数据库管理程序配置
db2 get
dbm cfg
41. 修改表结构
alter table yhdab ALTER kh SET DATA TYPE varchar(13);
alter table yhdab ALTER ID SET DATA TYPE varchar(13);
alter table lst_bsi alter bsi_money set data type int;
42.查看DB2内存分配使用情况
db2mtrk -i -d -p -v 查看DB2内存分配和使用情况
查看DB2内存分配和使用情况
43.编译存储过程
db2 -td@
-vf file
44.DB2move 转移数据库组件db2move intedb export -u db2admin -p db2admin
导出db2move app1 export
导入db2move app2 load
IMPORT FROM "G:\tbl_income.ixf" OF IXF MESSAGES "G:\tbl_income1.msg" INSERT INTO DB2ADMIN.TBL_INCOME_PAR_TEST;
45.执行SQL脚本
db2 -tvf
1.sql
46.将100.1.16.16的intedb下载到本地
DB2 catalog
tcpip node node16 remote 100.1.16.16 server 50000
DB2 catalog db intedb
at node node16 ---intedb是要链接的数据库名
DB2 catalog db intedb
as intedb42 at node node16 ---intedb是要链接的数据库名 intedb42是别名
--快照监控执行47--49
47.连接节点
DB2 attach
to node15 user db2admin (node15是节点名)
48.监视器
db2 update
monitor switches using table on
49.快照监视器
DB2 get
snapshot for tables on anly (anly--数据库名)
50.重新编译完存储过程后
DB2 terminate
51.DB2设置
DB2set -all
52.索引重建
db2 reorg
indexs all for table table_name
53.备份数据库
db2 force
application all
db2 backup db intedb
to /intedb_log
54.恢复
db2 restore db gxk
from d: to d:
55.删除数据库（drop）
杀掉所有连接db2 list
application for database anly show detail、db2 force
application ( , )
db2 deactivate db anly
db2 drop db anly
删除相关的文件系统、设备
56.显示数据库设置
db2 get db cfg
for o_yd
db2 get
dbm cfg
57.察看版本
db2level
58.DELETE不释放数据库表空间
C:\Documents and Settings\user>db2 reorg
table tbl_reference_tmp use tbspinte
SQL2213N 指定的表空间不是系统临时表空间。
C:\Documents and Settings\user>db2 reorg
table tbl_reference_tmp use tbtemp32k
DB20000I REORG 命令成功完成。
其中tbl_reference_tmp在表空间tbsptemp上，而tbtemp32k是系统临时表空间（由于表空间tbsptemp已满，所以使用tbtemp32k进行重组）
59.不带日志清除表
alter table tbl_income_tmp ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE ;
60.建立别名
create alias db2admin.tables for sysstat.tables;
CREATE ALIAS DB2ADMIN.VIEWS FOR SYSCAT.VIEWS
create alias db2admin.columns for syscat.columns;
create alias guest.columns for syscat.columns;
61.导入数据
db2 LOAD
FROM D:\a.TXT OF DEL TERMINATE INTO TBL_REFERENCE_rpt
62.加载节点
db2 attach
to nodename user db2admin using db2admin
63.表快照
db2 update
monitor switches using table on
db2 get
snapshot for tables on bidb
64．写数据库管理程序配置
db2 update
dbm cfg using 参数名 参数值
65．写数据库的配置
db2 connect
to o_yd user db2 using pwd
db2 update db cfg
for o_yd using 参数名 参数值
66.备份数据库
db2 force
application all
db2 backup db o_yd
to d:
(db2 initialize
tape on \\.\tape0)
(db2 rewind
tape on \\.\tape0)
db2 backup db o_yd
to \\.\tape0
67.恢复数据库
db2 restore db o_yd
from d: to d:
db2 restore db o_yd
from \\.\tape0 to d:
68．绑定存储过程
db2 connect
to o_yd user db2 using pwd
db2 bind
c:\dfplus.bnd
拷贝存储过程到服务器上的C:\sqllib\function目录中
69.整理表
db2 connect
to o_yd user db2 using pwd
db2 reorg
table ydd
db2 runstats
on table ydd with distribution and indexes all
70.导出表数据
db2 export
to c:\dftz.txt of del select * from dftz
db2 export
to c:\dftz.ixf of ixf select * from dftz
71.导入表数据
import from c:\123.txt of del insert into ylbx.czyxx
db2 import
to c:\dftz.txt of del commitcount 5000 messages c:\dftz.msg insert into dftz
db2 import
to c:\dftz.ixf of ixf commitcount 5000 messages c:\dftz.msg insert into dftz
db2 import
to c:\dftz.ixf of ixf commitcount 5000 insert into dftz
db2 import
to c:\dftz.ixf of ixf commitcount 5000 insert_update into dftz
db2 import
to c:\dftz.ixf of ixf commitcount 5000 replace into dftz
db2 import
to c:\dftz.ixf of ixf commitcount 5000 create into dftz (仅IXF)
db2 import
to c:\dftz.ixf of ixf commitcount 5000 replace_create into dftz (仅IXF)
72.不记日志删除
truncate table table_name