首页 开发笔记 数据库

# 信息查询

# 数据库版本查询

系统信息查询
1
2
SELECT *
FROM SUB_ENERGY.ODS_MEDIACORRESPONDENCE

# 存储过程列表

1
2
3
SELECT PROCSCHEMA, PROCNAME FROM SYSCAT.PROCEDURES
WHERE PROCSCHEMA IN ('SCHEMA_NAME1','SCHEMA_NAME2' )
ORDER BY PROCSCHEMA

# 表空间大小容量查询

SELECT substr (tbsp_name, 1, 20) AS TABLESPACE_NAME,
	substr (tbsp_content_type, 1, 10) AS TABLESPACE_TYPE,
	sum(tbsp_total_size_kb) / 1024 AS TOTAL_MB,
	sum(tbsp_used_size_kb) / 1024 AS USED_MB,
	sum(tbsp_free_size_kb) / 1024 AS FREE_MB,
	tbsp_page_size AS PAGE_SIZE
FROM SYSIBMADM.TBSP_UTILIZATION
GROUP BY tbsp_name, tbsp_content_type, tbsp_page_size
ORDER BY 1

# 数据结构修改

# 修改数据类型

1
ALTER TABLE schema_name.table_name ALTER column_name SET DATA TYPE DECIMAL(19, 4);

# 数据分区重组

1
CALL sysproc.admin_cmd('reorg table SchemaName.TableName');

# 修改数据库约束

1
2
3
4
ALTER TABLE schema_name.table_name ALTER MODULE_TYPE SET NOT NULL;
REORG TABLE schema_name.table_name;
ALTER TABLE schema_name.table_name DROP PRIMARY KEY;
ALTER TABLE schema_name.table_name ADD PRIMARY KEY(SRC_SYSTEM_ID, DES_SYSTEM_ID, SRC_OBJECT_NAME, DES_OBJECT_NAME, MODULE_TYPE);

# 字段类型、长度、精度修改

1
ALTER TABLE schema_name.table_name ALTER column_name SET DATA TYPE DECIMAL(19, 4);

# 表解锁语句（长度、精度修改易导致锁表，需要重新组织数据）

1
CALL sysproc.admin_cmd('REORG TABLE schema_name.table_name');

# 日期运算

# 日期间隔天数

1
2
SELECT DAYS(TO_DATE('20221028','YYYYMMDD')) - DAYS(TO_DATE('20220929','YYYYMMDD')) AS D_TIME 
FROM SYSIBM.DUAL;

# UNIX 时间戳转换数据库时间戳

1
TIMESTAMP('1970-01-01 00:00:00.000') + (1680150600036 / 1000.0) SECONDS;

# 查询上个月 (日期字段精度为年月)

1
2
3
4
5
SELECT YEARMONTH_COLUMN FROM TABLENAME
WHERE  TABLENAME.YEARMONTH_COLUMN = (
	SELECT YEAR (TO_DATE ('202204' + '01', 'YYYYMMDD') - 1 MONTH)|| RIGHT (100+ MONTH (TO_DATE ('202204' || '01', 'YYYYMMDD') - 1 MONTH),2)
	FROM SYSIBM.DUAL
);

# 授权管理

# 数据表授权

1
GRANT ALTER, DELETE, INDEX, INSERT, SELECT, REFERENCES, UPDATE ON TABLE schema_name.table_name TO USER user_name WITH GRANT OPTION;

# 函数授权

1
GRANT EXECUTE ON SPECIFIC FUNCTION schema_name.function_name TO USER user_name WITH GRANT OPTION;

# 存储过程授权

1
GRANT EXECUTE ON PROCEDURE schema_name.procedure_name TO USER user_name WITH GRANT OPTION;

# 授权用户取消被授权用户的表级操作授权，及被授权用户 user_name 给其他用户关联授予的相应权限

1
REVOKE SELECT ON schema_name.table_name FROM user_name

# 授权用户取消被授权用户的表级操作授权，及被授权用户 user_name 给其他用户关联授予的相应权限

1
REVOKE SELECT ON schema_name.table_name FROM user_name CASCADE

更新于 阅读次数 次