DB2数据库中的相关命令-3

  来源：

---

 

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. 信息帮助 (? XXXnnnnn )

例:? SQL30081

39. SQL 帮助(说明 SQL 语句的语法)

help statement

例如，help SELECT

40. SQLSTATE 帮助(说明 SQL 的状态和类别代码)

? sqlstate 或 ? class-code

41. 更改与"管理服务器"相关的口令

db2admin setid username password

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

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

(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');