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
;