zOs/SQL/CATDB

select dbName, count(*)
    from sysibm.sysTables
    where dbName like 'QZ%'
    group by dbName
    order by dbname
;x;
select type, count(*), min(name), max(name)
    from sysibm.sysdatabase
    group by type
    order by type
;
select type, count(*), min(name), max(name)
    from sysibm.sysdatabase
    where name like 'WK%'
    group by type
    order by type
;
;X; where (name like 'D%' and length(strip(name)) = 8
            and translate(strip(substr(name, 3)), '', '0123456789')=''
          )
;;;
select count(*), cat, min(name), max(name)
    from
      ( select
            case
                when name like 'DSNDB%' then 'dsnDb'
                when name like 'BJ%' and LENGTH(STRIP(name)) = 8
                    then 'bj%'
                when name like 'DB2%' then 'db2%'
                when name like 'DEMO%' then 'demo%'
                when name like 'DGDB%' and LENGTH(STRIP(name)) = 8
                    then 'dgdb%'
                when name like 'DGO%' and LENGTH(STRIP(name)) = 8
                    then 'dgow%'
                when name like 'WKDB%' then 'wkdb%'
                when LENGTH(STRIP(name)) = 7 and right(strip(name), 3)
                              = 'A1A'
                    then '____a1a'
                when length(strip(name)) >= 7 and
                           translate(right(strip(name), 6),
                               '0000000000', '0123456789') = '000000'
                        then 'user'
                else name end cat,
            name
            from sysibm.sysdatabase
      ) c
    group by cat
    order by cat
;
x
select count(*), creator
    from sysibm.systables
    where dbName =    'DGOWA600'
    group by creator
    order by 1 desc
;
select count(*), dbname
    from sysibm.systables
    where dbName like 'DG%'
    group by dbname
    order by 1 desc
;
x
SELECT cTyp,
        count(*) tables,
        count(distinct creator) creators, MIN(creator), MAX(creator),
        MIN(DBNAME) dbName, MAX(DBNAME)
  from
  ( SELECT
        CASE
            when creator = 'SYSIBM' then 'sysibm'
            when dbname like 'DSNDB%'
                then left(lower(dbname), 8)
            when DBNAME LIKE 'BJ%' AND LENGTH(STRIP(DBNAME)) = 8
                then left(dbName, 3) || 'xxxxx'
            when length(strip(creator)) = 7 and
                   translate(substr(creator, 2, 6),
                       '0000000000', '0123456789') = '000000'
                then 'user'
            when creator like 'BMC%' then 'bmc'
            else creator
        end cTyp,
        creator, name, dbname
      FROM SYSIBM.SYSTABLES
      WHERE TYPE = 'T'
  ) s
    GROUP BY cTyp
    ORDER BY cTyp
;
XELECT DISTINCT CREATOR, COUNT(*), COUNT(DISTINCT DBNAME),
                         MIN(DBNAME), MAX(DBNAME)
    FROM SYSIBM.SYSTABLES
    WHERE TYPE = 'T'
        AND NOT (DBNAME LIKE 'BJ%' AND LENGTH(STRIP(DBNAME)) = 8)
    GROUP BY CREATOR
    ORDER BY CREATOR
;
SELECT DISTINCT CREATOR, COUNT(*), COUNT(DISTINCT DBNAME),
                         MIN(DBNAME), MAX(DBNAME)
    FROM SYSIBM.SYSTABLES
    WHERE TYPE = 'T'
        AND NOT (DBNAME LIKE 'BJ%' AND LENGTH(STRIP(DBNAME)) = 8)
    GROUP BY CREATOR
    ORDER BY CREATOR
;
    LEFT(CREATOR, 9)  || LEFT(DBNAME, 9)
    || CHAR(COUNT(*))
    || MIN(LEFT(NAME, 8)) || ' .. ' || MAX(LEFT(NAME, 8))
    FROM SYSIBM.SYSTABLES
    WHERE TYPE = 'T'
        AND NOT (DBNAME LIKE 'BJ%' AND LENGTH(STRIP(DBNAME)) = 8)
    GROUP BY CREATOR, DBNAME
    ORDER BY 1 ASC
;
;
X
SELECT DISTINCT
    LEFT(CREATOR, 9)  || LEFT(DBNAME, 9)
    || CHAR(COUNT(*))
    || MIN(LEFT(NAME, 8)) || ' .. ' || MAX(LEFT(NAME, 8))
    FROM SYSIBM.SYSTABLES
    WHERE TYPE = 'T'
        AND NOT (DBNAME LIKE 'BJ%' AND LENGTH(STRIP(DBNAME)) = 8)
    GROUP BY CREATOR, DBNAME
    ORDER BY 1 ASC
;