---------+---------+---------+---------+---------+---------+---------+---------+
with j as
(
select strip(value(p.collid, l.collid, db.db)) collid,
p.cnt pkgCnt, l.cnt lstCnt, dbCnt, dbMin, dbMax
from (select collid, count(*) cnt, left(collid, 2) c2
from sysibm.sysPackage group by collid) p
full join (select collid, count(*) cnt
from sysibm.sysPackList group by collid) l
on p.collid = l.collid
full join (select left(name, 2) db, count(*) dbCnt,
min(name) dbMin, max(name) dbMax
from sysibm.sysDatabase
group by left(name, 2) ) db
on p.c2 = db.db
)
select substr(collid, 1, 30) collid, pkgCnt, lstCnt
, dbCnt, dbMin, dbMax from j
where pkgCnt is not null
and lstCnt is not null
and -- not
( (dbCnt is not null or collid in ('YX', 'YY'))
and collid not like 'A1%'
and collid not like 'A2%'
and collid not like 'AUSAM%'
and collid not like '%?_%' escape '?'
and collid not like 'CBR%'
and collid not like 'COLXX%'
and collid not like 'CSQ%'
and collid not like '%DB2%'
and collid not like 'DSN%'
and collid not like 'FRN%'
and collid not like 'FAR%'
and collid not like 'HP%'
and collid not like 'M2%'
and collid not like 'PHP%'
and collid not like 'SAM%'
and collid not like 'STR%'
and collid not in('#D', 'ANLCHECK', 'BMIINSTL', 'BPO110'
, 'CFDB2COL', 'HBDTEST', 'JOSKA', 'LSNR', 'NTA1P'
, 'PTTAPLAN', 'PUPG53', 'Q', 'XP','XQ', 'YB' ,'YC', 'YD'
, 'YNK', 'YP', 'YW', 'ZK', 'ZP', 'ZS'
)
)
-- and collid > 'Q'
order by 1
with ur
---------+---------+---------+---------+---------+---------+---------+---------+
COLLID PKGCNT LSTCNT DBCNT DBMIN
---------+---------+---------+---------+---------+---------+---------+---------+
AC 2 4 1 AC01A1P
AM 33 4 1 AM01A1P
AN 11 33 1 AN01A1P
AT 92 92 1 AT01A1P
AU 519 7809 4 AU01A1P
AV 1771 1555 20 AV01A1P
AV01 493 558 20 AV01A1P
AV02 493 555 20 AV01A1P
AV03 455 108 20 AV01A1P
AV05 308 108 20 AV01A1P
COLLID PKGCNT LSTCNT DBCNT DBMIN
---------+---------+---------+---------+---------+---------+---------+---------+
AV06 308 108 20 AV01A1P
AV07 311 108 20 AV01A1P
AV08 311 108 20 AV01A1P
AV09 325 108 20 AV01A1P
AV10 325 107 20 AV01A1P
AV11 307 107 20 AV01A1P
AV12 307 107 20 AV01A1P
AV13 306 96 20 AV01A1P
AV14 306 96 20 AV01A1P
AV15 306 96 20 AV01A1P
AV16 306 96 20 AV01A1P
AV17 306 93 20 AV01A1P
AV18 306 93 20 AV01A1P
AV19 306 93 20 AV01A1P
AV20 306 93 20 AV01A1P
AV21 308 93 20 AV01A1P
AV22 308 93 20 AV01A1P
AV23 298 79 20 AV01A1P
AV24 298 79 20 AV01A1P
AV25 295 83 20 AV01A1P
AV26 295 83 20 AV01A1P
AV27 292 88 20 AV01A1P
AV28 318 88 20 AV01A1P
AV29 115 81 20 AV01A1P
AV30 115 81 20 AV01A1P
AV31 52 70 20 AV01A1P
AV32 52 70 20 AV01A1P
AX 39 236 1 AX01A1P
BE 384 2025 2 BE01A1P
BE01 240 1724 2 BE01A1P
BE02 240 1731 2 BE01A1P
BE03 240 1698 2 BE01A1P
BE04 240 1713 2 BE01A1P
BF 88 661 1 BF03A1P
BG 514 141 1 BG01A1P
BJ 7 2 10 BJ01A1P
BP 357 4948 2 BP01A1P
BQ 38 12 1 BQ01A1P
BS 35 118 1 BS01A1P
BU 35 3265 1 BU01A1P
BV 66 361 1 BV01A1P
BX 814 488 1 BX01A1P
CD 3099 8816 3 CD01A1P
CE 3424 721 4 CE01A1P
CJ 22 258 1 CJ01A1P
CK 1094 1442 1 CK01A1P
CT 379 645 2 CT01G1P
CW 475 404 4 CW01A1P
CY 231 23 1 CY02A1P
CZ 4249 1828 23 CZ01A1P
DA 14 840 8 DA351153
DB 49 18 19 DB01A1P
DE 1555 1289 6 DE01A1P
DG 2243 1431 2 DG01A1P
DI 528 4216 6 DI01A1P
DM 402 1259 1 DM40A1P
DO 26 5 2 DOTGDB2
DP 988 469 7 DP02A1P
COLLID PKGCNT LSTCNT DBCNT DBMIN
---------+---------+---------+---------+---------+---------+---------+---------+
DT 260 20 5 DT01A1P
DU 1 5 2 DUMMY
DW 477 114 2 DW10A1P
ED 924 267 3 ED02A1P
EF 214 560 5 EF01A1P
EG 75 113 2 EG01A1P
EK 881 142 2 EK01A1P
EQ 410 399 3 EQ01A1P
ER 839 87 1 ER01A1P
EU 176 343 7 EU01A1P
EX 790 2656 1 EX01A1P
FC 52 27 1 FC01A1P
FF 496 21 1 FF01A1P
FI 1369 1831 2 FI02A1P
FK 132 410 1 FK01A1P
FZ 291 19 2 FZ01A1P
GA 255 33 1 GA01A1P
GE 425 87 5 GE01A1P
GM 699 441 1 GM01A1P
GT 80 194 1 GT01A1P
HB 514 109 1 HB01A1P
HY 1381 487 2 HY01A1P
ID 248 423 6 ID01A1P
IT 161 1433 4 IT01A1P
JM 217 53 1 JM01A1P
JR 44 108 1 JR01A1P
KB 145 472 1 KB01A1P
KC 661 1968 1 KC01A1P
KD 218 435 3 KD01A1P
KE 2159 1054 1 KE01A1P
KS 772 534 8 KS01A1P
KT 24 279 1 KT01A1P
KX 32 797 1 KX01A1P
LC 392 78 1 LC02A1P
LG 20 48 1 LG01A1P
LO 6 30 1 LO01A1P
LW 143 245 2 LW01A1P
LX 105 332 1 LX01A1P
MB 121 53 1 MB01A1P
MC 773 45 3 MC01A1P
MF 1910 209 5 MF01A1P
MI 510 272 1 MI01A1P
NF 1015 758 4 NF01A1P
NG 1391 1320 6 NG01A1P
NH 3 12 1 NH01A1P
NI 6808 1374 6 NI02A1P
NI01 50 69 6 NI02A1P
NI02 50 69 6 NI02A1P
NJ 118 13 1 NJ01A1P
NK 131 186 1 NK01A1P
NL 671 946 1 NL01A1P
NO 233 51 1 NO01A1P
NP 334 318 1 NP01A1P
NR 15 392 2 NR01A1P
NT 8 269 1 NT21A1P
NZ 1969 572 8 NZ01A1P
OE 559 6072 1 OE02A1P
OO 649 4896 1 OO01A1P
COLLID PKGCNT LSTCNT DBCNT DBMIN
---------+---------+---------+---------+---------+---------+---------+---------+
OZ 103 305 1 OZ01A1P
PC 378 146 18 PC01A1P
PC01 1104 105 18 PC01A1P
PC02 1096 104 18 PC01A1P
PC03 1027 93 18 PC01A1P
PC04 1027 93 18 PC01A1P
PC05 1027 93 18 PC01A1P
PC06 1027 93 18 PC01A1P
PC07 965 93 18 PC01A1P
PC08 837 93 18 PC01A1P
PC09 837 93 18 PC01A1P
PC10 837 93 18 PC01A1P
PC11 689 90 18 PC01A1P
PC12 597 91 18 PC01A1P
PC13 410 91 18 PC01A1P
PC14 191 88 18 PC01A1P
PC15 191 88 18 PC01A1P
PC16 191 88 18 PC01A1P
PI 19 23 1 PI01A1P
PO 8 132 1 PO01A1P
PR 1078 104 2 PR01A1P
PT 107 41 1 PTDB
PV 117 3 3 PV01A1P
PW 488 329 1 PW01A1P
PX 335 72 3 PX01A1P
RA 134 285 1 RA01A1P
RB 135 14 1 RB01A1P
RE 205 379 1 RE01A1P
RF 353 49 1 RF01A1P
RG 5 299 1 RG01A1P
RI 187 75 1 RI01A1P
RM 1479 2170 1 RM01A1P
RP 1017 679 4 RP01A1P
RV 859 314 1 RV01A1P
SA 1178 500 8 SA02A1P
SB 133 74 1 SB01A1P
SE 47 120 1 SE02A1P
SF 212 34 1 SF01A1P
SM 20 25 2 SM01A1P
SN 1783 382 1 SN01A1P
SU 41 357 3 SU01A1C
SV 4638 1099 18 SV01A1P
SW 265 41 1 SW01A1P
SX 660 496 1 SX01A1P
SY 2 24 3 SY20A1P
TG 584 338 1 TG01A1P
TN 2033 186 1 TN01A1P
TP 286 48 1 TP01A1P
TR 409 854 3 TR01A1P
TT 235 66 1 TT01A1P
TV 14 25 1 TV01A1P
UI 195 16 1 UI01A1P
UU 1374 330 3 UU02A1P
VD 227 362 1 VD01A1P
VK 355 583 2 VK01A1P
VP 403 430 4 VP01A1P
VR 5 344 1 VR01A1P
VT 385 96 3 VT01A1P
COLLID PKGCNT LSTCNT DBCNT DBMIN
---------+---------+---------+---------+---------+---------+---------+---------+
VV 1116 2048 12 VV01A1P
WA 963 521 1 WA01A1P
WB 6765 1230 3 WB11A1P
WD 9 327 1 WD01A1P
WF 1411 411 1 WF01A1P
WG 141 38 1 WG01A1P
WI 5140 1579 3 WI01A1P
WK 143 287 10 WKDBDOF0
WL 615 145 9 WL01A1P
WM 157 687 1 WM01A1P
WN 1837 228 2 WN01A1P
WP 1458 2121 6 WP01A1P
WQ 1266 87 1 WQ01A1P
WR 40 18 1 WR01A1P
WU 1583 931 10 WU10A1P
WV 175 367 3 WV01A1P
WW 226 78 1 WW20A1P
WY 808 286 4 WY10A1P
XC 54 87 2 XC01A1P
XC00 44 9 2 XC01A1P
XC01 44 9 2 XC01A1P
XC02 44 9 2 XC01A1P
XC03 44 9 2 XC01A1P
XC04 44 9 2 XC01A1P
XC05 44 9 2 XC01A1P
XC06 44 9 2 XC01A1P
XC07 44 9 2 XC01A1P
XC08 44 9 2 XC01A1P
XC09 44 9 2 XC01A1P
XR 320 60 1 XR01A1P
YX 614 15893 ----------- ---------
YY 2505 16090 ----------- ---------
ZV 248 221 2 ZV01A1P
DSNE610I NUMBER OF ROWS DISPLAYED IS 217
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 46
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 285