db2应用经验《五》.txt

db2 指定某个字符串中的第几个字符的更新

DB2应用经验《五》
//表或视图特权 
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 


//检索具有特权的所有授权名 
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');