Code: |
SELECT A.SCHEMA ,A.NAME ,C.LASTUSED FROM SYSIBM.SYSROUTINES A ,SYSIBM.SYSPACKDEP B ,SYSIBM.SYSPACKAGE C WHERE A.NAME = B.DNAME AND B.BNAME = C.NAME AND C.LASTUSED > '0001-01-01' ORDER BY C.LASTUSED DESC ; |
Code: |
---Stored Object plus Tables and more (Vertical) SELECT B.BQUALIFIER, B.BNAME, TRIM(A.COLLID) || '.' || TRIM(A.NAME), A.OWNER, A.VERSION, A.VALID, A.OPERATIVE, A.TIMESTAMP, A.BINDTIME, A.PKSIZE, A.ISOLATION, A.LASTUSED, CASE (B.BTYPE) WHEN 'A' THEN 'Alias' WHEN 'E' THEN 'INSTEAD OF trigger' WHEN 'F' THEN 'User-defined function or cast function' WHEN 'H' THEN 'Global variable' WHEN 'G' THEN 'Global temporary table' WHEN 'I' THEN 'Index' WHEN 'M' THEN 'Materialized query table' WHEN 'O' THEN 'Stored procedure' WHEN 'P' THEN 'Partitioned table space if it is defined as LARGE or with the DSSIZE parm' WHEN 'Q' THEN 'Sequence object' WHEN 'R' THEN 'Table space' WHEN 'S' THEN 'Synonym' WHEN 'T' THEN 'Table' WHEN 'U' THEN 'Distinct type' WHEN 'V' THEN 'View' WHEN 'W' THEN 'SYSTEM_TIME period' WHEN 'Z' THEN 'BUSINESS_TIME period' WHEN '0' THEN 'Sequence alias' END AS TYPE FROM SYSIBM.SYSPACKAGE A, SYSIBM.SYSPACKDEP B WHERE (A.COLLID=B.DCOLLID AND A.NAME=B.DNAME -- AND A.COLLID='XXXXXL' ---YOUR COLLECTION ID -- AND B.BTYPE='T' --- TABLE only ) AND LENGTH(A.NAME) > 8 --- Try and weed out IBM System Stuff AND A.LASTUSED > '0001-01-01' FOR FETCH ONLY WITH UR; --- JUST PROCEDURE SELECT TRIM(A.COLLID) || '.' || TRIM(A.NAME), A.VERSION, A.VALID, A.OPERATIVE, A.LASTUSED FROM SYSIBM.SYSPACKAGE A, SYSIBM.SYSPACKDEP B WHERE (A.COLLID=B.DCOLLID AND A.NAME=B.DNAME -- AND A.COLLID='XXXXXL' ---YOUR COLLECTION ID AND B.BTYPE='T' --- TABLE only ) AND LENGTH(A.NAME) > 8 --- Try and weed out IBM System Stuff AND A.LASTUSED > '0001-01-01' GROUP BY TRIM(A.COLLID) || '.' || TRIM(A.NAME), A.VERSION, A.VALID, A.OPERATIVE, A.LASTUSED FOR FETCH ONLY WITH UR; |
output generated using printer-friendly topic mod. All times are GMT - 5 Hours