zOs/SQL/CANTDOM

--   leere / nichtLeere Tables pro domain
--
WITH D (DOM, GB) AS
(
          SELECT 'AOC', 'BQ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'AOC', 'DF' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'AOC', 'EV' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'AOC', 'HB' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'AOC', 'NF' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'AOC', 'NL' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'AOC', 'UB' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'AT' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'AU' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'BP' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'BU' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'BW' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'DB' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'EL' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'EW' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'IT' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'KD' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'KE' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'KS' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'LO' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'ME' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'MT' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'NG' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'NJ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'OE' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'OR' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'OX' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'PL' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'SC' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'SE' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'TA' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'YP' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BAS', 'YY' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'BF' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'BG' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'DA' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'DG' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'DM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'ER' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'KO' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'LS' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'RB' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'RF' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'WM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CDY', 'WP' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CHA', 'BT' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CHA', 'CO' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CHA', 'SA' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CHA', 'SX' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CHA', 'VT' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'COA', 'CE' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'COA', 'CW' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'COA', 'DW' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'COA', 'WA' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'COA', 'WG' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'COA', 'WK' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'COA', 'WU' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'COA', 'WV' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'COA', 'WW' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'COA', 'WX' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'COA', 'WY' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRE', 'LC' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRM', 'VP' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRM', 'WR' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'AP' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'CJ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'CR' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'CT' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'CY' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'CZ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'EB' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'EC' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'EM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'EQ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'EX' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'GA' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'HY' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'JA' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'JM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'KB' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'KR' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'KX' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'MI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'RA' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'RV' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'TM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'UI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'UU' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CRS', 'XP' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CUS', 'AF' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CUS', 'AN' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CUS', 'CA' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CUS', 'CD' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CUS', 'CI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CUS', 'CU' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CUS', 'HI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CUS', 'OO' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CUS', 'RM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'DO' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'ID' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'OM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'OS' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'PO' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'SY' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'TS' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'VI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'XB' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'XC' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'XI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'XR' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ECM', 'YS' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'FAC', 'FD' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'FAC', 'RP' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'FAC', 'RQ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'FIN', 'AV' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'FIN', 'DI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'FIN', 'FI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'FIN', 'RE' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'FIN', 'VK' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'FIN', 'VV' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'IMA', 'NI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'LOG', 'A1' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'LOG', 'CH' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'LOG', 'NP' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'LOG', 'PA' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'LOG', 'PH' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'LOG', 'PI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'LOG', 'PM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'LOG', 'PX' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'OTM', 'TN' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'OTM', 'TZ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'OTM', 'WB' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'OTM', 'WC' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'OTM', 'WN' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'AS' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'AZ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'CK' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'CV' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'FF' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'GE' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'MC' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'NZ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'SF' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'VD' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'ZD' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'ZE' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PAY', 'ZV' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PRC', 'EF' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PRC', 'EI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'PRC', 'SN' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'RRL', 'EK' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'RRL', 'EU' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'RRL', 'HK' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'RRL', 'HR' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'RRL', 'JR' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'RRL', 'LG' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'RRL', 'MF' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'RRL', 'RX' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'RRL', 'TG' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'RRL', 'US' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SAC', 'BE' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SAC', 'BK' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SAC', 'BS' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SAC', 'BX' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SAC', 'CM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SAC', 'KA' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SAC', 'KC' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SAC', 'KK' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SAC', 'KM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SAC', 'SB' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SAC', 'VR' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'AM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'DE' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'DN' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'DP' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'DT' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'ED' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'FO' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'FZ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'GM' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'LW' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'NO' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'RI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'ST' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'SU' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'SV' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'TP' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'TR' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'WD' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'WF' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'WI' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'WL' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'WQ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SCL', 'WS' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SEC', 'PW' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SSI', 'SW' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'SSI', 'TL' FROM SYSIBM.SYSDUMMY1
)
, T (GB, TBSLe, szLe, tbsVo, szVo, rowVo) AS
(
SELECT LEFT(T.DBNAME,2),
    sum(case when cardf <= 0 then 1 else 0 end),
    SUM(case when cardf <= 0 then max(t.SPACEF, 0) else 0 end),
    sum(case when cardf >  0 then 1 else 0 end),
    SUM(case when cardf >  0 then t.SPACEF else 0 end),
    SUM(case when cardf >  0 then cardf else 0 end)
  FROM SYSIBM.SYSTABLES T
    JOIN SYSIBM.SYSTABLESPACE S
    ON T.DBNAME = S.DBNAME AND T.TSNAME = S.NAME
  GROUP BY LEFT(T.DBNAME,2)
)
SELECT D.DOM, SUM(TBSLe) "le #TB",
         dec(SUM(szLe) / 1024/1024, 8,3)   "le GB",
         SUM(TBSVo) "vo #Tb",
         dec(SUM(szVo) / 1024/1024, 8,3) "vo GB",
         bigInt(SUM(rowVo)) "vo #rows"
  from t
  left join d
  on t.gb = d.gb
  group by dom
  order by dom
  WITH UR