DB2 z/OS   DB2 LUW
z/OS CICS VTAM

DB2 SQL

Home



DB2 SQL Zeiten Berechnen mit TIMESTAMPDIFF :

In dem Beispiel  habe ich eine Tabelle mit Arbeitszeiten (Startzeit, Endezeit sind Timestamp wegen Datumsweches bei Nachtarbeiten)


With TAB (Startzeit, Endezeit, Pausen)
         AS ( Select Timestamp('2010-11-28 07:00:00') ,
                     Timestamp('2010-11-29 01:00:10') ,
                     Time('01:00:00')
              FROM Sysibm.sysdummy1 union all
              Select Timestamp('2010-11-29 08:00:00') ,
                     Timestamp('2010-11-29 16:00:00') ,
                     Time('00:30:00')
              FROM Sysibm.sysdummy1
             )

select Time(Char(Digits(Decimal(TIMESTAMPDIFF(8, CHAR(ENDEZEIT - STARTZEIT)), 2, 0)))
                  Concat ':'
                  Concat    Char(Digits(Decimal(Mod(TIMESTAMPDIFF(4, CHAR(ENDEZEIT - STARTZEIT)), 60), 2, 0)))
                  Concat ':'
                  Concat    Char(Digits(Decimal(Mod(TIMESTAMPDIFF(2, CHAR(ENDEZEIT - STARTZEIT)), 60), 2, 0)))
                 )
    -  (( HOUR(TIME(PAUSEN))* 3600)
    +  (MINUTE(TIME(PAUSEN))*   60)
    +   SECOND(TIME(PAUSEN))      
    )  SECOND  AS Arbeitszeit
FROM TAB     

ARBEITSZEIT
17:00:00
07:30:00


DB2 SQL Zeiten Berechnen mit TIME :

Select jetzt, spaeter, Pausen
, TRANSLATE('AB:CD:EF', digits(
            TIme(TRANSLATE('AB:CD:EF', digits(spaeter - Jetzt) , 'ABCDEF') )
       - Pausen )    , 'ABCDEF') As Arbeitszeit
from (
VALUES(Time('23:00:00'),
                Time('23:00:00') + 120 Minutes,
                Time('00:30:00')
               )  -- Achgtung! liefert falsches Ergeniss wegen Datumswechsel
            , (Current Time,
               Current Time + 220 Minutes,
               Time('00:30:00')
              )
) t1 (jetzt, spaeter, Pausen)
;

Ergebniss =
21:30:00   (Falsches Ergebniss wegen Datumswechsel)
03:30:00   (240 Minuten - 30 Minuten Pause)



Tabellen ohne Indexes : (LUW und z/OS)

select TB.Creator, TB.Name, IX.TBCreator, IX.TBName
from Sysibm.Systables  TB
Left Outer Join Sysibm.SYSINDEXES IX
on  TB.Creator = IX.TBCreator
And TB.Name    = IX.TBName
Where TB.Type  = 'T'
And   IX.TBCreator is null


Alle Tabellen mit Clusterratio < 90% (Reorg sollte gemacht werden)  z/OS

SELECT TB.CREATOR, TB.NAME
     , TB.STATSTIME
     , IX.CREATOR, IX.NAME
     , IX.CLUSTERRATIO
     , IX.CLUSTERING, IX.CLUSTERED
FROM SYSIBM.SYSTABLES  TB
INNER JOIN SYSIBM.SYSINDEXES IX 
ON  TB.CREATOR= IX.TBCREATOR 
AND TB.NAME   = IX.TBNAME
WHERE IX.CLUSTERING = 'Y'
AND   IX.CLUSTERED  = 'Y' 
AND   IX.CLUSTERRATIO  < 90


Kleine Query um die ID's fuer einen DSN1COPY zu selektieren


SELECT T3.NAME AS DBNAME
   ,   T1.NAME AS TSNAME
   ,   STRIP(T2.CREATOR) !! '.' !! STRIP(T2.NAME) AS TABNAME
   ,   T3.DBID AS DBID
   ,   T1.PSID AS TSID
   ,   T2.OBID AS TBID
   ,   Hex(T3.DBID) AS DBID1
   ,   Hex(T1.PSID) AS TSID1
   ,   Hex(T2.OBID) AS TBID1
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.NAME  = '<TABLENAME>'
And    T2.Creator = '<CREATOR>'
ORDER BY DBNAME desc, TSNAME, TABNAME
  ;
              

Alle Tabellen mit Clusterratio < 90% (Reorg sollte gemacht werden)  LUW

Select Creator, Name, Cluster
From(
SELECT TB.Creator, TB.Name,
          CASE
            When CLUSTERRATIO < 0
              Then Integer(CLUSTERFACTOR*100)
            ELSE CLUSTERRATIO
          END AS Cluster
From Sysibm.Systables TB
Inner Join Sysibm.SysIndexes AS IX
On  TB.Creator   = IX.TBCreator
AND TB.Name      = IX.TBName
AND IX.Indextype = 'CLUS'
WHERE TB.TYPE IN ( 'T','S')
)T1
Where Cluster < 90


Alle Tabellen mit RI/FK bei denen die keys nicht indiziert sind  LUW

with FKEYS (AUSW, TABSCHEMA, TABNAME, CONSTNAME, COLCOUNT, IX_COLNAMES) AS
( SELECT  'PK ' AS AUSW
         , TABSCHEMA
         , TABNAME
         , CONSTNAME
         , COLCOUNT
         , Trim(FK_COLNAMES)
  FROM  SYSCAT.REFERENCES
  union all
  SELECT  'REF' AS AUSW
        , REFTABSCHEMA AS TABSCHEMA
        , REFTABNAME AS TABNAME
        ,  CONSTNAME
        ,  COLCOUNT
        ,  Trim(PK_COLNAMES)
  FROM  SYSCAT.REFERENCES
  ORDER BY 2, 3, 4                     
)
   
select FK.AUSW, FK.TABSCHEMA, FK.TABNAME, FK.CONSTNAME, FK.COLCOUNT, FK.IX_COLNAMES
     , ic.colname
     , ic.indname
from FKEYS FK
inner join syscat.indexes IX
on  fk.tabschema = ix.tabschema
and fk.tabname   = ix.tabname
left outer join SYSCAT.INDEXCOLUSE ic
ON  IC.INDSCHEMA = IX.INDSCHEMA  
AND IC.INDNAME   = IX.INDNAME     
and Trim(FK.IX_COLNAMES) = Trim(ic.colname)
where ic.INDSCHEMA is NULL
;


Alle Tabellen mit Foreign Key einer Tabelle ermitteln (LUW)


WITH FK_Tables (
    LEVEL
    ,PKSchema
    ,PKNAME
    ,FKSchema
    ,FKNAME
    )
AS (
    SELECT 0
        ,ref.tabschema
        ,ref.tabname
        ,ref.reftabschema
        ,ref.reftabname
    FROM syscat.REFERENCES ref
    WHERE   ref.reftabschema = '<YopurSchema>'
        AND ref.reftabname IN ('<YourTable>')
   
    UNION ALL
   
    SELECT p.LEVEL + 1
        ,r.tabschema
        ,r.tabname
        ,r.reftabschema
        ,r.reftabname
    FROM syscat.REFERENCES r
        ,FK_Tables p
    WHERE p.LEVEL < 2
        AND p.fKSchema = r.tabschema
        AND p.fKNAME = r.tabname
    )
SELECT DISTINCT PKSchema
    ,PKNAME
    ,FKSchema
    ,FKNAME
FROM FK_Tables
ORDER BY PKSchema
    ,PKNAME;