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
)
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;