DB2常用命令列表
- 建立数据库DB2_GCB
CREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCB USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 32
- 连接数据库
connect to sample1 user db2admin using 8301206
- 建立别名
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;
- 建立表
create table zjt_tables as (select * from tables) definition only; create table zjt_views as (select * from views) definition only;
- 插入记录
insert into zjt_tables select * from tables; insert into zjt_views select * from views;
- 建立视图
create view V_zjt_tables as select tabschema,tabname from zjt_tables;
- 建立触发器
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))
- 建立唯一性索引
CREATE UNIQUE INDEX I_ztables_tabname ON zjt_tables(tabname); 9. 查看表
select tabname from tables where tabname='ZJT_TABLES';
- 查看列
select SUBSTR(COLNAME,1,20) as 列名,TYPENAME as 类型,LENGTH as 长度 from columns where tabname='ZJT_TABLES';
- 查看表结构
db2 describe table user1.department db2 describe select * from user.tables
- 查看表的索引
db2 describe indexes for table user1.department
- 查看视图
select viewname from views where viewname='V_ZJT_TABLES';
- 查看索引
select indname from indexes where indname='I_ZTABLES_TABNAME';
- 查看存贮过程
SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15) FROM SYSCAT.PROCEDURES;
- 类型转换(cast)
ip datatype:varchar select cast(ip as integer)+50 from log_comm_failed
- 重新连接
connect reset
- 中断数据库连接
disconnect db2_gcb
- view application
LIST APPLICATION;
- kill application
FORCE APPLICATION(0); db2 force applications all (强迫所有应用程序从数据库断开)
- lock table
lock table test in exclusive mode
- 共享
lock table test in share mode
- 显示当前用户所有表
list tables
- 列出所有的系统表
list tables for system
- 显示当前活动数据库
list active databases
- 查看命令选项
list command options
- 系统数据库目录
LIST DATABASE DIRECTORY
- 表空间
list tablespaces
- 表空间容器
LIST TABLESPACE CONTAINERS FOR Example: LIST TABLESPACE CONTAINERS FOR 1
- 显示用户数据库的存取权限
GET AUTHORIZATIONS
- 启动实例
DB2START
- 停止实例
db2stop
- 表或视图特权
grant select,delete,insert,update on tables to user grant all on tables to user WITH GRANT OPTION
- 程序包特权
GRANT EXECUTE ON PACKAGE PACKAGE-name TO PUBLIC
- 模式特权
GRANT CREATEIN ON SCHEMA SCHEMA-name TO USER
- 数据库特权
grant connect,createtab,dbadm on database to user
- 索引特权
grant control on index index-name to user
- 信息帮助 (? XXXnnnnn )
例:? SQL30081
- SQL 帮助(说明 SQL 语句的语法)
help statement 例如,help SELECT
- SQLSTATE 帮助(说明 SQL 的状态和类别代码)
? sqlstate 或 ? class-code
- 更改与"管理服务器"相关的口令
db2admin setid username password
- 创建 SAMPLE 数据库
db2sampl db2sampl F:(指定安装盘)
- 使用操作系统命令
! dir
- 转换数据类型 (cast)
SELECT EMPNO, CAST(RESUME AS VARCHAR(370)) FROM EMP_RESUME WHERE RESUME_FORMAT = 'ascii' 45. UDF
要运行 DB2 Java 存储过程或 UDF,还需要更新服务器上的 DB2 数据库管理程序配置,以包括在该机器上安装 JDK 的路径
db2 update dbm cfg using JDK11_PATH d:sqllibjavajdk TERMINATE update dbm cfg using SPM_NAME sample
- 检查 DB2 数据库管理程序配置
db2 get dbm cfg
- 检索具有特权的所有授权名
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE ' FROM SYSCAT.TABAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE ' FROM SYSCAT.PACKAGEAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX ' FROM SYSCAT.INDEXAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN ' FROM SYSCAT.COLAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA ' FROM SYSCAT.SCHEMAAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SERVER ' FROM SYSCAT.PASSTHRUAUTH ORDER BY GRANTEE, GRANTEETYPE, 3
create table yhdab (id varchar(10), password varchar(10), ywlx varchar(10), kh varchar(10)); create table ywlbb (ywlbbh varchar(8), ywmc varchar(60))
- 修改表结构
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; insert into yhdab values ('20000300001','123456','user01','20000300001'), ('20000300002','123456','user02','20000300002');
- 业务类型说明
insert into ywlbb values ('user01','业务申请'), ('user02','业务撤消'), ('user03','费用查询'), ('user04','费用自缴'), ('user05','费用预存'), ('user06','密码修改'), ('user07','发票打印'), ('gl01','改用户基本信息'), ('gl02','更改支付信息'), ('gl03','日统计功能'), ('gl04','冲帐功能'), ('gl05','对帐功能'), ('gl06','计费功能'), ('gl07','综合统计')