zOs/SQL/CATPART

-- list tables and partition keys in a recursive sql
--     for ix- and tb-based partitioning
--     paCols = string with column Names
--     paNos = string with column Numbers
with t (cr, tb, partitions, paKeys, paKi,
             laKy, paCols, paNos, ixCr, ix ) as
(
  select t.creator, t.name, partitions,
       case when partitions = 0 then 0
            when partKeyColNum > 0 then partKeyColNum
                                   else 999999
       end,
       case when partitions = 0 then 'no'
            when partKeyColNum > 0 then 'tb'
            when partKeyColNum = 0 then 'ix'
            else '??'
       --   else 'p' || strip(char(partitions))
       --     || 'c' || strip(char(partKeyColNum))
       end ,
       case when partitions > 0 and partkeyColNum <= 0 then -1 else 0
       end,
       varchar('', 1000), varchar('', 1000), t.dbName, t.tsName
    from sysibm.sysTables t
      join sysibm.sysTablespace s
        on t.dbName = s.dbName and t.tsName = s.name
    where t.creator = 'A540769' and t.type = 'T' -- and s.partitions>0
  union all select cr, tb, partitions, i.colCount, paKi, laKy+1,
          paCols, paNos, i.creator, i.name
      from t, sysibm.sysIndexes i
      where t.laKy <= -1 and (i.creator, i.name) in
               (select ixCreator, ixName
                    from sysibm.sysTablePart p
                    where p.dbName = t.ixCr and p.tsName = t.ix
                        and p.partition = 1
               )
  union all select cr, tb, partitions, paKeys, paKi, laKy+1,
          paCols || strip(c.name) || ' ' || c.partkey_ordering ||',',
          paNos || strip(char(c.colno)) || c.partkey_ordering,
          ixCr, ix
      from t join sysibm.sysColumns c
      on t.cr = c.tbCreator and t.tb = c.tbName
          and c.partKey_colSeq = laKy+1
      where laKy < paKeys  and paKi = 'tb'
  union all select cr, tb, partitions, paKeys, paKi, laKy+1,
          paCols || strip(k.colName) || ' ' || k.ordering ||',',
          paNos || strip(char(k.colno)) || k.ordering,
          ixCr, ix
      from t join sysibm.sysKeys k
      on t.ixCr = k.ixCreator and t.ix = k.ixName
          and k.colSeq = laKy+1
      where laKy < paKeys  and paKi = 'ix'
)
select * from t
    where laKy = paKeys
    order by cr, tb
;;;
-- list tables and partition keys
--     for ix- and tb-based partitioning
select substr(strip(t.creator) || '.' || strip(t.Name) , 1, 20),
       partitions, partKeyColNum,
       case when partitions = 0 then 'noPa'
            when partKeyColNum > 0 then 'tbPa'
            when partKeyColNum = 0 then 'ixPa'
            else '???'
       --   else 'p' || strip(char(partitions))
       --     || 'c' || strip(char(partKeyColNum))
            end,
       substr(c.name, 1, 12), c.partkey_colSeq, c.partkey_ordering,
       substr(strip(ixCr) || '.' || strip(ix), 1, 20) "paIx",
       substr(k.colName, 1, 12), k.colSeq, k.ordering
    from sysibm.sysTables t
      join sysibm.sysTablespace s
        on t.dbName = s.dbName and t.tsName = s.name
      join (select dbName, tsName,
                    min(ixCreator) ixCr, min(ixName) ix
                from sysibm.sysTablePart
                group by dbName, tsName
           ) p
        on t.dbName = p.dbName and t.tsName = p.tsName
      left join sysibm.sysColumns c
        on c.tbcreator = t.creator
           and c.tbName = t.name    and c.partkey_colSeq <> 0
      left join sysibm.sysKeys k
        on k.ixcreator = p.ixCr and k.ixName = p.ix
    where t.creator = 'A540769' and t.type = 'T'
    order by t.creator, t.name, c.partKey_colSeq, k.colSeq
 ;;;
select dbName, Name, spacef
    from sysibm.sysTableSpace
    where partitions = 0
    order by spacef desc
    fetch first 100 rows only
    with ur
;   ;
x
select dbName, tsName, partition, limitkey, limitKey_internal
    from sysibm.sysTablePart p
    where -- dbName = 'NI02A1Z'    and tsName = 'A350A'
             partition > 0
             and (limitkey = '' or limitkey = '0')
    order by partition
    with ur
;   ;
select char(p.dbName, 8), char(p.tsName, 8),
       p.partition,  char(t.name,22), p.cardf,
       s.partitions, s.nTables
    from sysibm.sysTablePart p, sysibm.sysTablespace s,
         sysibm.sysTables t
    where s.dbName = p.dbName and s.name = p.tsName
        and t.dbName = p.dbName and t.tsName = p.tsName and t.type='T'
        and s.dbName in ('TSTNAKAL', 'DA540769')
    order by p.dbName, p.tsName, p.partition, t.name