zOs/SQL/CATPKGDP
with t as
(
select char(bQualifier, 8) cr, char(bName, 12) tb,
't' ty
-- char(strip(bQualifier) || '.' || strip(bName), 24), -- btype,
-- char(strip(dCollid) || '.' || strip(dName), 24),
-- count(*)
from sysibm.syspackdep
where(( dCollid = 'CD' and dName in
('YCDOGEE' ,
'YCDSGET' ,
'YCDX031' )
) or ( dCollid = 'CT' and dName in
('YCTMRLS' ,
'YCTM150' ,
'YCTM200' ,
'YCTM201' ,
'YCTM204' ,
'YCTM206' ,
'YCTM214' ,
'YCTM250' ,
'YCTM300' ,
'YCTM350' ,
'YCTM353' ,
'YCTM358' ,
'YCTSRLS' ,
'YCTSVPT' ,
'YCTS100' ,
'YCTS150' ,
'YCTS200' ,
'YCTS250' ,
'YCTS300' ,
'YCTS350' ,
'YCTS400' ,
'YCTTRA' )
) or ( dCollid = 'CZ' and dName in
('YCZAURA' ,
'YCZM005' ,
'YCZM025' ,
'YCZM095' ,
'YCZM098' ,
'YCZM100' ,
'YCZM101' ,
'YCZM103' ,
'YCZM106' ,
'YCZM107' ,
'YCZM300' ,
'YCZPARS' ,
'YCZT005' ,
'YCZT025' ,
'YCZT095' ,
'YCZT098' ,
'YCZT100' ,
'YCZT101' ,
'YCZT103' ,
'YCZT106' ,
'YCZT107' ,
'YCZT300' ,
'YCZUCOP' )
) or ( dCollid = 'RM' and dName in
('YRMCGP' ,
'YRMKCG' )
)) and bType = 'T'
group by bQualifier, bName
)
select cr, tb, closeRule,
'alter tablespace ' || strip(c.dbName) || '.' || strip(c.name)
|| ' closeRule no;'
from t, sysibm.sysTables c, sysibm.sysTablespace s
where cr = c.creator and tb = c.name
and c.dbName = s.dbName and c.tsName = s.name
union all select cr, tb, closeRule,
'alter index ' || strip(creator) || '.' || strip(name)
|| ' closeRule no;'
from t, sysibm.sysIndexes c
where cr = tbCreator and tb = tbName
order by cr, tb, 4
with ur
; x ;
select char(strip(bQualifier) || '.' || strip(bName), 24), btype,
char(strip(dCollid) || '.' || strip(dName), 24),
count(*)
from sysibm.syspackdep
where bName like 'TNI250A%'
group by bQualifier, bName, bType, dCollid, dName
order by dCollid, dName
with ur
; x ;
with tc as
(
select dLocation, dCollid, dName,
max(char(pcTimeStamp) || dContoken) tc
-- char(strip(dCollid) || '.' || strip(dName), 24) "package",
-- pcTimeStamp "preCompile",
-- char(pcTimeStamp) "char preCompile",
-- hex(dConToken) "conToken"
from sysibm.syspackdep
left join sysibm.syspackage
on dLocation = location
and dCollid = collid and dName = name and dConToken = conToken
where -- bType in ('P', 'R')
bQualifier = 'OA1A' and bName = 'TMF150A1'
group by dLocation, dCollid, dName
)
select dLocation, dCollid, dName,
substr(tc, 27) dconToken,
timestamp(left(tc, 26)) pcTimestamp
from tc
order by 1
;
x
select char(strip(bQualifier) || '.' || strip(bName), 24), btype,
char(strip(dCollid) || '.' || strip(dName), 24)
from sysibm.syspackdep
where dName = 'DBWK411'
;
x
select distinct
char(strip(dCollid) || '.' || strip(dName), 24) "package",
pcTimeStamp "preCompile",
hex(dConToken) "conToken"
from sysibm.syspackdep
left join sysibm.syspackage
on dCollid = collid and dName = name and dConToken = conToken
where bType in ('P', 'R')
and bQualifier like 'BJA_0001'
order by 1
;
x
select distinct coalesce(s.dep, t.dep), s.ty, t.ty
from
(
select 'sp' ty,
char(strip(dCollid)||'.'||strip(dName), 24) dep
from sysibm.syspackdep
where bType in ('P', 'R')
and bQualifier like 'BJA_0001'
) s
full join
(
select 'ta' ty,
char(strip(dCollid) || '.' || strip(dName), 24) dep
from sysibm.syspackdep
where -- bType in ('T') and
(bQualifier, bName) in
( select creator, name
from sysibm.sysTables
where dbName like 'BJA_0001'
)
) t
on t.dep = s.dep
order by 1
;
x
select char(strip(bQualifier) || '.' || strip(bName), 24), btype,
char(strip(dCollid) || '.' || strip(dName), 24)
from sysibm.syspackdep
where -- bType in ('T') and
(bQualifier, bName) in
( select creator, name
from sysibm.sysTables
where dbName like 'BJA_0001'
)
order by 1
;
x
select char(strip(bQualifier) || '.' || strip(bName), 24), btype,
char(strip(dCollid) || '.' || strip(dName), 24)
from sysibm.syspackdep
where bType in ('P', 'R')
and bQualifier like 'BJA_0001'
order by 1
;
x
select char(bname,12), char(bqualifier,12), btype
from sysibm.syspackdep
where bname = 'BJA80001'
;
select char(bname,12), char(bqualifier,12), btype
from sysibm.syspackdep
where dname = 'DBWK2'
;
x
select distinct p.collid, p.Name, p.version, p.type from
sysibm.syspackdep d join sysibm.syspackage p on p.location =
d.dLocation and p.collid = d.dCollid and p.name = d.dName
and p.conToken = d.dConToken where ( bQualifier = 'OA1A03'
and bName in ('TAV408A1'))
;
x
select distinct p.collid, p.Name, p.version, p.type from
sysibm.syspackdep d join sysibm.syspackage p on p.location =
d.dLocation and p.collid = d.dCollid and p.name = d.dName
and p.conToken = d.dConToken where ( bQualifier = 'VDPS2'
and bName in ('DTUNDERFIXCOMQ', 'DTUNDERFIXCOMP', 'IIXDU',
'IIXDUZWEI')) or ( bQualifier = 'VDPS3 ' and bName in ('vdps
table drei ')) or ( bQualifier = 'EFG' and bName in ('HIK'))
or ( (bQualifier, bName) in ( select tbcreator, tbname from
sysibm.sysindexes where ( creator = 'VDPS2' and name in
('IIXDU', 'IIXDUZWEI')) ) )
;
select distinct p.collid, p.Name, p.version, p.type from sysibm.syspackd
in sysibm.syspackage p on p.location = d.dLocation and p.collid = d.dCol
p.name = d.dName and p.conToken = d.dConToken where ( bQualifier = 'VD
d bName in ('DTUNDERFIXCOMQ', 'DTUNDERFIXCOMP', 'IIXDU', 'IIXDUZWEI')) o
alifier = 'VDPS3 ' and bName in ('vdps table drei ')) or ( bQualifier =
nd bName in ('HIK')) or ( (bQualifier, bName) in ( select tbcreator, tbn
m sysibm.sysindexes where ( creator = 'VDPS2' and name in ('IIXDU', 'IIX
)) ) )
;
select char(bname,12), char(bqualifier,12), btype
from sysibm.syspackdep
where dname = 'DBWK2'
;
select distinct char(p.collid, 8), char(p.Name,12), char(p.version, 30),
p.type
from sysibm.syspackdep d join sysibm.syspackage p
on p.location = d.dLocation and p.collid = d.dCollid
and p.name = d.dName and p.conToken = d.dConToken
where
-- ( bType in ('A', 'M', 'S', 'T', 'V') and
( ( bQualifier = 'gDB9998' and
bName in ('TWK003A', 'TWK001A')
) or
( bQualifier = 'dGDB9998' and
bName in ('TWK003A', 'TWK001A')
) or
( (bQualifier, bName) in
( select tbcreator, tbname
from sysibm.sysindexes
where creator = 'GDB9998' and
name = 'IQQ051A0'
)
)
)
-- )
;
x