zOs/SQL/OLDNAME
select char(dbName, 8) "db", count(*) "total",
sum(case when creator = 'IMF' then 1 else 0 end) "imf" ,
sum(case when creator = 'ODV' then 1 else 0 end) "odv" ,
sum(case when creator = 'OA1P' then 1 else 0 end) "oa1p" ,
sum(case when creator in ('IMF', 'ODV', 'OA1P')
then 0 else 1 end) "other" ,
char(max(case when creator in ('IMF', 'ODV', 'OA1P')
then '' else creator end), 8) "other"
from sysibm.systables
where type = 'T' and dbName in
( select s.dbname
from sysibm.systables s
where s.type = 'T' and s.creator in ('IMF', 'ODV')
)
group by dbName
order by dbName
;
select count(distinct dbName) "dbCnt", count(*) "tbCnt",
sum(case when creator = 'IMF' then 1 else 0 end) "imf" ,
sum(case when creator = 'ODV' then 1 else 0 end) "odv" ,
sum(case when creator = 'OA1P' then 1 else 0 end) "oa1p" ,
sum(case when creator in ('IMF', 'ODV', 'OA1P')
then 0 else 1 end) "other" ,
char(max(case when creator in ('IMF', 'ODV', 'OA1P')
then '' else creator end), 8) "other"
from sysibm.systables
where type = 'T' and dbName in
( select s.dbname
from sysibm.systables s
where s.type = 'T' and s.creator in ('IMF', 'ODV')
)
;
select char(d.name, 8),
sum(coalesce(t.cnt , 0)) "tables" ,
sum(coalesce(ts.byte, 0)) "tbBytes",
sum(coalesce(ts.cnt , 0)) "tbParts",
sum(coalesce(i.cnt , 0)) "indexes" ,
sum(coalesce(xs.byte, 0)) "ixBytes",
sum(coalesce(xs.cnt , 0)) "ixParts"
from sysibm.sysDatabase d
left join
( select dbName, count(*) cnt from sysibm.systables
where type = 'T' group by dbName
) t
on t.dbName = d.name
left join
( select dbName,
sum(coalesce(float(space), 0) * 1024) byte,
count(*) cnt
from sysIbm.tableSpaceStats
group by dbName
) ts
on ts.dbname = d.name
left join
( select dbName, count(*) cnt from sysibm.sysIndexes
group by dbName
) i
on i.dbName = d.name
left join
( select dbName,
sum(coalesce(float(space), 0)* 1024) byte,
count(*) cnt
from sysIbm.indexSpaceStats
group by dbName
) xs
on xs.dbName = d.name
where d.name in
( select s.dbname
from sysibm.systables s
where s.type = 'T' and s.creator in ('IMF', 'ODV')
)
group by d.name
order by d.name
;
select count(distinct d.name) "dbCnt" ,
sum(coalesce(t.cnt , 0)) "tables" ,
sum(coalesce(ts.byte, 0)) "tbBytes",
sum(coalesce(ts.cnt , 0)) "tbParts",
sum(coalesce(i.cnt , 0)) "indexes",
sum(coalesce(xs.byte, 0)) "ixBytes",
sum(coalesce(xs.cnt , 0)) "ixParts"
from sysibm.sysDatabase d
left join
( select dbName, count(*) cnt from sysibm.systables
where type = 'T' group by dbName
) t
on t.dbName = d.name
left join
( select dbName,
sum(coalesce(float(space), 0) * 1024) byte,
count(*) cnt
from sysIbm.tableSpaceStats
group by dbName
) ts
on ts.dbname = d.name
left join
( select dbName, count(*) cnt from sysibm.sysIndexes
group by dbName
) i
on i.dbName = d.name
left join
( select dbName,
sum(coalesce(float(space), 0)* 1024) byte,
count(*) cnt
from sysIbm.indexSpaceStats
group by dbName
) xs
on xs.dbName = d.name
where d.name in
( select s.dbname
from sysibm.systables s
where s.type = 'T' and s.creator in ('IMF', 'ODV')
)
;
;
x
with cre as
( select creator, name, dbname, tsName,
case when creator like 'OA1P%' then 'OA1P%'
when substr(creator, 2, 1) in ('0','1','2','3','4',
'5','6','7','8','9')
then left(creator, 1) || '%user'
when left(creator, 3) in ('BMC', 'DB2')
then left(creator, 3) || '%'
else creator
end creGr
from sysibm.systables
where type = 'T'
)
select char(t.creGr, 8), t.cnt "tables",
coalesce(ts.byte, 0) "tblBytes", ts.cnt "tblParts",
coalesce(i.cnt, 0) "indexes", xs.sp "idxBytes", xs.pt "idxParts"
from
( select creGr, count(*) cnt
from cre
group by creGr
) t
left join
(select creGr, sum(float(space) * 1024) byte, count(*) cnt
from cre c, sysIbm.tableSpaceStats s
where c.dbname = s.dbName and c.tsName = s.Name
group by creGr
) ts
on t.creGr = ts.creGr
left join
( select creGr, count(*) cnt
from cre c, sysibm.sysindexes x
where c.creator = x.tbCreator and c.name = x.tbName
group by creGr
) i
on t.creGr = i.creGr
left join
( select creGr, sum(float(q.space)* 1024) sp, count(*) pt
from cre c, sysibm.sysindexes x, sysIbm.indexSpaceStats q
where c.creator = x.tbCreator and c.name = x.tbName
and x.dbname = q.dbName and x.indexSpace = q.indexSpace
group by creGr
) xs
on t.creGr = xs.creGr
order by 2 desc
;
x
with cre as
( select creator, name,
case when creator = 'IMF' then 'IMF'
when creator = 'ODV' then 'ODV'
when creator like 'OA1P%' then 'OA1P%'
when left(creator, 2) in ('A0','A1','A2','A3','A4',
'A5','A6','A7','A8','A9') then 'Auser'
when left(creator, 2) in ('F0','F1','F2','F3','F4',
'F5','F6','F7','F8','F9') then 'Fuser'
else creator
end creGr
from sysibm.systables
)
select char(creGr, 8), count(*)
from cre
group by creGr
order by creGr
;
x
select char(creator, 8), count(*), count(distinct dbname)
from sysibm.systables
group by creator
order by 2 desc
;
select char(creator, 8), count(*), count(distinct dbname)
from sysibm.systables
group by creator
order by 2 desc
;
select char(coalesce(n.db, o.db), 8) "db",
coalesce(o.cnt, 0) "oldCnt", o.mi "oldMin", o.ma "oldMax",
coalesce(n.cnt, 0) "newCnt", n.mi "newMin", n.ma "newMax"
from
( select dbname db, count(distinct creator) cnt,
char(min(creator), 8) mi, char(max(creator), 8) ma
from sysibm.systables
where type = 'T' and creator like 'OA1%'
group by dbname
) n
full join
( select dbname db, count(distinct creator) cnt,
char(min(creator), 8) mi, char(max(creator), 8) ma
from sysibm.systables
where type = 'T' and not creator like 'OA1%'
select char(dbName, 8) "db", count(*) "imf+odv+other",
sum(case when creator = 'IMF' then 1 else 0 end) "imf",
sum(case when creator = 'ODV' then 1 else 0 end) "odv"
from sysibm.systables
where type = 'T' and creator in ('IMF', 'ODV')
group by dbName
order by dbName
;
x
with cre as
( select creator, name, dbname, tsName,
case when creator like 'OA1P%' then 'OA1P%'
when substr(creator, 2, 1) in ('0','1','2','3','4',
'5','6','7','8','9')
then left(creator, 1) || '%user'
when left(creator, 3) in ('BMC', 'DB2')
then left(creator, 3) || '%'
else creator
end creGr
from sysibm.systables
where type = 'T'
)
select char(t.creGr, 8), t.cnt "tables",
coalesce(ts.byte, 0) "tblBytes", ts.cnt "tblParts",
coalesce(i.cnt, 0) "indexes", xs.sp "idxBytes", xs.pt "idxParts"
from
( select creGr, count(*) cnt
from cre
group by creGr
) t
left join
(select creGr, sum(float(space) * 1024) byte, count(*) cnt
from cre c, sysIbm.tableSpaceStats s
where c.dbname = s.dbName and c.tsName = s.Name
group by creGr
) ts
on t.creGr = ts.creGr
left join
( select creGr, count(*) cnt
from cre c, sysibm.sysindexes x
where c.creator = x.tbCreator and c.name = x.tbName
group by creGr
) i
on t.creGr = i.creGr
left join
( select creGr, sum(float(q.space)* 1024) sp, count(*) pt
from cre c, sysibm.sysindexes x, sysIbm.indexSpaceStats q
where c.creator = x.tbCreator and c.name = x.tbName
and x.dbname = q.dbName and x.indexSpace = q.indexSpace
group by creGr
) xs
on t.creGr = xs.creGr
order by 2 desc
;
x
with cre as
( select creator, name,
case when creator = 'IMF' then 'IMF'
when creator = 'ODV' then 'ODV'
when creator like 'OA1P%' then 'OA1P%'
when left(creator, 2) in ('A0','A1','A2','A3','A4',
'A5','A6','A7','A8','A9') then 'Auser'
when left(creator, 2) in ('F0','F1','F2','F3','F4',
'F5','F6','F7','F8','F9') then 'Fuser'
else creator
end creGr
from sysibm.systables
)
select char(creGr, 8), count(*)
from cre
group by creGr
order by creGr
;
x
select char(creator, 8), count(*), count(distinct dbname)
from sysibm.systables
group by creator
order by 2 desc
;
select char(creator, 8), count(*), count(distinct dbname)
from sysibm.systables
group by creator
order by 2 desc
;
select char(coalesce(n.db, o.db), 8) "db",
coalesce(o.cnt, 0) "oldCnt", o.mi "oldMin", o.ma "oldMax",
coalesce(n.cnt, 0) "newCnt", n.mi "newMin", n.ma "newMax"
from
( select dbname db, count(distinct creator) cnt,
char(min(creator), 8) mi, char(max(creator), 8) ma
from sysibm.systables
where type = 'T' and creator like 'OA1%'
group by dbname
) n
full join
( select dbname db, count(distinct creator) cnt,
char(min(creator), 8) mi, char(max(creator), 8) ma
from sysibm.systables
where type = 'T' and not creator like 'OA1%'
group by dbname
) o
on n.db = o.db
order by 2 desc, 5 desc
;
x
select dbname, count(distinct creator),
char(min(creator), 8), char(max(creator), 8)
from sysibm.systables
where type = 'T' -- and dbname like 'A%'
group by dbname
order by 2 desc