Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Mon Oct 09, 2017 11:58 am Post subject:
Thanks kolusu,
The above query did not give me the complete list. It did give me the column I need to look at( LASTUSED).
Below are two queries I built. The first one gives all the detail about Objects in the database. The second is a unique list of stored procedures. Both queries have Where block data commented out, uncomment and fill in your target specific object.
I'm sure these can be more refined, I may have made assumptions that aren't 100%.
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;
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum