zOs/SQL/CATPKVER
select substr(strip(p.collid) || '.' || p.name, 1, 20)
, substr(p.version, 1, 30) pVersion
, substr(o.version, 1, 30) oVersion
, days(timestamp(o.lastUsed)) - days(p.timestamp)
, p.lastUsed pLast
, o.lastUsed oLast
, p.timestamp pTimestamp
, o.timestamp oTimestamp
, p.pctimestamp pPcTimestamp
, o.pctimestamp oPcTimestamp
, p.*, o.*
from sysibm.sysPackage p
join sysibm.sysPackage o
on o.location = p.location
and o.collid = p.collid
and o.name = p.name
and (( o.timestamp < p.timestamp
or o.pcTimestamp < p.pctimestamp
) and o.lastUsed > date(p.timestamp) + 3 days
)
where p.lastUsed > current date - 100 days
and o.lastUsed > current date - 200 days
order by 4 desc, p.collid, p.name, p.version, p.version
fetch first 1000 rows only
with ur
;x;
select ( select count(*)
from sysibm.sysPackage o
where o.location = p.location
and o.collid = p.collid
and o.name = p.name
and (( o.timestamp < p.timestamp
or o.pcTimestamp < p.pctimestamp
) and o.lastUsed > date(p.timestamp)
)
) cOld
from sysibm.sysPackage p
where lastUsed > current date - 10 days
order by cOld desc
fetch first 100 rows only
;x;
group by collid, name
having count(*) > 1
, count(distinct version) cVers
, collid, name
, min(version), max(version)
, min(timestamp), max(timestamp)
, min(pcTimestamp), max(pcTimestamp)
from sysibm.sysPackage
where lastUsed > current date - 10 days
group by collid, name
having count(*) > 1
order by 1 desc, collid, name
fetch first 100 rows only
with ur
select count(*), count(distinct conToken) cCon
, count(distinct version) cVers
, collid, name
, min(version), max(version)
, min(timestamp), max(timestamp)
, min(pcTimestamp), max(pcTimestamp)
from sysibm.sysPackage
where lastUsed > current date - 10 days
group by collid, name
having count(*) > 1
order by 1 desc, collid, name
fetch first 100 rows only
with ur
set current application compatibility 'V11R1';
with c2 as
(
select p.*
, min(3
, row_number() over(partition by location, collid, name
order by timestamp desc)) tSeq
, case when lastUsed > current date - 7 days then '1week'
when lastUsed > current date - 1 month then '2mon'
when lastUsed > current date - 1 year then '3year'
else '4old' end lastUse
from sysibm.sysPackage p
)
, cp as
(
select lastUse, valid, operative, dbProtocol, tSeq
, count(*) v
from c2
group by lastUse, valid, operative, lastUse, dbProtocol, tSeq
)
select substr(lastUse, 2), valid, operative
, dbProtocol, tSeq
, sum(v) cVers
from cp
group by cube(operative, lastUse, valid, dbProtocol, tSeq)
with ur
;x;
set current application compatibility 'V11R1';
with c2 as
(
select p.*
, row_number() over(partition by location, collid, name
order by timestamp desc) tSeq
, case when lastUsed > current date - 7 days then '1week'
when lastUsed > current date - 1 month then '2mon'
when lastUsed > current date - 1 year then '3year'
else '4old' end lastUse
from sysibm.sysPackage p
)
select count(*)
, sum(case when valid = 'Y' then 1 else 0 end) valid
, sum(case when valid <> 'N' then 1 else 0 end) valNotN
, sum(case when lastUsed > current date - 400 days
then 1 else 0 end) n400
, sum(case when tSeq <= 2 then 1 else 0 end) tSe2
, sum(case when valid <> 'N'
and lastUsed > current date - 400 days
then 1 else 0 end) val400
, sum(case when valid <> 'N'
and lastUsed > current date - 400 days
and tSeq <= 2
then 1 else 0 end) val400se2
from c2
with ur
;x;
set current application compatibility 'V11R1';
with c2 as
(
select p.*
, case when lastUsed > current date - 7 days then '1week'
when lastUsed > current date - 1 month then '2mon'
when lastUsed > current date - 1 year then '3year'
else '4old' end lastUse
from sysibm.sysPackage p
)
, cp as
(
select lastUse, count(*) v
from c2
group by location, collid, name, lastUse
)
select v, substr(lastUse, 2), count(*) cp, sum(v) cVers
from cp
group by rollup(lastUse, v)
with ur
;x;
$#out 20160907 16:40:54
COL1 ...
PVERSION ...
OVERSION ...
COL4 ...
PLA...
...
...
...
...
COL1 PVERSION OVERSION COL4 PLA...
BP.YBPSTAM BPST0001265BF6605F BPST00007158D24A24 2738 07....
FADB2.SQLPCRTN 2012-06-19-16.38.27.690246 2011-04-29-09.57.59.868239 1520 12....
YY.YYVP03I VIP00000766423EA3B VIP00000736305E67B 1206 07....
PV.A5PO352 2013-06-30-02.47.27.062357 2013-02-06-15.43.51.069288 1126 04....
PV.A5PO355 2013-06-30-02.47.33.131118 2013-02-06-15.44.12.092387 1126 04....
PV.A5PO357 2013-06-30-02.47.48.905716 2013-02-06-15.44.53.919417 1126 04....
PV.A5PO358 2013-06-30-02.47.52.591627 2013-02-06-15.45.00.825749 1126 04....
PV.A5PO356 2013-08-13-17.20.46.968322 2013-02-06-15.44.21.687396 1118 04....
PC.YPCTECH IVN00001466526B28E IVN00000795E820606 1035 07....
ID.ID6500 EOSP000129 EOSP0000375A1B0EB3 818 07....
YY.YITRFTG TABS00045066F41A3A TABS0001735AA7E592 663 07....
OE.YOEP209 OE00000242681203CA OE0000007959BFC8B0 469 07....
NF.YNFIB11 IB000001126859705C IB0000010066FA8024 370 07....
NF.YNFIB16 IB0000011368597090 IB00000103673B2D92 370 07....
WP.YWPRCBX DBH000029868534338 DBH000022361B86A4E 368 07....
NF.YNFIB15 IB000001126859706D IB0000010066FA8157 362 06....
WP.YWPR7BX DBH00002986853443F DBH000022361B86A6F 354 07....
AV02.YAVVDPS AIS000076267C73C55 AIS00006766575B5B4 344 07....
NF.YNFIB13 IB0000011268597067 IB00000100671E0F56 294 06....
WU.YWUPUT TU0000041167F69F13 TU000001735C1B6EB6 280 07....
WP.YWPR7HX DBH000030668DE4CA3 DBH000022361B86A73 270 06....
WP.YWPR1P2 DBH000030668DA822D DBH00001825DF61E7A 251 07....
WP.YWPR2HX DBH000030668DE4C58 DBH000025264999E2E 251 07....
WP.YWPR3HX DBH000030668DE4C61 DBH000025264999E74 251 06....
WP.YWPR6PX DBH000030668D3A9F1 DBH000020560DB5945 250 07....
SW.YSWDB2U SWTL000106687FAE88 SWTL000084641B206E 215 07....
CT.YCTS200 CANA000141692319B9 CANA000112655B2FB0 193 07....
WP.YWPL2P2 DBH000030668DE4C0E DBH00001825DF61620 179 07....
FADB2.SQLPCRTN 2015-12-18-13.29.05.917548 2011-04-29-09.57.59.868239 130 07....
FADB2.SQLPLRTN 2014-09-18-18.32.39.433616 2011-11-04-00.14.33.902781 130 07....
ER.YERDGET EAMR00023169CE491E EAMR00019267274EF2 126 07....
CI.YCIAA1 NUGN0000806915E64A NUGN000074686E8194 124 07....
VV.YVVZZ05 VDSS00041369B29034 VDSS00036466E30063 123 07....
FADB2.SQLPCRTN 2015-12-18-13.29.05.917548 2012-06-19-16.38.27.690246 106 07....
NI.NI7995 SIR0000297 ALIS000169 104 09....
ZV.YZVESRT BESR00001269A42A86 ZED000007262B46532 100 13....
AV.YAVX051 AIS00008476A049E28 AIS00008376A01B827 88 12....
AV.YAVX101 AIS00008476A049E88 AIS00008376A01EB2B 87 10....
AV.YAVX051 AIS00008376A01B827 AIS00008476A049E28 64 13....
CK.YCKDCBB DCA000041169A83452 DCA0000413699C5679 57 07....
KX.YKXGFMB HYPO0001716A4E07D6 TLBO0000636989DACE 36 12....
KS.YKSCIFE EBVV0006006A4BCCF7 EBVV00055167ED2C1C 27 07....
RM.YRMORGP PARS0003716A14725C PARS00032565007918 27 07....
WP.YWPR1PX DBH00003346A3417E0 DBH000030668D3A9D7 27 07....
WP.YWPR2HX DBH00003346A34181D DBH000030668DE4C58 27 07....
WP.YWPR6PX DBH00003346A3418BD DBH000030668D3A9F1 27 07....
WP.YWPR1QX DBH00003346A3417F6 DBH000030668DA8237 26 07....
WP.YWPR2PX DBH00003346A341831 DBH000030668D3A9DB 26 07....
WP.YWPR3HX DBH00003346A34184F DBH000030668DE4C61 26 07....
WP.YWPR7HX DBH00003346A3418D7 DBH000030668DE4CA3 26 07....
WA.WA5800 VEGA000516 VEGA000509 12 10....
WI.YWIABV WS8I0009546A33AC1E WS8I00092468B59A0A 7 07....
WC.YWC0150 CSPS0001436A292498 CSPS0001396A21B6AD 4 11....
53 rows fetched: select substr(strip(p.collid) || '.' || p.name, 1, 20) , ...
fatal error in WSH: SQLCODE = -104: ILLEGAL SYMBOL "X". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: <ERR_STMT> <WNG_STMT> GET SQL SAVEPOINT HOLD FREE
ASSOCIATE
src x
> <<<pos 1 of 1<<<
sql = x
sqlCode 0: rollback
$#out