zOs/SQL/EW43

set current sqlid = 'S100447';
select substr(strip(dbName) || '.' || strip(tsName), 1, 15), partition,
     row_number() over(partition by dbName, tsName
                       order by partition desc) rn
     from sysibm.systablepart
     where dbName like 'MF%' and partition < 10

;
with ts as
(
  select dbName db, name ts
       , 'ts' ty, dbName qu, name nm, '' err
       , strip(dbName) || '.' || strip(name) pa
       , ts.*
    from sysibm.sysTablespace ts
)
, t as
(
  select dbName db, tsName ts, 't' ty, creator qu, name nm, '' err
       ,  strip(dbName) || '.' || strip(tsName)
         || '.' || strip(creator) || '.' || strip(name) pa
       , tb.*
    from sysibm.sysTables tb
    where type not in ('A', 'V')
)
, v1 (db, ts, ty, qu, nm, pa, lv) as
(
  select db, ts, ty, qu, nm, varchar(pa, 200), 0
    from t
  union all select db, ts, dType, dCreator, dName
       , case when length(pa) < 70 then pa || '.'
                                   else left(pa, 65) || '.....' end
         || strip(dCreator) || '.' || strip(dName)
       , lv+1
    from v1 join sysibm.sysViewDep d
      on d.bCreator = v1.qu and d.bName = v1.nm
    where lv < 1000
)
, v2 as
(
  select row_number() over (partition by qu, nm
                            order by lv, pa) rn
      , v1.*
      from v1
)
select * from v2
    where db like 'VV24%' and ts like 'VDPS168%'
 -- order by qu, nm, lv, pa
;