DB2 z/OS   DB2 LUW
z/OS CICS VTAM

DB2 LUW  

Home
 

Beispiel zum Komprimieren mehrerer Tabellen und Indexes:

select 'Alter Table '
concat RTrim(TB.Creator)
concat '.'
concat TB.Name
concat ' COMPRESS YES ;'
from Sysibm.Systables  TB
Where TB.CREATOR LIKE '<YOUR CREATOR>'
For Fetch Only
With Ur
;

select 'Alter Index '
concat RTrim(IX.Creator)
concat '.'
concat IX.Name
concat ' COMPRESS YES ;'
from       Sysibm.Systables  TB
Inner Join Sysibm.Sysindexes IX
on IX.TBCreator = TB.Creator
And IX.TBName   = TB.Name
Where TB.CREATOR LIKE '<YOUR CREATOR>'
For fetch only
with ur
;


Abfragen welche Tabellen komprimiert sind :

Select creator, name, compression
        from sysibm.systables
        where compression <> 'N'

    ODER SO

select "TABSCHEMA", "TABNAME", "DBPARTITIONNUM", "DATA_PARTITION_ID",
    "COMPRESS_ATTR", "DICT_BUILDER", "DICT_BUILD_TIMESTAMP",
    "COMPRESS_DICT_SIZE", "EXPAND_DICT_SIZE", "ROWS_SAMPLED",
    "PAGES_SAVED_PERCENT", "BYTES_SAVED_PERCENT", "AVG_COMPRESS_REC_LENGTH",
    "OBJECT_TYPE"
  from "SYSIBMADM"."ADMINTABCOMPRESSINFO"
Where COMPRESS_ATTR = 'Y'
and OBJECT_TYPE = 'DATA'
and DICT_BUILD_TIMESTAMP is not null


Abfragen welche Indexes komprimiert sind :

select creator, name, COMPRESSION 
from sysibm.sysindexes
where
COMPRESSION  = 'Y'
;


Informationen über Index compressen  :


Select INDSCHEMA    
    ,  INDNAME    
    ,  TABSCHEMA    
    ,  TABNAME    
    ,  DBPARTITIONNUM    
    ,  IID    
    ,  DATAPARTITIONID    
    ,  COMPRESS_ATTR    
    ,  INDEX_COMPRESSED
    ,  PCT_PAGES_SAVED
FROM TABLE(sysproc.admin_get_index_compress_info('', '', '', -2, -2)) AS t
where COMPRESS_ATTR = 'Y'
;

SELECT STATEMENT FÜR REORG TABLE (Creator = SYSIBM)

SELECT DISTINCT 'REORG TABLE ' CONCAT RTRIM (CREATOR) CONCAT '.' CONCAT RTRIM (NAME)
       CONCAT CASE
                 WHEN IXNAME IS NOT NULL THEN ' INDEX ' CONCAT IXNAME
                 ELSE ''
              END
       CONCAT ' ALLOW READ ACCESS '
       CONCAT CASE
                 WHEN TEMPSPACE IS NOT NULL THEN 'USE ' CONCAT RTRIM (TEMPSPACE)
                 ELSE ''
              END
       CONCAT ' ;'
  FROM (SELECT tb.CREATOR,
               tb.NAME,
               tb.CARD,
               ts.pagesize,
               TS.TBSPACE,
               (Select TMP.TBSPACE
                From SYSIBM.SYSTABLESPACES TMP
                Where  TMP.pagesize = TS.pagesize
                AND TMP.Datatype = 'T'
                FETCH FIRST 1 ROWS ONLY
               ) AS TEMPSPACE,
               (SELECT RTRIM (IX.CREATOR) CONCAT '.' CONCAT RTRIM (IX.NAME)
                  FROM SYSIBM.SYSINDEXES IX
                 WHERE     UNIQUERULE IN ('P', 'U', 'D')
                       AND IX.TBCREATOR = TB.CREATOR
                       AND IX.TBNAME = TB.NAME
                ORDER BY CASE
                            WHEN INDEXTYPE = 'CLUS' THEN 1
                            WHEN UNIQUERULE = 'P' THEN 2
                            WHEN UNIQUERULE = 'U' THEN 3
                            WHEN UNIQUERULE = 'D' THEN 4
                            ELSE 4
                         END
                FETCH FIRST 1 ROWS ONLY
               ) AS IXNAME
          FROM SYSIBM.SYSTABLES tb
               INNER JOIN SYSIBM.SYSTABLESPACES TS
                  ON tb.TBSPACE = TS.TBSPACE
         WHERE TB.TYPE IN ('S', 'T') AND TB.CREATOR = 'SYSIBM'
        ORDER BY tb.CARD DESC, tb.CREATOR, tb.NAME) REORGTB
;


Die Query hatte ich mal für DB2 LUW geschrieben um zu prüfen welche Tabellen Reorganissiert werden sollte (wie REORGCHK)
F4 (Clusterratio) wird dabei vorgezogen und  bei der Tabelle angezeigt

SELECT CHAR(T. TABSCHEMA,12) AS TABSCHEMA , CHAR(T.TABNAME,40) AS TABNAME ,
    T.CARD , T.NPAGES , T.FPAGES , I.NLEAF , CAST(T.CARD * (C.AVGCOLLEN + 10)
    AS BIGINT) AS TSIZE ,
    CASE
      WHEN T.CARD = 0
        THEN NULL
      ELSE CAST(100*T.OVERFLOW/T.CARD AS DEC(13,2))
    END AS F1 ,
    CASE
      WHEN BIGINT(T.FPAGES-1) * BIGINT(S.PAGESIZE-76) = 0
        THEN NULL
      ELSE CAST(100*BIGINT(T.CARD) * (C.AVGCOLLEN + 10) / (BIGINT(T.FPAGES-1)
        * BIGINT(S.PAGESIZE-76)) AS DEC(17,2))
    END AS F2 ,
    CASE
      WHEN T.FPAGES = 0
        THEN NULL
      ELSE CAST(100*T.NPAGES/T.FPAGES AS DEC(17,2))
    END AS F3 , F4 ,
    CASE
      WHEN T.CARD =0
        THEN '-'
      ELSE
        CASE
          WHEN CAST(100*T.OVERFLOW/T.CARD AS DEC(13,2)) >= 5
            THEN '*'
          ELSE '-'
        END
    END ||
    CASE
      WHEN BIGINT(T.FPAGES-1) * BIGINT(S.PAGESIZE-76) = 0
        THEN '-'
      ELSE
        CASE
          WHEN CAST(100*BIGINT(T.CARD) * (C.AVGCOLLEN + 10) / (BIGINT(T.FPAGES
              -1) * BIGINT(S.PAGESIZE-76)) AS DEC(17,2)) <= 70
            THEN '*'
          ELSE '-'
        END
    END ||
    CASE
      WHEN T.CARD =0
        THEN '-'
      ELSE
        CASE
          WHEN CAST(100*T.NPAGES/T.FPAGES AS DEC(17,2)) <= 80
            THEN '*'
          ELSE '-'
        END ||
        CASE
          WHEN F4 IS NOT NULL
            AND F4 < 90
            THEN '*'
          ELSE '-'
        END
    END AS REORG
  FROM SYSCAT.TABLES T
    INNER JOIN SYSCAT.TABLESPACES S
    ON T.TBSPACEID = S.TBSPACEID
    INNER JOIN (
      SELECT TABSCHEMA, TABNAME, SUM(
          CASE
            WHEN LOGGED = ''
              THEN AVGCOLLEN
            WHEN LENGTH <= 1024
              THEN 72
            WHEN length <= 8192
              THEN 96
            WHEN length <= 65536
              THEN 120
            WHEN length <= 524000
              THEN 144
            WHEN length <= 4190000
              THEN 168
            WHEN length <= 134000000
              THEN 200
            WHEN length <= 536000000
              THEN 224
            WHEN length <= 1070000000
              THEN 256
            WHEN length <= 1470000000
              THEN 280
            WHEN length <= 2147483647
              THEN 316
          END +
          CASE
            WHEN LOGGED <> ''
              AND NULLS = 'Y'
              THEN 1
            ELSE 0
          END ) AS AVGCOLLEN
        FROM SYSCAT.COLUMNS
        GROUP BY TABSCHEMA, TABNAME ) AS C
    ON T.TABSCHEMA = C.TABSCHEMA
    AND T.TABNAME = C.TABNAME
    INNER JOIN (
      SELECT TABSCHEMA, TABNAME, SUM(NLEAF) AS NLEAF, SUM(NLEVELS) AS NLEVELS
        FROM SYSCAT.INDEXES
        GROUP BY TABSCHEMA, TABNAME) AS I
    on T.TABSCHEMA = I.TABSCHEMA
    AND T.TABNAME = I.TABNAME
    LEFT OUTER JOIN (
      SELECT TABSCHEMA, TABNAME, Indextype,
          CASE
            When CLUSTERRATIO < 0
              Then Integer(CLUSTERFACTOR*100)
            ELSE CLUSTERRATIO
          END AS F4
        FROM SYSCAT.INDEXES ) AS CLU
    on T.TABSCHEMA = CLU.TABSCHEMA
    AND T.TABNAME = CLU.TABNAME
    AND CLU.Indextype = 'CLUS'
  WHERE T.TYPE IN ( 'T','S')
;



Restore Database

SELECT DISTINCT 'restore database ' concat RTRIM(CURRENT SERVER)
       CONCAT case when UCASE(LOCATION) LIKE  '%DB2TSM%' THEN ' USE TSM '
                   Else ' FROM '  CONCAT RTrim (LOCATION)
                End
       CONCAT ' taken at '
       CONCAT char (START_TIME)
       Concat ' BUFFER 1024 REPLACE EXISTING WITHOUT  ROLLING FORWARD WITHOUT PROMPTING ;'
  FROM sysibmadm.DB_history
 WHERE operation = 'B'
       AND Start_time = (SELECT Max (Start_time)
                           FROM sysibmadm.DB_history
                          WHERE operation = 'B'
                          AND   SQLCODE  IS NULL
                          )
;


Recreate Nicknames

select 'CREATE OR REPLACE NICKNAME '
concat RTRIM(TABSCHEMA) concat '.' concat Rtrim(TABNAME)
concat '  FOR '
concat RTRIM(SERVERNAME)    concat '.'
concat  RTRIM(REMOTE_SCHEMA) concat '.'
concat  RTRIM(REMOTE_TABLE) 
concat ';'
from SYSCAT.NICKNAMES
;



select 'db2 CREATE OR REPLACE NICKNAME '
concat RTRIM(TABSCHEMA) concat '.' concat Rtrim(TABNAME)
concat '  FOR '
concat RTRIM(SERVERNAME)    concat '.'
concat  RTRIM(REMOTE_SCHEMA) concat '.'
concat  RTRIM(REMOTE_TABLE) 
concat ''
from SYSCAT.NICKNAMES nn
inner join db2pbs.sysibm_systables t1
on t1.creator = nn.REMOTE_SCHEMA
and t1.name   = nn.REMOTE_TABLE
where nn.REMOTE_SCHEMA <> 'SYSIBM'
and nn.SERVERNAME = '<Creator>'
;



select '-- DROP NICKNAME "' concat RTRIM(TABSCHEMA) concat '"."'
concat Rtrim(TABNAME)  concat '" ;'
from SYSCAT.NICKNAMES nn
left outer join '<Creator>'.sysibm_systables t1
on t1.creator = nn.REMOTE_SCHEMA
and t1.name   = nn.REMOTE_TABLE
where nn.REMOTE_SCHEMA <> 'SYSIBM'
and nn.SERVERNAME = '<Creator>'
and t1.creator is null

;


1.) Möglichkeite eins wenn die Nicknames mit einem anderen Owner angelegt wurden.
select 'TRANSFER OWNERSHIP OF NICKNAME ' concat RTRIM(TABSCHEMA) concat '.' concat Rtrim(TABNAME)
        concat ' TO USER M00DB2UC  PRESERVE PRIVILEGES ; '
from SYSCAT.NICKNAMES nn
where nn.REMOTE_SCHEMA <> 'SYSIBM'
and nn.owner <> '<Creator>'
;



2.) Möglichkeit 2    "SET SESSION AUTHORIZATION", dafür muss aber vorher der Grand gemacht werden !


db2 CREATE OR REPLACE NICKNAME '<Creator>'.'<Name>'  FOR <Location>.'<Creator>'.'<Name>'

db2 grant SETSESSIONUSER on user <FROMUSERID> to user <to userid>

db2 SET SESSION AUTHORIZATION '<USERID>'


Siehe IBM :  http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp


Delete LOGS von TSM

Logs von TSM löschen, Infos kommen von  
SYSIBMADM.DB_HISTORY, also erst mit Prune History
die Informationen bereinigen und dann LOGS aus TSM löschen !


SELECT 'db2adutl delte logs between '
concat FIRST_DEL_LOG
concat ' and '
concat LAST_DEL_LOG
  FROM (SELECT FIRSTLOG, LASTLOG, START_TIME
               CHAR ('S0000000') AS FIRST_DEL_LOG,
               SUBSTR (FIRSTLOG, 1, 1)
               CONCAT Substr (
                         CHAR (DIGITS (INT (SUBSTR (FIRSTLOG, 2, 7) - 1))),
                         4, 7)
                  AS LAST_DEL_LOG
          FROM SYSIBMADM.DB_HISTORY
         WHERE OPERATION = 'B'
        ORDER BY START_TIME
        FETCH FIRST 1 ROWS ONLY);

Not LOGGED

ALTER TABLE Creator.Name   ACTIVATE NOT LOGGED INITIALLY;

Rebind Package

select DISTINCT 'DB2 REBIND PACKAGE ' concat RTrim(PKGSCHEMA )
  Concat '.'
  Concat PKGNAME
  concat Case when pkgversion = '' Then ''
              Else 'VERSION "' CONCAT RTrim(pkgversion) CONCAT '" RESOLVE ANY   REOPT NONE '
          End as Version
from SYSCAT.PACKAGEDEP
Where pkgversion = '<PGVERSION>'
and PKGSCHEMA = '<PGSCHEMA>' and
PKGNAME in ('<PGNAME1>','<PGNAME2>', '<PGNAMEn>')

;


FILE SYSTEM CACHING


select 'alter tablespace '
concat RTrim(TBSP_NAME)
concat ' NO FILE SYSTEM CACHING ;'
from sysibmadm.SNAPTBSP
where FS_CACHING = 0
;


Indexes not USED !


select        IX.INDSCHEMA
        ,     IX.INDNAME
        ,     IX.TABSCHEMA
        ,     IX.TABNAME
        ,     IX.LASTUSED
        ,     IX.MADE_UNIQUE
FROM SYSCAT.INDEXES       IX
Inner join SYSCAT.TABLES   TB
ON    TB.TABSCHEMA   =  IX.TABSCHEMA
AND   TB.TABNAME     =  IX.TABNAME
WHERE TB.LASTUSED    > '01.01.0001'
AND   IX.MADE_UNIQUE = 'N'
AND   IX.UNIQUERULE  = 'D' 
-- AND        TB.TABSCHEMA = 'XXXXX'

;


Clustering Indexes


select        IX.INDSCHEMA
        ,     IX.INDNAME
        ,     IX.TABSCHEMA
        ,     IX.TABNAME
        ,     IX.LASTUSED
        ,     IX.MADE_UNIQUE
FROM SYSCAT.INDEXES       IX
WHERE IX.INDEXTYPE    = 'CLUS'

;

Doppelte Indexcolumns finden !

--- Herrn Google gefragt "db2 sql figure out duplicate indexes"

SELECT  A.creator, A.NAme, A.TBCREATOR, A.TBNAME, A.COLNAMES
    ,   B.creator, B.NAme, B.TBCREATOR, B.TBNAME, B.COLNAMES
FROM SYSIBM.SYSINDEXES A
INNER JOIN SYSIBM.SYSINDEXES B
ON A.TBNAME           = B.TBNAME
AND A.TBCREATOR = B.TBCREATOR
-- AND A.COLCOUNT = B.COLCOUNT
AND NOT ( A.NAME = B.NAME AND A.CREATOR = B.CREATOR )
WHERE A.COLCOUNT = ( SELECT COUNT(*)
                     FROM SYSCAT.INDEXCOLUSE C
                     WHERE C.INDNAME = B.NAME
                     AND C.INDSCHEMA = B.CREATOR
                     AND ( COLNAME , COLSEQ, COLORDER ) IN
                     ( SELECT COLNAME,COLSEQ, COLORDER
                       FROM SYSCAT.INDEXCOLUSE D
                       WHERE D.INDNAME = A.NAME
                       AND D.INDSCHEMA = A.CREATOR ))

;

Index Compress Info :

SELECT *
FROM TABLE(sysproc.admin_get_index_compress_info('', '', '', -2, -2)) AS ICI

Index Info :

SELECT *
FROM TABLE(sysproc.admin_get_index_info('','','')) AS t


Selectstatement um ADMIN_MOVE_TABLE zu generieren  


Select 'CALL SYSPROC.ADMIN_MOVE_TABLE(' ||
'''' || RTrim(CREATOR) || '''' || ',' ||
'''' || RTRIM(NAME)    || '''' || ',' ||
'''' || RTrim(CREATOR) || '''' || ',' ||
'''' || RTrim(CREATOR) || '''' || ',' ||
'''' || RTrim(CREATOR) || '''' || ',' ||
'NULL, NULL, NULL, NULL, NULL, ' ||
'''' ||
'MOVE' ||
'''' || ') ;'
from sysibm.systables
where creator = 'xxxx'


Selectstatement um FOREIGN KEY zu generieren 


-- Drop FOREIGN KEY
Select 'Alter table ' concat Rtrim(TABSCHEMA) concat '.'  concat RTrim(TABNAME) concat ' Drop FOREIGN KEY ' concat Rtrim(CONSTNAME) concat ' ;'

from (
SELECT TABSCHEMA
    ,TABNAME
    ,CONSTNAME
    ,REFTABSCHEMA
    ,REFTABNAME
    ,COLCOUNT
    ,CASE
        WHEN DELETERULE = 'C'
            THEN 'ON DELETE CASCADE '
        WHEN DELETERULE = 'R'
            THEN 'ON DELETE RESTRICT '
        WHEN DELETERULE = 'R'
            THEN 'ON DELETE SET NULL '
        ELSE 'ON DELETE NO ACTION '
        END DELETERULE
    ,CASE
        WHEN UPDATERULE = 'R'
            THEN 'ON UPDATE RESTRICT '
        ELSE 'ON UPDATE NO ACTION '
        END UPDATERULE
    ,FK_COLNAMES
    ,PK_COLNAMES
FROM SYSCAT.REFERENCES
where TABSCHEMA = '<tabschema>'
ORDER BY TABSCHEMA
    ,TABNAME
    ,CONSTNAME
) T1    
FOR FETCH ONLY
;


-- Create  FOREIGN KEY
Select 'Alter table ' concat Rtrim(TABSCHEMA) concat '.' concat RTrim(TABNAME)
concat ' ADD constraint ' concat Rtrim(CONSTNAME)
concat ' foreign key ( ' concat Replace(Replace(Replace(Trim(FK_COLNAMES), '   ', ' '), '  ', ','), ',,', ',')  concat ' ) '
concat ' REFERENCES '  concat Rtrim(REFTABSCHEMA) concat '.'  concat RTrim(REFTABNAME)
concat ' ( ' concat Replace(Replace(Replace(Trim(PK_COLNAMES), '   ', ' '), '  ', ','), ',,', ',')  concat ' ) '
concat DELETERULE concat '  '
concat UPDATERULE concat ' '
concat ' ;'

from (
SELECT TABSCHEMA
    ,TABNAME
    ,CONSTNAME
    ,REFTABSCHEMA
    ,REFTABNAME
    ,COLCOUNT
    ,CASE
        WHEN DELETERULE = 'C'
            THEN 'ON DELETE CASCADE '
        WHEN DELETERULE = 'R'
            THEN 'ON DELETE RESTRICT '
        WHEN DELETERULE = 'R'
            THEN 'ON DELETE SET NULL '
        ELSE 'ON DELETE NO ACTION '
        END DELETERULE
    ,CASE
        WHEN UPDATERULE = 'R'
            THEN 'ON UPDATE RESTRICT '
        ELSE 'ON UPDATE NO ACTION '
        END UPDATERULE
    ,FK_COLNAMES
    ,PK_COLNAMES
FROM SYSCAT.REFERENCES
where TABSCHEMA = '<tabschema>'

ORDER BY TABSCHEMA
    ,TABNAME
    ,CONSTNAME
) T1    
FOR FETCH ONLY
;