DB2日常表，空间管理等部分SQL语句

--锁字典
SELECT *
FROM SYSIBMADM.SNAPLOCK a
WHERE 1=1
and a.TABNAME LIKE '%'
AND a.TABNAME IS NOT NULL
;

--存储过程字典
SELECT a.PROCSCHEMA,a.PROCNAME,a.VALID,a.REMARKS
FROM syscat.PROCEDURES a
WHERE 1=1 AND a.TEXT LIKE ('%MSACKD%')

--包
SELECT *
FROM SYSCAT.PACKAGES b
WHERE 1=1 AND b.PKGNAME='P7190341'
AND b.BOUNDBY='ZXJX'
FETCH FIRST 100 ROWS ONLY ;

--表信息字典
SELECT a.TABNAME,a.REMARKS,a.CREATE_TIME,a.ALTER_TIME,a.INVALIDATE_TIME,a.STATS_TIME
FROM SYSCAT.TABLES a
WHERE 1=1 AND a.tabname LIKE '%BAK%'
ORDER BY 1
WITH UR;

--视图字典
SELECT *
FROM SYSCAT.VIEWS a
WHERE a.VIEWNAME='V_JX_ZDJLMX';

--查看表结构
SELECT a.TABNAME,a.colname
,CASE WHEN a.TYPESCHEMA='SYSIBM' THEN '' ELSE A.TYPESCHEMA END
||' '||a.TYPENAME||CASE WHEN a.TYPENAME IN ('INTEGER','LONG','DATE','TIME') THEN ''
       WHEN a.TYPENAME LIKE '%CHAR%' THEN '('||VARCHAR(a.LENGTH)||')'
       ELSE '('||VARCHAR(a.LENGTH)||','||VARCHAR(a.SCALE)||')' END TYPE
,a.LENGTH,a.SCALE
,a.DEFAULT,a.NULLS
,a.REMARKS
FROM SYSCAT.COLUMNS a
WHERE a.TABSCHEMA='ZXJX' --
AND a.tabname LIKE 'PLAT_ACCCDEPSTC'
--'DS_F_NI_B2C_B2C_PAYRST_ALL'
--AND a.COLNAME LIKE 'PLAT_WCDEPSTC'
ORDER BY a.COLNO;

--例程(过程或函数)依赖
SELECT *
FROM syscat.ROUTINES a LEFT JOIN SYSCAT.ROUTINEDEP b
ON a.SPECIFICNAME=b.SPECIFICNAME
WHERE a.ROUTINENAME='PC_ZLKH'
FETCH FIRST 10 ROWS ONLY

--查看表及使用人等信息
SELECT a.LOCK_MODE,a.TABNAME,a.TABSCHEMA,a.TBSP_NAME
,b.CLIENT_PID,b.COORD_NODE_NUM,b.CLIENT_PLATFORM,b.CLIENT_PROTOCOL,b.TERRITORY_CODE,b.APPL_NAME
,b.APPL_ID,b.CLIENT_NNAME,b.CLIENT_PRDID,b.INPUT_DB_ALIAS,b.CLIENT_DB_ALIAS,b.CORR_TOKEN
,c.DB_NAME,c.AGENT_ID,c.INBOUND_COMM_ADDRESS,c.LOCK_TIMEOUT_VAL,c.ROWS_READ,c.ROWS_WRITTEN
FROM SYSIBMADM.SNAPLOCK a LEFT JOIN SYSIBMADM.SNAPAPPL_INFO b
ON a.AGENT_ID=b.AGENT_ID
LEFT JOIN SYSIBMADM.SNAPAPPL c
ON a.AGENT_ID=c.AGENT_ID ;

--表空间
SELECT a.*,b.BPNAME,b.NPAGES,b.PAGESIZE
FROM SYSCAT.TABLESPACES a JOIN SYSCAT.BUFFERPOOLS b
ON a.BUFFERPOOLID=b.BUFFERPOOLID
WHERE a.TBSPACE='ZXJXPTSPACE2010_3';

--清空表
ALTER TABLE LOG_TABLE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;

--查看数据库表空间中使用情况
SELECT a.TBSPACE,a.OWNER,a.OWNERTYPE
,(b.TBSP_USED_PAGES/(b.TBSP_USED_PAGES+b.TBSP_FREE_PAGES+b.TBSP_PENDING_FREE_PAGES+0.000000001)*1.0000) USEDBL
,a.CREATE_TIME,a.TBSPACEID,a.TBSPACETYPE,a.DATATYPE
,round((b.TBSP_TOTAL_PAGES+b.TBSP_PENDING_FREE_PAGES)*c.PAGESIZE*1.00/1024/1024,2) TBSP_TOTAL_PAGES
,(b.TBSP_USABLE_PAGES+b.TBSP_PENDING_FREE_PAGES)*c.PAGESIZE*1.00/1024/1024 TBSP_USABLE_PAGES
,b.TBSP_USED_PAGES*c.PAGESIZE*1.00/1024/1024 TBSP_USED_PAGES
,(b.TBSP_FREE_PAGES+b.TBSP_PENDING_FREE_PAGES)*c.PAGESIZE*1.00/1024/1024 TBSP_FREE_PAGES
,b.TBSP_PENDING_FREE_PAGES*c.PAGESIZE*1.00/1024/1024 TBSP_PENDING_FREE_PAGES
,b.TBSP_PAGE_TOP
,b.TBSP_NUM_RANGES,b.TBSP_NUM_CONTAINERS
,b.TBSP_INITIAL_SIZE TBSP_INITIAL_SIZE
,b.TBSP_CURRENT_SIZE*1.00/1024/1024  TBSP_CURRENT_SIZE
,b.TBSP_TOTAL_PAGES
FROM SYSCAT.TABLESPACES a JOIN SYSIBMADM.SNAPTBSP_PART b
ON a.TBSPACEID=b.TBSP_ID
JOIN SYSCAT.BUFFERPOOLS c
ON a.BUFFERPOOLID=c.BUFFERPOOLID
WHERE a.DATATYPE NOT IN ('T') AND a.OWNERTYPE NOT IN ('S')
ORDER BY USEDBL DESC;

--表空间中有哪些表及占用空间
SELECT DISTINCT b.TBSPACE--,a1.DATAPARTITIONNAME
,a.TABSCHEMA,a.TABNAME,a.TYPE,a.STATUS,a.CARD
,a.NPAGES*b.PAGESIZE*1.00/1024/1024 USEDSIZE_M,a.FPAGES*b.PAGESIZE*1.00/1024/1024 SETSIZE_M
,a.REMARKS,a.CREATE_TIME,a.ALTER_TIME,a.INVALIDATE_TIME,a.STATS_TIME
FROM SYSCAT.TABLES a LEFT JOIN SYSCAT.DATAPARTITIONS a1
ON a.TABNAME=a1.TABNAME
LEFT JOIN SYSCAT.TABLESPACES b
ON a1.TBSPACEID=b.TBSPACEID
WHERE 1=1 --AND b.TBSPACE='MIS_INDEX'  OR  a.TBSPACEID=12
AND (a.TABNAME LIKE '%%' AND b.TBSPACE LIKE  'ZXJXDATASPACE2')
WITH UR ;

--视图和表关系依赖
SELECT *
FROM SYSCAT.TABDEP a
WHERE 1=1 AND a.TABNAME='RISK_ACRSKSTC'
AND a.TABSCHEMA='ZXJX'

--例程和表等依赖
SELECT a.*,b.PROCNAME
FROM SYSCAT.ROUTINEDEP a LEFT JOIN SYSCAT.PROCEDURES b
ON a.SPECIFICNAME=b.SPECIFICNAME
WHERE 1=1 AND a.ROUTINESCHEMA='ZXJX'
AND a.BNAME='RISK_ACRSKSTC2Q'

--函数
SELECT *
FROM SYSCAT.FUNCTIONS a
WHERE 1=1 AND a.FUNCSCHEMA='ZXJX'
AND a.FUNCNAME='FC_WRITELOG'

--表分区关系
SELECT a.TBSPACE,a.OWNER,a.OWNERTYPE,a.CREATE_TIME,a.TBSPACEID
,a.TBSPACETYPE,a.DATATYPE,a.PAGESIZE,a.DBPGNAME,b.DATAPARTITIONNAME
,b.TABSCHEMA,b.TABNAME,b.DATAPARTITIONID,b.CARD,b.NPAGES,b.FPAGES
,b.NPAGES*a.PAGESIZE/1024/1024 NSIZE_M
,b.FPAGES*a.PAGESIZE/1024/1024 FSIZE_M
,b.INDEX_TBSPACEID
,b.AVGCOMPRESSEDROWSIZE,b.AVGROWSIZE
,b.PCTROWSCOMPRESSED,b.PCTPAGESAVED,b.AVGROWCOMPRESSIONRATIO
FROM syscat.TABLESPACES a, SYSCAT.DATAPARTITIONS b
WHERE 1=1 AND b.TABNAME='PLAT_SRCCDEP_BAK'
AND a.TBSPACEID=b.TBSPACEID
AND b.CARD=0
FETCH FIRST 100 ROWS ONLY

--例程依赖
SELECT *
FROM SYSCAT.ROUTINEDEP A
WHERE 1=1 -- AND A.SPECIFICNAME='PC_ACCLANLINESTC2Q'
AND A.BNAME='DS_F_NI_B2C_B2C_PAYRST_ALL'