Hallo Forum,

anbei ein paar Beispiele mit den UDTF's

1. Beispiel: Die 20 größten Objekte in der QUSRSYS. 1. Aufruf 7 Sek, 2. Aufruf 0,1 Sek

SELECT CAST(OBJLONGSCHEMA AS CHAR (10)) AS OBJLIBRARY, OBJNAME,OBJTYPE,OBJATTRIBUTE,OBJSIZE,OBJTEXT
FROM TABLE(QSYS2.OBJECT_STATISTICS('QUSRSYS','ALL')) AS X
order by OBJSIZE desc
limit 20;

2. Beispiel: Alle Objekte, die der User ROSS in einem bestimmten Zeitraum erstellt hat. 1. Aufruf 1 Min, 2. Aufruf 1,7 Sek

SELECT CAST(OBJLONGSCHEMA AS CHAR (10)) AS OBJLIBRARY, OBJNAME,OBJTYPE,OBJATTRIBUTE,OBJSIZE,
date(OBJCREATED) as OBJDATE, time(OBJCREATED) as OBJTIME, OBJDEFINER, OBJTEXT
FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALLUSR','ALL')) AS X
where OBJDEFINER = 'ROSS'
and date(OBJCREATED) between '2016-03-15' and '2016-03-31'
and time(OBJCREATED) between '15:00:00' and '16:00:00';

3. Beispiel: Suche eines Objekts über *ALLUSR. 1. Aufruf 5 Sek, 2. Aufruf 1,7 Sek

SELECT CAST(OBJLONGSCHEMA AS CHAR (10)) AS OBJLIBRARY, OBJNAME,OBJTYPE,OBJATTRIBUTE,OBJSIZE,
date(OBJCREATED) as OBJDATE, time(OBJCREATED) as OBJTIME, OBJDEFINER, OBJTEXT
FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALLUSR','ALL')) AS X
where objname = 'MYOBJECT';

4. Beispiel: Suche nach doppelten physical Files 1,2 Sek

SELECT OBJNAME,count(*) as Anzahl
FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALLUSR','*FILE')) AS X
where OBJATTRIBUTE = 'PF' and OBJNAME not like 'Q%'
group by OBJNAME
having count(*) > 1
order by OBJNAME;

Der Link zur Doku: https://www.ibm.com/developerworks/c...on+ease+of+use


Herzliche Grüße
Rainer Ross