DB2 z/OS   DB2 LUW
z/OS CICS VTAM

DB2 z/OS Info

Home


Sonstige Beispiele



MQT :

CREATE TABLE TESTMQT1
  AS (SELECT CREATOR, NAME, TYPE
    , CASE WHEN TYPE    = 'A' THEN 'ALIAS'
           WHEN TYPE    = 'G' THEN 'CREATED GLOBAL TEMPORARY TABLE'
           WHEN TYPE    = 'M' THEN 'MATERIALIZED QUERY TABLE'
           WHEN TYPE    = 'T' THEN 'TABLE'
           WHEN TYPE    = 'V' THEN 'VIEW'
           WHEN TYPE    = 'X' THEN 'AUXILIARY TABLE'
           ELSE 'UNKNOWN TYPE' END AS TYPE_DESC
       FROM SYSIBM.SYSTABLES
     )
DATA INITIALLY DEFERRED REFRESH DEFERRED
;
REFRESH TABLE TESTMQT1 ;        


Kleine Query um INCLUDES fuer listdef zu erzeugen :


SELECT DISTINCT   '    INCLUDE  TABLESPACES TABLESPACE '
               CONCAT RTRIM(DBNAME) CONCAT '.' CONCAT RTRIM(TSNAME)
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T'
AND   DBNAME = 'DSNDB06'
FOR FETCH ONLY
WITH UR
;



Aendern WLM Environment

SELECT 'ALTER PROCEDURE '
CONCAT RTRIM(SCHEMA)
CONCAT '.'
CONCAT RTRIM(NAME)
CONCAT ' WLM ENVIRONMENT '<NEW_WLM_ENVIRONMENT>'  ;'
FROM SYSIBM.SYSROUTINES
WHERE ROUTINETYPE = 'P'
AND WLM_ENVIRONMENT = '<OLD_WLM_ENVIRONMENT>'
--   FETCH FIRST  1000 ROWS ONLY
FOR FETCH ONLY
WITH UR
;

Ermitteln VSAM Dataset eines Tablespaces

SELECT  RTRIM(TP.VCATNAME)
CONCAT '.'
CONCAT 'DSNDBC'
CONCAT '.'
CONCAT RTRIM(TS.DBNAME)
CONCAT '.'
CONCAT RTRIM(TS.NAME)
CONCAT '.'
CONCAT RTRIM(TP.IPREFIX)
CONCAT CHAR(SUBSTR(DIGITS(INT(TS.INSTANCE)), 7, 4), 4)
CONCAT '.'
CONCAT 'A'
CONCAT    CASE WHEN TP.PARTITION = 0 THEN '001'
               ELSE CHAR(SUBSTR(DIGITS(INT(TP.PARTITION)), 8, 3), 3)
           END
AS VSAM
FROM SYSIBM.SYSTABLESPACE  TS
INNER JOIN SYSIBM.SYSTABLEPART TP
ON  TS.DBNAME    = TP.DBNAME
AND TS.NAME      = TP.TSNAME

Ermitteln VSAM Dataset eines Index



SELECT  RTRIM(IP.VCATNAME)
CONCAT '.'
CONCAT 'DSNDBC'
CONCAT '.'
CONCAT RTRIM(IX.DBNAME)
CONCAT '.'
CONCAT RTRIM(IX.INDEXSPACE)
CONCAT '.'
CONCAT 'I0001'
CONCAT '.'
CONCAT 'A'
CONCAT    CASE WHEN IP.PARTITION = 0 THEN '001'
               ELSE CHAR(SUBSTR(DIGITS(INT(IP.PARTITION)), 8, 3), 3)
           END
AS VSAM
FROM SYSIBM.SYSINDEXES     IX
INNER JOIN SYSIBM.SYSINDEXPART IP
ON  IX.CREATOR   = IP.IXCREATOR
AND IX.NAME      = IP.IXNAME

Modify Recovery  : ich will mindestens 3 Fullcopys halten

WITH MODTAB  (RN, PN, DBNAME, TSNAME
                             , DSNUM, ICTYPE, FILESEQNO
                             , DEVTYPE, DSNAME, ICDATE
                             , TIMESTAMP, DSVOLSER
                            )
         AS (SELECT ROWNUMBER() OVER(ORDER BY  DBNAME
                                             , TSNAME
                                             , TIMESTAMP DESC) AS RN
                 ,  ROWNUMBER() OVER(PARTITION BY DBNAME
                                                , TSNAME) AS PN
                 ,  DBNAME
                 ,  TSNAME
                 ,  DSNUM
                 ,  ICTYPE
                 ,  FILESEQNO
                 ,  DEVTYPE
                 ,  DSNAME
                 ,  '20' CONCAT CHAR(ICDATE) AS ICDATE
                 ,  TIMESTAMP
                 ,  DSVOLSER
             FROM SYSIBM.SYSCOPY
             WHERE ICTYPE = 'F'
             )
SELECT '   MODIFY RECOVERY TABLESPACE '
CONCAT RTRIM(DBNAME) CONCAT '.' CONCAT RTRIM(TSNAME)
CONCAT ' DELETE DATE(' CONCAT ICDATE CONCAT ')'
FROM MODTAB
WHERE PN = 3
ORDER BY DBNAME, TSNAME, PN DESC
FOR FETCH ONLY  WITH UR
 ;


Tablespaces ohne Imagecopy :  


SELECT TS.DBNAME, TS.NAME
FROM SYSIBM.SYSTABLESPACE        TS
LEFT OUTER JOIN SYSIBM.SYSCOPY   IC
ON TS.DBNAME   = IC.DBNAME
AND TS.NAME   = IC.TSNAME
AND IC.ICTYPE   = 'F'
WHERE IC.DBNAME IS NULL
;

SYSLGRNX DB2 z/OS ab V10    


SELECT    SUBSTR(DBNAME, 1, 8)  AS DBNAME
        , SUBSTR(NAME, 1, 8)    AS TSNAME
        , HEX(LGRDBID)  AS LGRDBID
        , HEX(LGRPSID)  AS LGRPSID
        , DATE(
          SUBSTR(LGRUCDT, 3, 2) !! '.'   !!
          SUBSTR(LGRUCDT, 1, 2) !! '.20' !!
          SUBSTR(LGRUCDT, 5, 2)
          )
          AS LGRUCDT
        , SUBSTR(LGRUCTM, 1, 2) !! ':' !!
          SUBSTR(LGRUCTM, 3, 2) !! ':' !!
          SUBSTR(LGRUCTM, 5, 2) !! '.' !!
          SUBSTR(LGRUCTM, 7, 2)
          AS LGRUCTM
        , HEX(LGRSRBA)   AS LGRSRBA
        , HEX(LGRSPBA)   AS LGRSPBA
        , HEX(LGRPART)   AS LGRPART
        , HEX(LGRSLRSN)  AS LGRSLRSN
        , HEX(LGRELRSN)  AS LGRELRSN
        , HEX(LGRMEMB)   AS LGRMEMB
FROM SYSIBM.SYSLGRNX                   LG
INNER JOIN SYSIBM.SYSTABLESPACE        TS
ON  HEX(LG.LGRDBID)    = HEX(TS.DBID)
AND HEX(LG.LGRPSID)    = HEX(TS.PSID)
WHERE TS.DBNAME        = '#DBNAME'
AND   TS.NAME          = '#TSNAME'
AND       DATE(
          SUBSTR(LG.LGRUCDT, 3, 2) !! '.'   !!
          SUBSTR(LG.LGRUCDT, 1, 2) !! '.20' !!
          SUBSTR(LG.LGRUCDT, 5, 2)
          )   = CURRENT DATE
ORDER BY
          DATE(
          SUBSTR(LG.LGRUCDT, 3, 2) !! '.'   !!
          SUBSTR(LG.LGRUCDT, 1, 2) !! '.20' !!
          SUBSTR(LG.LGRUCDT, 5, 2)
          )
DESC
  , TIME( SUBSTR(LG.LGRUCTM, 1, 2) !! ':' !!
          SUBSTR(LG.LGRUCTM, 3, 2) !! ':' !!
          SUBSTR(LG.LGRUCTM, 5, 2)
        ) DESC
--   FETCH FIRST   100   ROWS ONLY
FOR FETCH ONLY
WITH UR
 ;


Reorg DSNDB06 (kann mit Dynamsql ausgeführt werden (wwr))


 WITH SSINF (SSID, DSHLQ, UNIT) AS
      (
       SELECT 'DB2A'
            , 'MGDB05.RO.'
            , 'SYSDA'
       FROM SYSIBM.SYSDUMMY1
      )
    , TSTAB (SORT, DBNAME, TSNAME, TYPE ) AS
      (
       SELECT DISTINCT 1 , DBNAME, TSNAME, TYPE
       FROM SYSIBM.SYSTABLES TB
       WHERE TB.CREATOR = 'SYSIBM'
       AND   TB.DBNAME  = 'DSNDB06'
       AND TB.TSNAME  <> 'SYSCOPY'
       AND TB.TSNAME  <> 'SYSRTSTS'
       AND TB.TYPE IN('T','X')
       UNION  SELECT 2,  'DSNDB01', 'SCT02', ' '
              FROM SYSIBM.SYSDUMMY1
       UNION  SELECT 3,  'DSNDB01', 'SPT01', ' '
              FROM SYSIBM.SYSDUMMY1
       UNION  SELECT 4,  'DSNDB01', 'DBD01', ' '
              FROM SYSIBM.SYSDUMMY1
       UNION  SELECT 5,  'DSNDB06', 'SYSCOPY', ' '
              FROM SYSIBM.SYSDUMMY1
      )
 SELECT DISTINCT
    '// IF (RC LT 5 AND ABEND = FALSE) THEN  '
 ,  '//* REORG ORDER ' !! CHAR(SORT)
 ,  '//' !! RTRIM(TSNAME) !! ' EXEC PGM=DSNUTILB,REGION=30M,         '
 ,  '//         PARM=(' !! RTRIM(SSID) !!',''REORG.'
     !! RTRIM(TSNAME) !! ''',RESTART(PHASE))              '
 ,  '//SYSPRINT  DD SYSOUT=*                                         '
 ,  '//UTPRINT   DD SYSOUT=*                                         '
 ,  '//SYSUT1    DD DSN='!!DSHLQ !! RTRIM(TSNAME) !!'.SYSUT1,'
 ,  '//         DISP=(NEW,CATLG,CATLG),SPACE=(CYL,(500,500)),UNIT=SYSDA'
 ,  '//SORTOUT   DD DSN='!!DSHLQ !! RTRIM(TSNAME) !!'.SORTOUT,'
 ,  '//         DISP=(NEW,CATLG,CATLG),SPACE=(CYL,(500,500)),UNIT=SYSDA'
 ,  '//SYSREC    DD DSN='!!DSHLQ !! RTRIM(TSNAME) !!'.SYSREC,'
 ,  '//         DISP=(NEW,CATLG,CATLG),SPACE=(CYL,(500,500)),UNIT=SYSDA'
 ,  '//SYSERR    DD DSN='!!DSHLQ !! RTRIM(TSNAME) !!'.SYSERR,'
 ,  '//         DISP=(NEW,CATLG,CATLG),SPACE=(CYL,(100,100)),UNIT=SYSDA'
,  '//SYSLISTD DD *                                                 '
,  '   LISTDEF ROLIST                                               '
,  '        INCLUDE TABLESPACE '
       !! RTRIM(DBNAME) !! '.' !! RTRIM(TSNAME)
,  '//SYSTEMPL DD *                                                 '
,  '  TEMPLATE UNLDSN  DSN(''&SSID..REO.&DB..&TS..D&JU..T&TI.'')    '
,  '                   DISP(NEW,DELETE,CATLG)                       '
,  '  TEMPLATE SYSCOPY DSN(''&SSID..IC1.&DB..&TS..D&JU..T&TI.'')    '
,  '                   DISP(NEW,CATLG,DELETE)                       '
,  '//SYSIN DD *                                                    '
,  '  REORG TABLESPACE LIST ROLIST                                  '
,  '        LOG       NO                                            '
,  '        SHRLEVEL  REFERENCE                                     '
,    CASE WHEN TYPE = 'X'  THEN '        SORTDATA'
          ELSE '        SORTDATA SORTDEVT  SYSDA SORTNUM 4' END
,  '        UNLDDN    (UNLDSN)                                      '
,  '        UNLOAD    CONTINUE                                      '
,  '        WORKDDN   (SYSUT1,SORTOUT)                              '
,  CASE WHEN TSNAME = 'SYSCOPY' THEN
        '   REPAIR SET TABLESPACE DSNDB06.SYSCOPY  NOCOPYPEND    '
        ELSE ''
    END
,  '                                                                '
,  '  COPY  LIST ROLIST                                             '
,  '        SHRLEVEL  CHANGE                                        '
,  '        FULL YES                                                '
,  '        COPYDDN(SYSCOPY)                                        '
,  '/*                                                              '
,  '//DELETE  EXEC PGM=IDCAMS,COND=(5,LT)                           '
,  '//SYSPRINT  DD SYSOUT=*                                         '
,  '//SYSIN     DD *                                                '
,  '  DELETE ' !! DSHLQ !! RTRIM(TSNAME) !!'.SYSUT1   NONVSAM '
,  '  DELETE ' !! DSHLQ !! RTRIM(TSNAME) !!'.SORTOUT  NONVSAM '
,  '  DELETE ' !! DSHLQ !! RTRIM(TSNAME) !!'.SYSREC   NONVSAM '
,  '  DELETE ' !! DSHLQ !! RTRIM(TSNAME) !!'.SYSERR   NONVSAM '
,  '  SET MAXCC = 0                                                 '
,  '/*                                                              '
,  '// ENDIF '
FROM SSINF, TSTAB
ORDER BY 1
;



FOREIGN KEY ohne Indexes ermitteln 


WITH
  CREATOR_TAB ( CREATOR
              ) AS
              ( SELECT 'XXXXXXXX' FROM SYSIBM.SYSDUMMY1
              )
  , FKTAB     (  AUSWAHL
               , TBCREATOR
               , TBNAME
               , RELNAME
               , COLSEQ
               , COLNAME
               , REFTBCREATOR
               , REFTBNAME
               , IXCREATOR
               , IXNAME
               ) AS
               ( SELECT DISTINCT 'FK  '
                      , FK.CREATOR
                      , FK.TBNAME
                      , FK.RELNAME
                      , FK.COLSEQ
                      , FK.COLNAME
                      , RL.REFTBCREATOR
                      , RL.REFTBNAME
                      , IX.CREATOR
                      , IX.NAME
                  FROM SYSIBM.SYSFOREIGNKEYS        FK
                  INNER JOIN      SYSIBM.SYSRELS    RL
                  ON  FK.CREATOR      =  RL.CREATOR
                  AND FK.TBNAME       =  RL.TBNAME
                  AND FK.RELNAME      =  RL.RELNAME
                  LEFT OUTER JOIN SYSIBM.SYSINDEXES IX
                  ON  RL.CREATOR      =  IX.TBCREATOR
                  AND RL.TBNAME       =  IX.TBNAME
                  LEFT OUTER JOIN SYSIBM.SYSKEYS    KY
                  ON   KY.IXCREATOR    =  IX.CREATOR
                  AND  KY.IXNAME       =  IX.NAME
                  AND KY.COLSEQ        =  FK.COLSEQ
                  AND KY.COLNAME       =  FK.COLNAME
                  WHERE FK.CREATOR    IN (SELECT CREATOR
                                          FROM   CREATOR_TAB
                                         )
                  AND   IX.TBCREATOR IS NOT NULL
                  AND   KY.COLSEQ    IS NOT NULL
                  ORDER BY FK.CREATOR
                         , FK.TBNAME
                         , FK.RELNAME
                         , FK.COLSEQ
                         , RL.REFTBCREATOR
                         , RL.REFTBNAME
               )
  , RELTAB     ( AUSWAHL
               , TBCREATOR
               , TBNAME
               , RELNAME
               , COLSEQ
               , COLNAME
               , REFTBCREATOR
               , REFTBNAME
               , IXCREATOR
               , IXNAME
               ) AS
               ( SELECT DISTINCT 'REL '
                      , FK.CREATOR
                      , FK.TBNAME
                      , FK.RELNAME
                      , FK.COLSEQ
                      , FK.COLNAME
                      , RL.REFTBCREATOR
                      , RL.REFTBNAME
                      , IX.CREATOR
                      , IX.NAME
                  FROM SYSIBM.SYSFOREIGNKEYS        FK
                  INNER JOIN      SYSIBM.SYSRELS    RL
                  ON  FK.CREATOR      =  RL.CREATOR
                  AND FK.TBNAME       =  RL.TBNAME
                  AND FK.RELNAME      =  RL.RELNAME
                  LEFT OUTER JOIN SYSIBM.SYSINDEXES IX
                  ON  RL.REFTBCREATOR =  IX.TBCREATOR
                  AND RL.REFTBNAME    =  IX.TBNAME
                  LEFT OUTER JOIN SYSIBM.SYSKEYS    KY
                  ON   KY.IXCREATOR    =  IX.CREATOR
                  AND  KY.IXNAME       =  IX.NAME
                  AND KY.COLSEQ        =  FK.COLSEQ
                  AND KY.COLNAME       =  FK.COLNAME
                  WHERE FK.CREATOR    IN (SELECT CREATOR
                                          FROM   CREATOR_TAB
                                         )
                  AND   IX.TBCREATOR IS NOT NULL
                  AND   KY.COLSEQ    IS NOT NULL
                  ORDER BY FK.CREATOR
                         , FK.TBNAME
                         , FK.RELNAME
                         , FK.COLSEQ
                         , RL.REFTBCREATOR
                         , RL.REFTBNAME
               )

SELECT FK.CREATOR
     , FK.TBNAME
     , FK.RELNAME
     , FKT.TBNAME
     , RELT.TBNAME
FROM SYSIBM.SYSFOREIGNKEYS        FK

LEFT OUTER JOIN FKTAB             FKT
ON  FK.CREATOR    = FKT.TBCREATOR
AND FK.TBNAME     = FKT.TBNAME
AND FK.RELNAME    = FKT.RELNAME

LEFT OUTER JOIN RELTAB            RELT
ON  FK.CREATOR    = RELT.TBCREATOR
AND FK.TBNAME     = RELT.TBNAME
AND FK.RELNAME    = RELT.RELNAME
WHERE FK.CREATOR    IN (SELECT CREATOR
                        FROM   CREATOR_TAB
                       )
AND (    FKT.TBCREATOR   IS NULL
     OR  RELT.TBCREATOR  IS NULL
    )
ORDER BY FK.CREATOR
       , FK.TBNAME
       , FK.RELNAME
 ;



Besser mit XMLAGG  



SET CURRENT SCHEMA = 'SYSIBM' ;

WITH   CREATOR_TAB ( CREATOR
              ) AS
              ( SELECT 'XXXXXXXX'    FROM SYSIBM.SYSDUMMY1
              )
,  FKTAB (TBCREATOR
         ,TBNAME
         ,RELNAME
         ,FK_COLS
         ,FK_COLS_LEN
          ) AS  (
          SELECT CREATOR
               , TBNAME
               , RELNAME
     ,  REPLACE(
            REPLACE(
            CAST(XMLSERIALIZE(XMLAGG(XMLELEMENT(NAME "COLS",
                                              COLNAME CONCAT ' '
                                            )
                                     ORDER BY  CREATOR
                                             , TBNAME
                                             , RELNAME
                                             , COLSEQ
                            ) AS CLOB(32000)
                        )
                   AS VARCHAR(32000) )
               , '</COLS>','')
               , '<COLS>','')
     ,  LENGTH(TRIM(REPLACE(
            REPLACE(
            CAST(XMLSERIALIZE(XMLAGG(XMLELEMENT(NAME "COLS",
                                              COLNAME CONCAT ' '
                                            )
                                     ORDER BY  CREATOR
                                             , TBNAME
                                             , RELNAME
                                             , COLSEQ
                            ) AS CLOB(32000)
                        )
                   AS VARCHAR(32000) )
               , '</COLS>','')
               , '<COLS>','')
              ) )
       FROM SYSFOREIGNKEYS
       WHERE   CREATOR IN (SELECT CREATOR
                           FROM   CREATOR_TAB
                                         )
       GROUP BY CREATOR
              , TBNAME
              , RELNAME
 )


,  INDEXTAB (TBCREATOR, TBNAME, IXNAME, IXCREATOR, IX_COLS)   AS (
       SELECT  IX.TBCREATOR
             , IX.TBNAME
             , KEY.IXNAME
             , KEY.IXCREATOR
     ,  REPLACE(
            REPLACE(
            CAST(XMLSERIALIZE(XMLAGG(XMLELEMENT(NAME "COLS",
                                          KEY.COLNAME CONCAT ' '
                                            )
                                      ORDER BY IX.TBCREATOR
                                             , IX.TBNAME
                                             , KEY.IXNAME
                                             , KEY.IXCREATOR
                                             , KEY.COLSEQ
                            ) AS CLOB(32000)
                        )
                   AS VARCHAR(32000) )
               , '</COLS>','')
               , '<COLS>','')
      FROM SYSKEYS KEY
      INNER JOIN SYSINDEXES IX
      ON  IX.CREATOR  = KEY.IXCREATOR
      AND IX.NAME     = KEY.IXNAME
      WHERE   TBCREATOR IN (SELECT CREATOR
                            FROM   CREATOR_TAB
                           )
      And Exists (select 1 from SYSFOREIGNKEYS fk
                  Where fk.creator     = ix.tbcreator
                  and  fk.tbname      = ix.tbname
                  )

       GROUP BY IX.TBCREATOR, IX.TBNAME, KEY.IXNAME, KEY.IXCREATOR
       )


SELECT DISTINCT fkt.TBCREATOR
              , fkt.TBNAME
              , fkt.RELNAME
--            , fkt.FK_COLS
,       substr( FK_COLS, 1, 500)
,  FK_COLS_LEN
FROM FKTAB FKT
WHERE NOT EXISTS (SELECT 1
                  FROM  INDEXTAB IXT
                  WHERE FKT.TBCREATOR = IXT.TBCREATOR
                  AND   FKT.TBNAME    = IXT.TBNAME
                  AND FKT.FK_COLS =  SUBSTR(IX_COLS, 1, FK_COLS_LEN)
                  )
ORDER BY 1, 2, 3
--TCH FIRST 100 ROWS
;





Sichern DBIS, PSID, OBID XML und LOB sind nach Basetable Sortiert  


SELECT OBIDINFO  FROM TABLE (
 SELECT
        CHAR(RTRIM(T2.DBNAME) !! '.' !! RTRIM(T2.TSNAME) !! ' ', 20)
    !!  CHAR(' '                                               , 20)
    !!  CHAR(T3.DBID) !! ' '
    !!  CHAR(T1.PSID) !! ' '
    !!  CHAR(T2.OBID) !! ' '
    !!  RTRIM(T2.CREATOR) !! '.' !! RTRIM(T2.NAME) !! ' '
 AS OBIDINFO
 , T2.TYPE
 , T2.DBNAME AS DBNAME
 , T2.TSNAME AS TSNAME
 , ' '       AS AUXDB
 , ' '       AS AUXTS
 FROM SYSIBM.SYSTABLESPACE      T1
 INNER JOIN SYSIBM.SYSTABLES    T2
 ON    T1.DBNAME  = T2.DBNAME
 AND   T1.NAME    = T2.TSNAME
 INNER JOIN SYSIBM.SYSDATABASE  T3
 ON    T1.DBNAME  = T3.NAME
 WHERE T2.TYPE = 'T'
 UNION ALL
 SELECT
        CHAR(RTRIM(BTB.DBNAME) !! '.' !! RTRIM(BTB.TSNAME) !! ' ', 20)
    !!  CHAR(RTRIM(T2.DBNAME) !! '.' !! RTRIM(T2.TSNAME) !! ' ', 20)
    !!  CHAR(T3.DBID) !! ' '
    !!  CHAR(T1.PSID) !! ' '
    !!  RTRIM(T2.CREATOR) !! '.' !! RTRIM(T2.NAME) !! ' '
 AS OBIDINFO
 , T2.TYPE
 , BTB.DBNAME AS DBNAME
 , BTB.TSNAME AS TSNAME
 , T2.DBNAME AS AUXDB
 , T2.TSNAME AS AUXTS
 FROM SYSIBM.SYSTABLESPACE      T1
 INNER JOIN SYSIBM.SYSTABLES    T2
 ON    T1.DBNAME  = T2.DBNAME
 AND   T1.NAME    = T2.TSNAME
 INNER JOIN SYSIBM.SYSDATABASE  T3
 ON    T1.DBNAME  = T3.NAME
 INNER JOIN SYSIBM.SYSAUXRELS    AR
 ON    T2.NAME      = AR.AUXTBNAME
 AND   T2.CREATOR   = AR.AUXTBOWNER
 INNER JOIN SYSIBM.SYSTABLES     BTB
 ON    BTB.NAME     = AR.TBNAME
 AND   BTB.CREATOR  = AR.TBOWNER
 WHERE T2.TYPE = 'X'
 UNION ALL
 SELECT
        CHAR(RTRIM(BTB.DBNAME) !! '.' !! RTRIM(BTB.TSNAME) !! ' ', 20)
    !!  CHAR(RTRIM(T2.DBNAME) !! '.' !! RTRIM(T2.TSNAME) !! ' ', 20)
    !!  CHAR(T3.DBID) !! ' '
    !!  CHAR(T1.PSID) !! ' '
    !!  CHAR(T2.OBID) !! ' '
    !!  RTRIM(T2.CREATOR) !! '.' !! RTRIM(T2.NAME) !! ' '
 AS OBIDINFO
 , T2.TYPE
 , BTB.DBNAME AS DBNAME
 , BTB.TSNAME AS TSNAME
 , T2.DBNAME AS AUXDB
 , T2.TSNAME AS AUXTS
 FROM SYSIBM.SYSTABLESPACE      T1
 INNER JOIN SYSIBM.SYSTABLES    T2
 ON    T1.DBNAME  = T2.DBNAME
 AND   T1.NAME    = T2.TSNAME
 INNER JOIN SYSIBM.SYSDATABASE  T3
 ON    T1.DBNAME  = T3.NAME
 INNER JOIN SYSIBM.SYSXMLRELS    AR
 ON    T2.NAME      = AR.XMLTBNAME
 AND   T2.CREATOR   = AR.XMLTBOWNER
 INNER JOIN SYSIBM.SYSTABLES     BTB
 ON    BTB.NAME     = AR.TBNAME
 AND   BTB.CREATOR  = AR.TBOWNER
 WHERE T2.TYPE = 'P'
 ) TSNAME
 ORDER BY DBNAME, TSNAME, AUXDB, AUXTS
 ;




Free Packages   


WITH PG ( ON
, PN
, LOCATION
, COLLID
, NAME
, VERSION
, VALID
, OPERATIVE
, TIMESTAMP
, BINDTIME
)
AS (SELECT ROWNUMBER() OVER(ORDER BY LOCATION
, COLLID
, NAME
, TIMESTAMP DESC
)
, ROWNUMBER() OVER(PARTITION BY LOCATION
, COLLID
, NAME
)
, LOCATION
, COLLID
, NAME
, VERSION
, VALID
, OPERATIVE
, TIMESTAMP
, BINDTIME
FROM SYSIBM.SYSPACKAGE PG
)

SELECT ' FREE PACKAGE ('
!! CASE WHEN LOCATION = '' THEN ''
ELSE RTRIM(LOCATION) !! '.' END
!! RTRIM(COLLID)
!! '.'
!! RTRIM(NAME)
!! CASE WHEN VERSION = '' THEN ' ) '
ELSE '.(' !! RTRIM(VERSION) !! ')) '
END AS REBIND
FROM PG
WHERE PN > 5
AND VERSION <> ''
--AND (VALID = 'N' OR OPERATIVE = 'N' )
ORDER BY LOCATION
, COLLID
, NAME
, PN
FOR FETCH ONLY WITH UR
;