SELECT VIEWSCHEMA,VIEWNAME,VALID,VARCHAR(TEXT)
FROM SYSCAT.VIEWS
WHERE VIEWSCHEMA = 'ADMINISTRATOR'
/* check procedures if function recreate then recreate procedure is needed */
SELECT ROUTINENAME FROM sysibm.sysroutines
WHERE VARCHAR(TEXT) LIKE '%FN_CHKVALIDATE%'
/* 檢查可能失效的PROCEDURE */
SELECT *
FROM SYSIBM.SYSDEPENDENCIES
WHERE DSCHEMA = 'ORION'
AND BNAME NOT IN (SELECT PKGNAME FROM SYSCAT.PACKAGEDEP WHERE BTYPE = 'T')
AND BTYPE = 'K'
/* 檢查那些Stored Procedures用了哪些Functions */
SELECT PROCNAME,FUNCNAME
FROM SYSCAT.PROCEDURES,
(SELECT DNAME SYSDNAME,BNAME SYSNAME FROM SYSIBM.SYSDEPENDENCIES) T,
(SELECT PKGNAME,BNAME PKGBNAME FROM SYSCAT.PACKAGEDEP) T1,
(SELECT FUNCNAME,SPECIFICNAME FUNCSPECIFICNAME FROM SYSCAT.FUNCTIONS) T2
WHERE SPECIFICNAME = T.SYSDNAME
AND T.SYSNAME = T1.PKGNAME
AND T1.PKGBNAME = T2.FUNCSPECIFICNAME;
/* 檢查那些Stored procedure 用了哪些Tables */
SELECT T.DNAME,A.BNAME
FROM SYSCAT.PACKAGEDEP A,
(SELECT BNAME,DNAME from SYSIBM.SYSDEPENDENCIES WHERE DSCHEMA = 'ORION') T
WHERE BTYPE = 'T' AND PKGNAME = T.BNAME
/* 如何檢持哪些VIEW使用哪些TABLES,且是否為VALID */
SELECT A.VIEWNAME,A.BNAME,B.VALID
FROM SYSCAT.VIEWDEP A,SYSCAT.VIEWS B
WHERE A.VIEWNAME = B.VIEWNAME
AND B.DEFINER = 'ORION'
