With T1 as (
SELECT specific_schema, specific_name, external_name,
cast(left(external_name, locate('/',external_name)-1) as char(10)) as procedure_library,
cast(substring(external_name, locate('/',external_name)+1) as char(10)) as procedure_object
FROM QSYS2.sysprocs p)
select * from T1,
lateral ( select last_used_timestamp from TABLE(QSYS2.OBJECT_STATISTICS(PROCEDURE_LIBRARY, '*PGM',
OBJECT_NAME => t1.PROCEDURE_OBJECT)) A)
WHERE specific_schema = 'ROUTINES'
AND SPECIFIC_NAME = 'DRPCODE';
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Robert Rogerson
Sent: Tuesday, March 5, 2019 3:17 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL objects last used....
Hi all,
If I run these two queries
SELECT specific_schema, specific_name, external_name
FROM sysprocs
WHERE specific_schema = ('QS36F')
AND SPECIFIC_NAME = 'ADDSTOREZONE';
Which returns
SPECIFIC_SCHEMA SPECIFIC_NAME EXTERNAL_NAME
QS36F ADDSTOREZONE QS36F/ADDST0001
And then feed it to the table function
SELECT *
FROM TABLE(QSYS2.OBJECT_STATISTICS('QS36F', '*PGM',
OBJECT_NAME => 'ADDST00001')) A;
I can get the last used date for the sql procedure.
Before I go reinventing the wheel has anyone else done a similar query
that could be posted?
Thanks,
Rob
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
