select coalesce (ts.db, rs.db, ix.db),
ts.sp "cat.ts.space", rs.sp "rts.ts.space",
ix.sp "cat.ix.space", ri.sp "rts.ix.space",
ts.cnt "cat.ts.cnt", ts.minTi, ts.maxTi,
rs.cnt "rts.ts.cnt", rs.minTi, rs.maxTi,
ix.cnt "cat.ix.cnt", ix.minTi, ix.maxTi,
ri.cnt "rts.ix.cnt", ri.minTi, ri.maxTi
from
(select dbname db, sum(spacef * 1024) sp, count(*) cnt,
min(statstime) minTi, max(statstime) maxTi
from sysibm.systablepart
group by dbname
) ts
left join
(select dbname db, sum(float(space) * 1024) sp, count(*) cnt,
min(updatestatstime) minTi, max(updatestatstime) maxTi
from sysIbm.tableSpaceStats
group by dbName
) rs
on rs.db = ts.db
left join
(select ix.dbname db, sum(ip.spacef * 1024) sp,
count(*) cnt,
min(ip.statstime) minTi, max(ip.statstime) maxTi
from sysibm.sysIndexes ix, sysibm.sysIndexPart ip
where ix.name = ip.ixName and ix.creator = ip.ixCreator
group by dbname
) ix
on ix.db = ts.db
left join
(select dbname db, sum(float(q.space)* 1024) sp, count(*) cnt,
min(updatestatstime) minTi, max(updatestatstime) maxTi
from sysIbm.indexSpaceStats q
group by dbName
) ri
on ri.db = ts.db
where ts.db in (
'XBA5B001',
'XBA5C001',
'XBA5C002',
'XBA5D001',
'XBA5D002',
'XBA5D003',
'XBA5E001',
'XBA5E002',
'XBA5F001',
'XBA5F002',
'XBA5F003',
'XBA5F004',
'XBA5G001',
'XBA5G002',
'XBA5G003',
'XBA5G004',
'XBA5G005',
'XBA5G006',
'XBA5G007',
'XBA5G008',
'XBA5G009',
'XBA5G010',
'XBA5G011',
'XBA5G012',
'XBA5G013',
'XBA5G014',
'XBA5G015',
'XBA5G016',
'XBA5G017',
'XBA5G018',
'XBA5G019',
'XBA5G020',
'XBA5G021',
'XBA5G022',
'XBA5G023',
'XBA5G024',
'XBA5G025',
'XBA5G026',
'XBA5G027',
'XBA5G028',
'XBA5G029',
'XBA5H001',
'XBA5H002',
'XBA5H003',
'XBA5H004',
'XBA5H005',
'XBA5H006',
'XBA5H007',
'XBA5H008',
'XBA5H009',
'XBA5H010',
'XBA5H011',
'XBA5H012',
'XBA5H013',
'XBA5H014',
'XBA5H015',
'XBA5H016',
'XBA5H017',
'XBA5H018',
'XBA5H019',
'XBA5H020',
'XBA5H021',
'XBA5H022',
'XBA5H023',
'XBA5H024',
'XBA5H025',
'XBA5H026',
'XBA5H027',
'XBA5I001',
'XBA53001',
'XBA53002',
'XBA53003',
'XBA53004',
'XBA53005',
'XBA53006',
'XBA53007',
'XBA53008',
'XBA53009',
'XBA53010',
'XBA53011',
'XBA53012',
'XBA53013',
'XBA53014',
'XBA53015',
'XBA53016',
'XBA53017',
'XBA53018',
'XBA53019',
'XBA53020',
'XBA53021',
'XBA53022',
'XBA53023',
'XBA53024',
'XBA53025',
'XBA53026',
'XBA53027',
'XBA53028',
'XBA53029',
'XBA53030',
'XBA53031',
'XBA53032',
'XBA53033',
'XBA53034',
'XBA53035',
'XBA53036',
'XBA53037',
'XBA53038',
'XBA53039',
'XBA53040',
'XBA53041',
'XBA53042',
'XBA53043',
'XBA53044',
'XBA53045',
'XBA53046',
'XBA53047',
'XBA53048',
'XBA53049',
'XBA53050',
'XBA53051',
'XBA53052',
'XBA53053',
'XBA53054',
'XBA53055',
'XBA53056',
'XBA53057',
'XBA53058',
'XBA53059',
'XBA53060',
'XBA53061',
'XBA53062',
'XBA53063',
'XBA53064',
'XBA53065',
'XBA53066',
'XBA53067',
'XBA53068',
'XBA53069',
'XBA53070',
'XBA53071',
'XBA53072',
'XBA53073',
'XBA53074',
'XBA53075',
'XBA53076',
'XBA53077',
'XBA53078',
'XBA53079',
'XBA53080',
'XBA53081',
'XBA53082',
'XBA53083',
'XBA53084',
'XBA53085',
'XBA53086',
'XBA53087',
'XBA53088',
'XBA53089',
'XBA53090',
'XBA53091',
'XBA53092',
'XBA53093',
'XBA53094',
'XBA53095',
'XBA53096',
'XBA53097',
'XBA53098',
'XBA53099',
'XBA53100',
'XBA53101',
'XBA53102',
'XBA53103',
'XBA53104',
'XBA53105',
'XBA53106',
'XBA53107',
'XBA53108',
'XBA53109',
'XBA53110',
'XBA53111',
'XBA53112',
'XBA53113',
'XBA53114',
'XBA53115',
'XBA53116',
'XBA53117',
'XBA53118',
'XBA53119',
'XBA53120',
'XBA53121',
'XBA53122',
'XBA53123',
'XBA53124',
'XBA53125',
'XBA53126',
'XBA53127',
'XBA53128',
'XBA53129',
'XBA53130',
'XBA53131',
'XBA53132',
'XBA53133',
'XBA53134',
'XBA53135',
'XBA53136',
'XBA53137',
'XBA53138',
'XBA53139',
'XBA53140',
'XBA53141',
'XBA53142',
'XBA53143',
'XBA53144',
'XBA53145',
'XBA53146',
'XBA53147',
'XBA53148',
'XBA53149',
'XBA53150',
'XBA53151',
'XBA53152',
'XBA53153',
'XBA53154',
'XBA53155',
'XBA53156',
'XBA53157',
'XBA53158',
'XBA53159',
'XBA53160',
'XBA53161',
'XBA53162',
'XBA53163',
'XBA53164',
'XBA53165',
'XBA53166',
'XBA53167',
'XBA53168',
'XBA53169',
'XBA53170',
'XBA53171',
'XBA53172',
'XBA53173',
'XBA53174',
'XBA53175',
'XBA53176',
'XBA53177',
'XBA53178',
'XBA53179',
'XBA53180',
'XBA53181',
'XBA53182',
'XBA53183',
'XBA53184',
'XBA53185',
'XBA53186',
'XBA53187',
'XBA53188',
'XBA53189',
'XBA53190',
'XBA54001',
'XBA54002',
'XBA54003',
'XBA54004',
'XBA54005',
'XBA54006',
'XBA54007',
'XBA54008',
'XBA54009',
'XBA54010',
'XBA54011',
'XBA54012',
'XBA54013',
'XBA54014',
'XBA54015',
'XBA54016',
'XBA54017',
'XBA54018',
'XBA54019',
'XBA54020',
'XBA54021',
'XBA54022',
'XBA54023',
'XBA54024',
'XBA54025',
'XBA54026',
'XBA54027',
'XBA54028',
'XBA54029',
'XBA54030',
'XBA54031',
'XBA54032',
'XBA54033',
'XBA54034',
'XBA54035',
'XBA54036',
'XBA54037',
'XBA54038',
'XBA54039',
'XBA54040',
'XBA54041',
'XBA54042',
'XBA54043',
'XBA54044',
'XBA54045',
'XBA54046',
'XBA54047',
'XBA54048',
'XBA54049',
'XBA54050',
'XBA54051',
'XBA54052',
'XBA54053',
'XBA54054',
'XBA54055',
'XBA54056',
'XBA54057',
'XBA54058',
'XBA54059',
'XBA54060',
'XBA54061',
'XBA54062',
'XBA54063',
'XBA54064',
'XBA54065',
'XBA54066',
'XBA54067',
'XBA54068',
'XBA54069',
'XBA54070',
'XBA54071',
'XBA54072',
'XBA54073',
'XBA54074',
'XBA54075',
'XBA54076',
'XBA54077',
'XBA54078',
'XBA54079',
'XBA54080',
'XBA54081',
'XBA54082',
'XBA54083',
'XBA54084',
'XBA54085',
'XBA54086',
'XBA54087',
'XBA54088',
'XBA54089',
'XBA54090',
'XBA54091',
'XBA54092',
'XBA54093',
'XBA54094',
'XBA54095',
'XBA54096',
'XBA54097',
'XBA54098',
'XBA54099',
'XBA54100',
'XBA54101',
'XBA54102',
'XBA54103',
'XBA54104',
'XBA54105',
'XBA54106',
'XBA54107',
'XBA54108',
'XBA54109',
'XBA54110',
'XBA54111',
'XBA54112',
'XBA54113',
'XBA54114',
'XBA54115',
'XBA54116',
'XBA54117',
'XBA54118',
'XBA54119',
'XBA54120',
'XBA54121',
'XBA54122',
'XBA54123',
'XBA54124',
'XBA54125',
'XBA54126',
'XBA54127',
'XBA54128',
'XBA54129',
'XBA54130',
'XBA54131',
'XBA54132',
'XBA54133',
'XBA54134',
'XBA54135',
'XBA54136',
'XBA55001',
'XBA55002',
'XBA55003',
'XBA55004',
'XBA55005',
'XBA55006',
'XBA55007',
'XBA55008',
'XBA55009',
'XBA55010',
'XBA55011',
'XBA55012',
'XBA55013',
'XBA55014',
'XBA55015',
'XBA55016',
'XBA55017',
'XBA55018',
'XBA55019',
'XBA55020',
'XBA55021',
'XBA55022',
'XBA55023',
'XBA55024',
'XBA55025',
'XBA55026',
'XBA55027',
'XBA55028',
'XBA55029',
'XBA55030',
'XBA55031',
'XBA55032',
'XBA55033',
'XBA55034',
'XBA55035',
'XBA55036',
'XBA55037',
'XBA55038',
'XBA55039',
'XBA55040',
'XBA55041',
'XBA55042',
'XBA55043',
'XBA56001',
'XBA56002',
'XBA56003',
'XBA56004',
'XBA56005',
'XBA56006',
'XBA56007',
'XBA56008',
'XBA56009',
'XBA56010',
'XBA56011',
'XBA56012',
'XBA56013',
'XBA56014',
'XBA56015',
'XBA56016',
'XBA56017',
'XBA56018',
'XBA56019',
'XBA56020',
'XBA56021',
'XBA56022',
'XBA56023',
'XBA56024',
'XBA56025',
'XBA56026',
'XBA56027',
'XBA57001',
'XBA59001',
'XBA60001',
'XBA60002',
'XBA60003',
'XBA60004',
'XBA60005',
'XBA60006',
'XBA60007',
'XBA60008',
'XBA60009',
'XBA60010',
'XBA60011',
'XBA60012',
'XBA60013',
'XBA60014',
'XBA60015',
'XBA60016',
'XBA60017',
'XBA60018',
'XBA60019',
'XBA60020',
'XBA60021',
'XBA60022',
'XBA60023',
'XBA60024',
'XBA60025',
'XBA60026',
'XBA60027',
'XBA60028',
'XBA60029',
'XBA60030',
'XBA60031',
'XBA61001',
'XBA64001',
'XBA67001',
'XBA67002',
'XBA67003',
'XBA67004',
'XBA67005',
'XBA67006',
'XBA67007',
'XBA67008',
'XBA67009',
'XBA67010',
'XBA67011',
'XBA67012',
'XBA67013',
'XBA67014',
'XBA67015',
'XBA67016',
'XBA67017',
'XBA67018',
'XBA67019',
'XBA67020',
'XBA67021',
'XBA67022',
'XBA67023',
'XBA67024',
'XBA67025',
'XBA67026',
'XBA67027',
'XBA67028',
'XBA67029',
'XBA67030',
'XBA67031',
'XBA67032',
'XBA67033',
'XBA67034',
'XBA67035',
'XBA67036',
'XBA67037',
'XBA67038',
'XBA67039',
'XBA67040',
'XBA67041',
'XBA67042',
'XBA67043',
'XBA67044',
'XBA67045',
'XBA67046',
'XBA67047',
'XBA67048',
'XBA67049',
'XBA67050',
'XBA67051',
'XBA67052',
'XBA67053',
'XBA67054',
'XBA67055',
'XBA67056',
'XBA67057',
'XBA67058',
'XBA67059',
'XBA67060',
'XBA67061',
'XBA67062',
'XBA67063',
'XBA67064',
'XBA67065',
'XBA67066',
'XBA67067',
'XBA68001',
'XBA68002',
'XBA68003',
'XBA68004',
'XBA68005',
'XBA68006',
'XBA68007',
'XBA68008',
'XBA68009',
'XBA68010',
'XBA68011',
'XBA68012',
'XBA68013',
'XBA68014',
'XBA68015',
'XBA68016',
'XBA68017',
'XBA68018',
'XBA68019',
'XBA68020',
'XBA68021',
'XBA68022',
'XBA68023',
'XBA68024',
'XBA68025'
)
order by ts.db
;
xxx
select dbname, count(*), sum(float(space) * 1024),
min(updatestatstime), max(updatestatstime)
from sysIbm.tableSpaceStats
where dbName in ('XBA67057',
'XBA67058')
group by dbName
order by dbName
;
SELECT DB_NAME dbName, count(*), sum(float(HI_U_RBA)) tsAdm09
from oa1p.TADM09A1
where datum = current date - 1 day
and db_name in ('XBA67057',
'XBA67058')
group by DB_NAME
order by DBNAME
;
select dbname, count(*), sum(spacef), min(statstime), max(statstime)
from sysibm.systablepart
where dbName in ('XBA67057',
'XBA67058')
group by dbName
order by dbName