--消耗用户CPU时间最多的动态SQL
db2 "select * from sysibmadm.snapdyn_sql
order by total_usr_cpu_time desc
-
-查看锁表
select * from sysibmadm.locks_held WHERE TABNAME!='PRPLRIBNR' with ur;
--查看正在运行的执行脚本
select * from sysibmadm.long_running_sql WHERE ELAPSED_TIME_MIN IS NOT NULL
order by elapsed_time_min desc
/**查看锁表状态**/
select * from sysibmadm.locks_held with ur;
/**解锁表**/
CALL SYSPROC.ADMIN_CMD('force application(6750)');
/**统计表的数据量**/
select tabname,card from syscat.tables where tabname like 'IFRS%' ORDER BY tabname;
/**查看列为指定信息的信息**/
select * from SYSIBM.SYSCOLUMNS where TBCREATOR='DB2INST1' AND NAME LIKE '%COM%NAME' ;
select * from SYSIBM.SYSCOLUMNS where TBCREATOR='DB2INST1' and NAME not LIKE '%COM%NAME'and (HIGH2KEY like '%***%' or low2key like '%****%' );
/**通过报错信息定位表和字段**/
SELECT C.TABSCHEMA,
C.TABNAME, C.COLNAME FROM SYSCAT.TABLES AS T,
SYSCAT.COLUMNS AS C WHERE
C.TABSCHEMA = T.TABSCHEMA AND C.TABNAME = T.TABNAME AND
T.TBSPACEID = 3 AND T.TABLEID = 1208 AND C.COLNO = 2;
/*查看表空间占用情况*/
select
substr(tbsp_name,1,20) as 表空间名称,
substr(tbsp_content_type,1,10) as 表空间类型,
sum(tbsp_total_size_kb)/1024/1024 as 表空间总大小(G),
sum(tbsp_used_size_kb)/1024/1024 as 已经使用的表空间大小(G),
sum(tbsp_free_size_kb)/1024/1024 as 剩余表空间大小(G),
tbsp_page_size AS 页大小
from SYSIBMADM.TBSP_UTILIZATION
group by
tbsp_name,
tbsp_content_type,
tbsp_page_size
order by 3 desc
with ur;
表: select * from syscat.systables where Type = 'T'
用户: select * from syscat.dbauth ;
权限: select * from syscat.tabauth;
/
*表空间授权用户*/
select * from syscat.TBSPACEAUTH with ur;
/*表空间数据文件*/
select * from sysibmadm.CONTAINER_UTILIZATION with ur;