DB2 z/OS   DB2 LUW
z/OS CICS VTAM

DB2 SQL

 Home


-- LAST FULL IMAGE Copy

SELECT IC.DBNAME        AS ICDBNAME
     , IC.TSNAME        AS ICTSNAME
     , IC.DSNAME        AS ICDSNAME
     , IC.TIMESTAMP     AS ICTIMESTAMP
     , IC.ICTYPE        AS ICTYPE
     , IC.FILESEQNO     AS FILESEQNO
FROM SYSIBM.SYSCOPY     IC
INNER JOIN  SYSIBM.SYSTABLES TB
ON   IC.DBNAME  = TB.DBNAME
AND  IC.TSNAME  = TB.TSNAME
AND  'T'        = TB.TYPE
WHERE IC.ICTYPE  = 'F'
AND   IC.ICUNIT  = 'T'
AND TIMESTAMP    = (SELECT MAX(TIMESTAMP)
                    FROM SYSIBM.SYSCOPY ICM
                    WHERE IC.DBNAME   = ICM.DBNAME
                    AND   IC.TSNAME   = ICM.TSNAME
                    AND   IC.ICTYPE   = ICM.ICTYPE
                   )
AND   TB.CREATOR = '<YOURCREATOR>'
GROUP BY IC.DBNAME  
       , IC.TSNAME  
       , IC.DSNAME  
       , IC.TIMESTAMP
       , IC.ICTYPE 
       , IC.FILESEQNO   
ORDER BY IC.DBNAME, IC.TSNAME
 ;

-- SEQUENCES  AUF MAXASSIGNEDVAL VALUE SETZEN
SELECT 'ALTER SEQUENCE '
CONCAT RTRIM(SE.SCHEMA) CONCAT '.' CONCAT RTRIM(SE.NAME)
CONCAT ' RESTART WITH '
CONCAT CASE WHEN SE.MAXASSIGNEDVAL IS NULL THEN
                 RTRIM(CHAR(BIGINT(SE.START)))
            ELSE RTRIM(CHAR(BIGINT(SE.MAXASSIGNEDVAL + SE.INCREMENT)))
       END
CONCAT ' ; '
FROM SYSIBM.SYSSEQUENCES   SE
WHERE  SE.SEQTYPE  = 'S'
AND    SE.SCHEMA   = 'schema'
-- AND    SE.MAXASSIGNEDVAL IS NOT NULL
;
;;
-- IDENTITY COLUMNS AUF MAXASSIGNEDVAL VALUE SETZEN
SELECT 'ALTER TABLE '
CONCAT RTRIM(SD.DCREATOR) !! '.' !! RTRIM(SD.DNAME)
CONCAT ' ALTER COLUMN '  !! RTRIM(SD. DCOLNAME)
CONCAT ' RESTART WITH '
CONCAT CASE WHEN SE.MAXASSIGNEDVAL IS NULL THEN
                 RTRIM(CHAR(BIGINT(SE.START)))
            ELSE RTRIM(CHAR(BIGINT(SE.MAXASSIGNEDVAL + SE.INCREMENT)))
       END
CONCAT ' ; '  AS ALTER
FROM           SYSIBM.SYSSEQUENCESDEP  SD
INNER JOIN     SYSIBM.SYSSEQUENCES     SE
ON    SD.BSEQUENCEID  = SE.SEQUENCEID
WHERE   SE.MAXASSIGNEDVAL IS NOT NULL
AND   SE.SEQTYPE  = 'I'
WHERE SD.DCREATOR       = 'TBCreator';

-- Select statement um sequences wieder neu anzulegen
SELECT 'CREATE SEQUENCE '
CONCAT RTRIM(SE.SCHEMA) CONCAT '.' CONCAT RTRIM(SE.NAME)
     , '       AS ' CONCAT CASE WHEN PRECISION = 5 THEN  'SMALINT  '
                                WHEN PRECISION = 10 THEN 'INTEGER  '
                                WHEN PRECISION = 19 THEN 'BIGINT  '
                                ELSE 'DECIMAL (' CONCAT CHAR(PRECISION) CONCAT ')'
                            END
     , '       START WITH ' CONCAT CHAR(START)
     , '       INCREMENT BY ' CONCAT CHAR(INCREMENT)
     , '       MINVALUE     ' CONCAT CHAR(MINVALUE)
     , '       MAXVALUE     ' CONCAT CHAR(MAXVALUE)
     , CASE WHEN CYCLE = 'N' THEN '       NO CYCLE     '
            ELSE                  '          CYCLE     '
        END
     , '       CACHE        ' CONCAT CHAR(INT(CACHE))
     , CASE WHEN ORDER = 'N' THEN '       NO ORDER     '
            ELSE                  '          ORDER     '
        END
     , '      ;  '
FROM SYSIBM.SYSSEQUENCES   SE
WHERE  SE.SEQTYPE  = 'S'
AND    SE.SCHEMA   = 'schema'
;

-- ALTER INDEX die mehr als 10 extends haben 
SELECT 'ALTER INDEX '
CONCAT RTRIM(CREATOR) CONCAT '.' CONCAT RTRIM(NAME)
CONCAT CASE WHEN PARTITION = 0 THEN ''
            ELSE ' PART ' CONCAT RTRIM(CHAR(INT(PARTITION)))
        END
CONCAT ' PRIQTY ' CONCAT CHAR(INT(PQTY))
CONCAT ' SECQTY ' CONCAT CHAR(INT(SQTY))
CONCAT ' ;'
FROM (
SELECT SUBSTR(IX.CREATOR, 1, 10)  AS CREATOR
,      SUBSTR(IX.NAME   , 1, 10)  AS NAME
,      IXS.EXTENTS AS IXS_EXTENTS
,      IXS.PARTITION AS PARTITION
,      IXP.EXTENTS AS IXP_EXTENTS
,      SUBSTR(RTRIM(IX.DBNAME) CONCAT '.' CONCAT RTRIM(IX.INDEXSPACE)
            , 1, 30) AS SPACE
,      IXS.SPACE      AS PQTY
,      IXP.SPACE / 10 AS SQTY
,      IXS.SPACE AS IXSPACE
FROM SYSIBM.SYSINDEXSPACESTATS    IXS
INNER JOIN  SYSIBM.SYSINDEXES     IX
ON   IXS.DBID      = IX.DBID
AND  IXS.ISOBID    = IX.ISOBID
AND  IXS.ISOBID    = IX.ISOBID
INNER JOIN  SYSIBM.SYSINDEXPART   IXP
ON   IX.CREATOR    = IXP.IXCREATOR
AND  IX.NAME       = IXP.IXNAME
AND  IXS.PARTITION = IXP.PARTITION
WHERE IXS.EXTENTS   > 10
AND   IX.CREATOR    = 'creator'
) T1
;

-- ALTER TABLESPACE die mehr als 20 extends haben 
SELECT 'ALTER TABLESPACE ' CONCAT RTRIM(DBNAME) CONCAT '.'
CONCAT RTRIM(NAME)
CONCAT CASE WHEN PARTITION = 0 THEN ''
            ELSE ' PART ' CONCAT RTRIM(CHAR(INT(PARTITION)))
        END
CONCAT ' PRIQTY ' CONCAT CHAR(INT(PQTY))
CONCAT ' SECQTY ' CONCAT CHAR(INT(SQTY))
CONCAT ' ; '
FROM (
SELECT TS.DBNAME                  AS DBNAME
,      TS.NAME                    AS NAME
,      TSS.PARTITION              AS PARTITION
,      TSS.EXTENTS                AS EXTENTS
,      TSS.SPACE                  AS PQTY
,      TSS.SPACE / 10             AS SQTY
,      TSS.SPACE                  AS SPACE
FROM SYSIBM.SYSTABLESPACESTATS   TSS
INNER JOIN  SYSIBM.SYSTABLESPACE TS
ON   TSS.DBID      = TS.DBID
AND  TSS.PSID      = TS.PSID
INNER JOIN  SYSIBM.SYSTABLES     TB
ON   TS.DBNAME     = TB.DBNAME
AND  TS.NAME       = TB.TSNAME
AND  'T'           = TB.TYPE
INNER JOIN  SYSIBM.SYSTABLEPART  TP
ON   TS.DBNAME     = TP.DBNAME
AND  TS.NAME       = TP.TSNAME
AND  TSS.PARTITION = TP.PARTITION
WHERE TSS.EXTENTS   > 20
AND   TB.CREATOR    = 'creator'
) T1
;


BIND COPY zu einer andern Location

Ersetze <yourloacation>         zur eigenen location  
            <YOURCOLLID>    zur eigenen Collectionid

SELECT 'BIND PACKAGE(<YOURLOCATION>.' !! RTRIM(COLLID) !! ') + '
 ,     '     COPY(' !! RTRIM(COLLID) !! '.' !! RTRIM(NAME) !! ')'
 !!    CASE WHEN  VERSION = '' THEN ' +'
            ELSE  ' COPYVER(' !! RTRIM(VERSION) !! ') +'
        END
,      ' OWNER(' !! RTRIM(OWNER) !! ') +'
,      ' QUALIFIER('!! RTRIM(QUALIFIER) !! ') +'
,      ' VALIDATE(BIND)  +'
,      ' ISOLATION('  !! CASE WHEN ISOLATION = 'R' THEN 'RR'
                              WHEN ISOLATION = 'S' THEN 'CS'
                              WHEN ISOLATION = 'T' THEN 'RS'
                              ELSE                      'UR' END
!!     ')  +'
,      ' SQLERROR('  !! CASE WHEN SQLERROR  = 'C' THEN 'CONTINUE'
                             ELSE                'NOPACKAGE' END
!!     ')  '
!!       CASE WHEN DEFERPREPARE = 'N' THEN ' NODEFER(PREPARE) +'
              WHEN DEFERPREPARE = 'Y' THEN ' DEFER(PREPARE)   +'
              ELSE                         '    + ' END
 ,     'DEGREE(1) '
 !!    CASE WHEN RELEASE = 'C' THEN ' RELEASE(COMMIT)   '
            ELSE                    ' RELEASE(DEALLOCATE)  ' END
 !!    CASE WHEN DYNAMICRULES = 'B'  THEN ' DYNAMICRULES(BIND) + '
            WHEN DYNAMICRULES = 'R'  THEN ' DYNAMICRULES(RUN)  + '
            ELSE                    ' + '                    END
 ,     CASE WHEN KEEPDYNAMIC  = 'N'  THEN ' KEEPDYNAMIC(NO)  + '
            ELSE                          ' KEEPDYNAMIC(YES) + '  END
  ,     CASE WHEN EXPLAIN      = 'N'  THEN ' EXPLAIN(NO)      + '
            ELSE                          ' EXPLAIN(YES)     + '  END
 ,    ' DBPROTOCOL(DRDA)   '
 !!   CASE WHEN ENCODING_CCSID = 0 THEN ' + '
           ELSE 'ENCODING(' !! RTRIM(CHAR(ENCODING_CCSID)) !! ') +'
       END
 ,    CASE WHEN REOPTVAR = 'A' THEN ' REOPT(AUTO)   +'
           WHEN REOPTVAR = 'N' THEN ' REOPT(NONE)   +'
           WHEN REOPTVAR = 'Y' THEN ' REOPT(ALWAYS) +'
           ELSE ' REOPT(ONCE)   '
       END
 ,    CASE WHEN IMMEDWRITE = 'N' THEN ' IMMEDWRITE(NO)   '
           WHEN IMMEDWRITE = 'Y' THEN ' IMMEDWRITE(YES)  '
           WHEN IMMEDWRITE = '1' THEN ' IMMEDWRITE(PH1)  '
           ELSE '              '
       END
!!    CASE WHEN PATHSCHEMAS = '' THEN ' +' ELSE
               ' PATH(' !! RTRIM(PATHSCHEMAS) !! ') + '
       END
 ,    ' CURRENTDATA(NO) ACTION(REPLACE)   '
 ,    '     '
FROM (
      SELECT DISTINCT PG.COLLID
          ,  PG.NAME
          ,  PG.OWNER
          ,  PG.QUALIFIER
          ,  PG.VALIDATE
          ,  PG.ISOLATION
           ,  PG.RELEASE
           ,  PG.EXPLAIN
           ,  PG.QUOTE
           ,  PG.COMMA
           ,  PG.HOSTLANG
           ,  PG.CHARSET
           ,  PG.MIXED
           ,  PG.DEFERPREP
           ,  PG.SQLERROR
           ,  PG.REMOTE
           ,  PG.VERSION
           ,  PG.DEGREE
           ,  PG.PATHSCHEMAS
           ,  PG.DEFERPREPARE
           ,  PG.DBPROTOCOL
           ,  PG.ROUNDING
           ,  PG.DYNAMICRULES
           ,  PG.KEEPDYNAMIC
           ,  PG.ENCODING_CCSID
           ,  PG.REOPTVAR
           ,  PG.IMMEDWRITE
           ,  PG.SYSENTRIES
       FROM SYSIBM.SYSPACKAGE       PG
       WHERE PG.COLLID = '<YOURCOLLID>'
       AND   PG.TYPE   =    ' '
 ) T1
 ;


-- Tabellen ohne Clustering Index

SELECT RTRIM(TB.CREATOR) CONCAT '.' CONCAT TB.NAME   
FROM SYSIBM.SYSTABLES TB 
LEFT OUTER  JOIN SYSIBM.SYSINDEXES IX  
ON  TB.CREATOR =   IX.TBCREATOR  
AND TB.NAME    =   IX.TBNAME   
AND 'Y'        =   IX.CLUSTERING  
WHERE TB.TYPE       = 'T'
AND       TB.Creator    = '<your creator>'
AND        IX.TBCREATOR IS NULL  
; 

-- Prüefen Character auf Numerische Werte 


CREATE FUNCTION CHECK_NUMERIC
( STRING CHAR(250)
, STRING_LENGTH BIGINT
)
RETURNS INTEGER
SPECIFIC CHECK_NUMERIC
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
RETURN
CASE WHEN STRING_LENGTH < 1 THEN 0
WHEN STRING_LENGTH > 250 THEN 0
WHEN TRANSLATE(SUBSTR(STRING, 1, STRING_LENGTH) , '          Y', '1234567890 ') = ' ' THEN 1
                                                                                                                                                           ELSE 0
-- oder so Sonderzeichen !#+- ... werden so aber nicht berücksichtig
WHEN UPPER(SUBSTR(STRING, 1, STRING_LENGTH) ) =  LOWER(SUBSTR(STRING, 1, STRING_LENGTH) ) THEN  1
                                                                                                                                                                                            ELSE 0

END
;

SELECT CHECK_NUMERIC('123456', 6)
FROM SYSIBM.SYSDUMMY1
;


Alle Tabellen mit Foreign Key einer Tabelle ermitteln (zOS)

WITH TABLENAME (CREATOR, NAME) AS (
      SELECT '<YourCreator>', '<YourTBName>%'    FROM SYSIBM.SYSDUMMYU
      )
, FK_TABLES (LEVEL, PKSCHEMA, PKNAME
              , FKSCHEMA, FKNAME) AS (
                SELECT 0
                     , REF.CREATOR
                     , REF.TBNAME
                     , REF.REFTBCREATOR
                     , REF.REFTBNAME
                 FROM SYSIBM.SYSRELS REF
                    , TABLENAME TBN
                WHERE REF.REFTBCREATOR LIKE  TBN.CREATOR
                AND   REF.REFTBNAME LIKE TBN.NAME
                UNION ALL
                SELECT P.LEVEL + 1
                     , R.CREATOR
                     , R.TBNAME
                     , R.REFTBCREATOR
                     , R.REFTBNAME
                 FROM SYSIBM.SYSRELS R
                    , FK_TABLES P
                WHERE P.LEVEL  < 10
                AND   P.PKSCHEMA = R.REFTBCREATOR
                AND   P.PKNAME   = R.REFTBNAME
                )
, SORTTB  AS( SELECT *
              FROM    FK_TABLES
              ORDER BY LEVEL, FKSCHEMA, FKNAME  )
SELECT  DISTINCT
        FKSCHEMA, FKNAME
      , PKSCHEMA , PKNAME
   FROM SORTTB
;