# 信息查询# 数据库版本查询系统信息查询 SELECT * FROM SUB_ENERGY. ODS_MEDIACORRESPONDENCE
# 存储过程列表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
# 数据结构修改# 修改数据类型ALTER TABLE schema_name.table_name ALTER column_name SET DATA TYPE DECIMAL( 19 , 4 ) ;
# 数据分区重组CALL sysproc.admin_cmd( 'reorg table SchemaName.TableName' ) ;
# 修改数据库约束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) ;
# 字段类型、长度、精度修改ALTER TABLE schema_name.table_name ALTER column_name SET DATA TYPE DECIMAL( 19 , 4 ) ;
# 表解锁语句（长度、精度修改易导致锁表，需要重新组织数据）CALL sysproc.admin_cmd( 'REORG TABLE schema_name.table_name' ) ;
# 日期运算# 日期间隔天数SELECT DAYS(TO_DATE('20221028','YYYYMMDD')) - DAYS(TO_DATE('20220929','YYYYMMDD')) AS D_TIME
FROM SYSIBM.DUAL;
# UNIX 时间戳转换数据库时间戳TIMESTAMP( '1970-01-01 00:00:00.000' ) + ( 1680150600036 / 1000.0 ) SECONDS ;
# 查询上个月 (日期字段精度为年月)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 ) ;
# 授权管理# 数据表授权GRANT ALTER, DELETE, INDEX, INSERT, SELECT, REFERENCES, UPDATE ON TABLE schema_name.table_name TO USER user_name WITH GRANT OPTION;
# 函数授权GRANT EXECUTE ON SPECIFIC FUNCTION schema_name.function_name TO USER user_name WITH GRANT OPTION;
# 存储过程授权GRANT EXECUTE ON PROCEDURE schema_name.procedure_name TO USER user_name WITH GRANT OPTION;
# 授权用户取消被授权用户的表级操作授权，及被授权用户 user_name 给其他用户关联授予的相应权限REVOKE SELECT ON schema_name.table_name FROM user_name
# 授权用户取消被授权用户的表级操作授权，及被授权用户 user_name 给其他用户关联授予的相应权限REVOKE SELECT ON schema_name.table_name FROM user_name CASCADE
