DB2数据库常用脚本

--消耗用户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;