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);
//查看表
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'
//要运行 DB2 Java 存储过程或 UDF，还需要更新服务器上的 DB2 数据库管理程序配置，以包括在该机器上安装 JDK 的路径
db2 update dbm cfg using JDK11_PATH d:\sqllib\java\jdk
TERMINATE
update dbm cfg using SPM_NAME sample
//检查 DB2 数据库管理程序配置
db2 get dbm cfg
//检索具有特权的所有授权名