zOs/SQL/RTSXX
with tm as
( select t.*, s.psid,
char(case when s.nTables = 1 then ''
when s.nTables > 999 then '****'
else right('****' || strip(char(nTables)), 4)
end, 4) mult
from sysibm.sysTables t, sysibm.systablespace s
where t.type = 'T'
and t.dbName = s.dbName and t.tsname = s.name
)
select char(tm.name, 16) "tb", mult, decimal(cardf, 15) "runStatsCardF"
from tm
where dbname in ('DBJ01', 'DED02')
;
with tm as
( select t.*, s.psid,
char(case when s.nTables = 1 then ''
when s.nTables > 999 then '****'
else right('****' || strip(char(nTables)), 4)
end, 4) mult
from sysibm.sysTables t, sysibm.systablespace s
where t.type = 'T'
and t.dbName = s.dbName and t.tsname = s.name
)
select char(tm.name, 16) "tb", mult,
decimal(sum(r.totalrows), 15) "rtsRows"
from tm
left join sysibm.tablespacestats r
on r.dbid = tm.dbid and r.psid = tm.psid
where tm.dbname in ('DBJ01', 'DED02')
group by tm.creator, tm.name, mult
;
with tm as
( select t.*, s.psid,
char(case when s.nTables = 1 then ''
when s.nTables > 999 then '****'
else right('****' || strip(char(nTables)), 4)
end, 4) mult
from sysibm.sysTables t, sysibm.systablespace s
where t.type = 'T'
and t.dbName = s.dbName and t.tsname = s.name
)
select char(tm.name, 16) "tb", mult,
decimal(cnt.cnt, 15) "sqlCount"
from tm
left join
( select 'ODV', 'TBJ317A1', count(*) from odv.TBJ317A1
union select 'ODV', 'TBJ318A1', count(*) from odv.TBJ318A1
union select 'ODV', 'TBJ319A1', count(*) from odv.TBJ319A1
union select 'ODV', 'TBJ320A1', count(*) from odv.TBJ320A1
union select 'ODV', 'TBJ321A1', count(*) from odv.TBJ321A1
union select 'ODV', 'TBJ322A1', count(*) from odv.TBJ322A1
union select 'ODV', 'TBJ323A1', count(*) from odv.TBJ323A1
union select 'ODV', 'TED021A1', count(*) from odv.TED021A1
union select 'ODV', 'TED021K1', count(*) from odv.TED021K1
union select 'ODV', 'TED021X1', count(*) from odv.TED021X1
union select 'ODV', 'TED021Y1', count(*) from odv.TED021Y1
union select 'ODV', 'TED022A1', count(*) from odv.TED022A1
union select 'ODV', 'TED022K1', count(*) from odv.TED022K1
union select 'ODV', 'TED022X1', count(*) from odv.TED022X1
union select 'ODV', 'TED022Y1', count(*) from odv.TED022Y1
union select 'ODV', 'TED023A1', count(*) from odv.TED023A1
union select 'ODV', 'TED024A1', count(*) from odv.TED024A1
union select 'ODV', 'TED025A1', count(*) from odv.TED025A1
union select 'ODV', 'TED026A1', count(*) from odv.TED026A1
union select 'ODV', 'TED026K1', count(*) from odv.TED026K1
union select 'ODV', 'TED028A1', count(*) from odv.TED028A1
) as cnt (cr, tb, cnt)
on tm.creator = cnt.cr and tm.name = cnt.tb
where tm.dbname in ('DBJ01', 'DED02')
;
x-----
with ts as
( select ts.*,
( select min(name)
from sysibm.sysTables tb
where tb.dbname = ts.dbName and tb.tsName = ts.name) tb
from sysibm.systablespace ts
where ts.dbname = 'DED02'
)
select char(ts.tb, 12) "ts for tb",
min(nTables) "nTables",
min(partitions) "parts",
sum(s.totalrows) "rts rows",
count(*) "rts count",
case when min(s.name) is null then '*** no RTS'
when max(partitions) <= 1 and count(*) = 1 then ''
when count(*) = 1 then '***count0'
when max(partitions) = count(*) then ''
else '***missRTS'
end "rtsSta"
from ts
left join sysibm.tablespacestats s
on s.dbid = ts.dbid and s.psid = ts.psid
group by ts.dbname, ts.name, ts.tb
order by 1
;
with ts as
( select ts.*,
( select min(name)
from sysibm.sysTables tb
where tb.dbname = ts.dbName and tb.tsName = ts.name) tb
from sysibm.systablespace ts
where ts.dbname = 'DED02'
)
select char(ts.tb, 12) "for tb",
char(ts.dbName, 8) "db",
char(ts.name, 8) "ts",
s.updatestatstime
from ts
left join sysibm.tablespacestats s
on s.dbid = ts.dbid and s.psid = ts.psid
where s.updatestatstime is null
or s.updatestatstime < current timestamp + 1 day
order by 4 asc
;
select char(t.name, 12) "tb",
char(t.dbName, 8) "db",
char(t.TSname, 8) "ts",
t.statstime
from sysibm.sysTables t
where t.type = 'T' and t.dbname = 'DED02'
order by 4 asc
;
x
select dbname, name, partition, s.updatestatstime
from sysibm.tablespacestats s, sysibm.sysDatabase d
where s.dbid = d.dbid
and d.name in ('DED02', 'DED05', 'DED06', 'DGM01', 'DRE01')
order by s.updatestatstime asc
;
union select i.dbname, i.indexspace name,
-- and ts.dbName = 'DED02'
group by ts.dbname, ts.name, ts.tb
-- where s.dbname = 'DED02'
union select char(ts.tb, 12), '', 'marker', partitions, nTables, '
from ts
order by 1, 2
;
x
select s.dbname, s.name, sum(s.totalrows) "rts rows",
count(*) "rts count",
-- min(partition), max(partition),
min(s.updatestatstime), max(s.updatestatstime)
from sysibm.tablespacestats s, sysibm.sysDatabase d
where s.dbid = d.dbid
and d.name in ('DED02', 'DED05', 'DED06', 'DGM01', 'DRE01')
group by s.dbname, s.name
union select i.dbname, i.indexspace name,
sum(i.totalentries) "rts rows", count(*) "rts count",
-- min(partition), max(partition),
min(i.updatestatstime), max(i.updatestatstime)
from sysibm.indexspacestats i, sysibm.sysDatabase d
where i.dbid = d.dbid
and d.name in ('DED02', 'DED05', 'DED06', 'DGM01', 'DRE01')
group by dbname, indexspace
order by 1, 2
;
select char(dbname, 8) "db", char(name, 12) "name",
cardf "(fukey)Card", spacef "runStats space", statstime
from sysibm.sysTables
where type = 'T' and dbname in ('DED02', 'DED05', 'DED06', 'DGM01',
'DRE01')
union select char(t.dbname, 8) "db", char(i.name, 12) "name",
i.fullKeyCardf "(fukey)Card", i.spacef "runStats space",
i.statstime
from sysibm.sysTables t, sysibm.sysIndexes i
where i.tbCreator = t.creator and i.tbName = t.name
and t.dbname in ('DED02', 'DED05', 'DED06', 'DGM01',
'DRE01')
order by 5 desc
;