with i (i) as 00001000
( select 1 from sysibm.sysDummy1 00002000
union all select 2 from sysibm.sysDummy1 00003000
) 00004000
, j as 00005000
( 00006000
select case when s.dbName is null then '-' else 'c' end 00007000
|| case when r.dbName is null then '-' else 'r' end cr 00008000
, value(s.dbName, r.dbName) db 00010000
, value(s.name, r.Name) ts 00010100
, value(p.partition, r.partition) part 00011000
, value(i.i, r.instance) inst 00012000
, s.clone 00012102
|| case when s.instance = i.i then 'b' else 'c' end cl 00012202
, p.space pSpc 00012300
, value(s.dbId, r.dbId) dbid 00013000
, value(s.psid, r.psid) psid 00014000
from (sysibm.sysTableSpace s 00020000
join i on s.instance = i or s.clone = 'Y' 00030000
join sysibm.sysTablePart p 00040000
on s.dbName = p.dbName and s.name = p.tsName 00050000
) full outer join sysibm.sysTableSpaceStats r 00052000
on s.dbName = r.dbName and s.name = r.name 00053000
and p.partition = r.partition 00053100
and i.i = r.instance 00054000
) 00060000
select 'include tablespace ' || strip(db) || '.' || strip(ts) 00060102
|| ' partlevel ' || part 00060202
, j.* 00060302
from j 00060402
where cr = 'c-' 00060502
order by cl, cr, db, ts, part 00060602
;x; 00060702
select count(*), cr, inst, cl 00061002
from j 00061102
group by cr, inst, cl 00061202
;x; 00061302
select * 00062000
from j 00062100
where cr <> 'cr' 00062200
order by db, ts, part, inst 00062300
;x; 00062400
, s (cr, db, ts, inst, parts, pX, pV, pB, l) as 00062502
( 00062602
select cr, db, ts, inst, cast('' as varChar(500)) 00062702
, min(part), -1, -1, 0 00062802
from j 00062902
group by cr, db, ts, inst 00063002
union all select cr, db, ts, inst, parts, 00063102
(select min(j.part) from j 00063202
where j.cr = s.cr and j.db = s.db and j.ts = s.ts 00063302
and j.part > s.pX 00063402
), pV, pB, l+1 00063502
from s 00063602
where l < 99 and pX is not null 00063702
) 00063802
select * 00063902
from s 00064002
;x; 00064102
select count(*), cr 00064200
from j 00064300
group by cr 00064400
;x; 00065000
select cr 00070000
|| case when r.dbName is null then '-' else 'r' end cr 00080000
, substr(value(c.db, r.dbName), 1, 8) db 00090000
, substr(value(c.ts, r.name), 1, 8) ts 00100000
, value(c.part, r.partition) part 00110000
, value(c.i, r.instance) inst 00120000
from c full outer join sysibm.sysTableSpaceStats r 00130000
on c.db = r.dbName and c.ts = r.name and c.part = r.partition 00140000
and c.i = r.instance 00150000
, x as 00240000
( 00250000
select case when c.db is null then '-' else 'c' end 00260000
|| case when r.dbName is null then '-' else 'r' end cr 00270000
, substr(value(c.db, r.dbName), 1, 8) db 00280000
, substr(value(c.ts, r.name), 1, 8) ts 00290000
, value(c.part, r.partition) part 00300000
, value(c.i, r.instance) inst 00310000
from c full outer join sysibm.sysTableSpaceStats r 00320000
on c.db = r.dbName and c.ts = r.name and c.part = r.partition 00330000
and c.i = r.instance 00340000
) 00350000