zOs/SQL/CATTPDSN

-- list all files of a tableSpacePartition:
--     iPrefix I000 or J000
--     partition OR dsnNum in last qualifier
--        if partition >= 1000 swap A TO BCDE in last qualif
--
--     NOT Done: hlq from storageGroup - hard coded, change manually
--
--  S100447.DSLOCATE(DSNAME) to check whether it is present
--
with n as
( select row_number() over () n
      from sysibm.sysColumns
      fetch first 1000 rows only
)
,    d as
(
select 'DBAF.DSNDBC.' || strip(dbName) || '.' || strip(tsName) || '.'
     || iPREFIX || '0001.'
     || translate(strip(char(partition/1000)), 'ABCDEF', '012345')
     || right('00' || strip(char(
            case when partition > 0 then partition
                 when n is not null then n
                 else 1 end)), 3) dsn
     , p.*
    from sysIbm.sysTablePart p left join n
    on n <= dsNum
)
select substr(S100447.DSLOCATE(char(DSN, 44)), 1, 10)
     , dbName, tsName, partition, iPrefix , storType, storName
    from d
    where dbName like 'DA54076%' -- 'VV28A1A%'
    order by dbName, tsName, partition
;; ------------------------------------------------------------------
; to find testCases:
select * from sysibm.sysTablespace
order by partitions desc
fetch first 100 rows only
with ur
;;
select * from sysibm.sysTablePart
order by dsNum      desc
fetch first 100 rows only
with ur
;;