zOs/SQL/DIRLGRNO

-- logRange orphans
WITH G AS
(
  SELECT OA1P.FQZC2I2(LGRDBID)DBID
     , OA1P.FQZC2I2(LGRPSID) PSID
     , count(distinct LGRPART) PArts
     , COUNT(*) CNT
     , MAX(TIMESTAMP(TRANSLATE('20YZ-MN-DE-', LGRUCDT, 'MNDEYZ')
           || TRANSLATE('HI.MN.ST.UV', LGRUCTM, 'HIMNSTUV'))) START
     , MAX(TIMESTAMP(SUBSTR(LGRSLRSN, 2, 8))) SLRSN
    FROM SYSIBM.SYSLGRNX
    GROUP BY LGRDBID, LGRPSID
)
SELECT substr(value(s.DBNAME, d.name), 1, 8) DB
     , substr(s.NAME, 1, 8) TS
     , g.dbid, g.psid
     , Parts, cnt
     , VALUE(START, '1111-11-11-11.11.11') START
     , VALUE(SLRSN, '1111-11-11-11.11.11') SLRSN
   FROM G
     left JOIN SYSIBM.SYSTABLESPACE S
          ON G.DBID = S.DBID AND G.PSID = S.PSID
     left JOIN SYSIBM.SYSDatabase d
          ON G.DBID = d.DBID AND s.dbname is null
     order by value(s.dbName, d.name, ' ' || g.dbid)
              , value(s.name, ' ' || g.psid)
WITH UR