db2学习指令（三）
41.列出所有表
db2 list tables
42.插入数据:
db2 insert into tb1 values(1,’sam’);
db2 insert into tb2 values(2,’smitty’);
www.zzzyk.com
42. 创建 SAMPLE 数据库
db2sampl
db2sampl F:(指定安装盘)
43. 使用操作系统命令
! dir
44. 转换数据类型 (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
www.zzzyk.com
46. 检查 DB2 数据库管理程序配置
db2 get dbm cfg
47. 检索具有特权的所有授权名
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
www.zzzyk.com
(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 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
www.zzzyk.com
('user01','业务申请'),
('user02','业务撤消'),
('user03','费用查询'),
('user04','费用自缴'),
('user05','费用预存'),
('user06','密码修改'),
('user07','发票打印'),
('gl01','改用户基本信息'),
('gl02','更改支付信息'),
('gl03','日统计功能'),
('gl04','冲帐功能'),
('gl05','对帐功能'),
('gl06','计费功能'),
('gl07','综合统计')
备份数据库：
CONNECT TO EXOA;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET; www.zzzyk.com
BACKUP DATABASE EXOA TO "/home/exoa2/db2bak/" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
CONNECT TO EXOA;
UNQUIESCE DATABASE;
CONNECT RESET;
作者 manULinux
