zOs/SQL/REODDL
$#@
call sqlConnect DBOL
$*( fun = funktion
t = tables erstellen/migrieren und views erstellen
v = nur views droppen und erstellen
tDropOld = alte Tables löschen
(entstehen bei migration durch rename
==> nach Kontrolle migration )
$*)
$=fun= ?DropOld
$=fun=v
$=db2v10ENF = 1 $** v10 catalog or not
$=mig=63 $*( migration ab wo
0 = create new
54 = migrate ab 54
570 = migrate ab (unvollständiger) Version 5.7
571 = migrate ab 5.7 ohne clusterRatio
59 = migrate stats tables from v9 to db2v10
62 = migrate stats tables from v10 to db2v11
63 = primary key in stats tables for cloned
$*)
$;
$** $>. fEdit() $@/ddl/
call sqlStmts $<@/ddl/
$@=[
SET CURRENT SQLID='S100447';
$]
if $fun = 'tDropOld' then $@=/tDropOld/
drop table s100447.tReoTsParmsOld;
drop table s100447.tReoTSParmsHistOld;
commit;
$/tDropOld/ else if $fun == 't' then $@/tAnf/
$@=[
----------------------------------------------------------------------
-- tRtsReo*Parms und *run* und tdbState
-- tables für rts reorg erstellen
-- default werte einfüllen
-- datenübernahme aus alten exception tables
-- 29. 2.16 v6.4: clone infos in views
-- 17.9.13 walter: aReor, reorP, rbdp usw. Stati
-- 28.09.2011 v5.7
$]
if $mig == 0 then $@=[
-- create new tables
$] else if $mig == 54 then $@=[
-- migrate from table version 54
$] else if $mig == 570 then $@=[
-- migrate from table version 570 (with bad triggers)
$] else if $mig == 571 then $@=[
-- migrate from table version 571 (without clustRatio)
$] else if $mig == 59 then $@=[
-- migrate stats Table from db2v9 to db2v10
$] else if $mig == 62 then $@=[
-- migrate stats Table from db2v9 to db2v10
$] else if $mig == 63 then $@=[
-- primary key / index with instance for cloned TS/IX
$] else
call err 'bad mig='$mig
$@=[
-- drop/stop auf die alten DBs DB2RTS, TSTRTSDB und DB2REORG
-- muss extra gemacht werden|
--
----------------------------------------------------------------------
$]
$/tAnf/ else if $fun \== 'v' then call err 'bad fun' $fun
if $fun == 't' | $fun == 'v' then $@=/vDrop/
drop view s100447.vReoTSSchwelle ;
drop view s100447.vReoIxSchwelle ;
drop view s100447.vReoJobParms ;
drop view s100447.vReoRunRng;
drop view s100447.vReoRunTSStats;
drop view s100447.vReoRunIXStats;
drop view s100447.vReoTSStatsPlus;
drop view s100447.vReoRunJobChk;
drop view s100447.vReoRunRngChk;
$/vDrop/
if $fun == 't' then $@/table/
if $mig == 0 then $@=[
--drop DATABASE Db2Reo; commit;
CREATE DATABASE db2Reo
BUFFERPOOL BP2
INDEXBP BP1
CCSID UNICODE
STOGROUP GSMS;
--DROP TABLESPACE db2Reo.A012A;
--DROP TABLESPACE Db2Reo.A012H;
--DROP TABLESPACE Db2Reo.A013A;
--DROP TABLESPACE Db2Reo.A013H;
--DROP TABLESPACE Db2Reo.A014A;
--DROP TABLESPACE Db2Reo.A014H;
--drop TABLEspace Db2Reo.A020A ;
--drop TABLEspace Db2Reo.A021A ;
--drop TABLEspace Db2Reo.A022A ;
--drop TABLEspace Db2Reo.A023A ;
--drop TABLEspace Db2Reo.A024A ;
--DROP TABLESPACE db2Admin.A012A;
--DROP TABLESPACE db2Admin.A012H;
--DROP TABLESPACE db2Admin.A013A;
--DROP TABLESPACE db2Admin.A013H;
--DROP TABLESPACE db2Admin.A014A;
--DROP TABLESPACE db2Admin.A014H;
--drop TABLEspace db2Admin.A036A ;
--drop TABLEspace db2Admin.A020A ;
commit
;
CREATE TABLESPACE AReoTPA
IN Db2Reo
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
CREATE TABLESPACE AReoTPH
IN Db2Reo
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
$]
if $mig >> 0 & $mig << 59 then $@=[
drop trigger s100447.tReoTSParmsBefIns ;
drop trigger s100447.tReoTSParmsBefUpd ;
drop trigger s100447.tReoTSParmsAftIns ;
drop trigger s100447.tReoTSParmsAftUpdSt ;
drop trigger s100447.tReoTSParmsAftUpd ;
drop trigger s100447.tReoTSParmsAftDelSt ;
rename table s100447.tReoTsParms to tReoTsParmsOld ;
rename INDEX s100447.IReoTSParms to IReoTSParmsOld ;
rename table s100447.tReoTSParmsHist to tReoTSParmsHistOld ;
rename INDEX s100447.IReoTSParmsHist to IReoTSParmsHistOld ;
$] else if $mig == 62 then $@[
ixSta = catTbLastCol(s100447, tReoRunIxStats)
if ixSta \== "44 STATS101" then $@[
call err 'bad s100447, tReoRunIxStats:' ixSta
$] else $@=[
alter TABLE s100447.tReoRunIxStats
alter COPYUPDATELRSN set data type CHARACTER(10) -- FOR BIT DATA
;
$]
tsSta = catTbLastCol(s100447, tReoRunTsStats)
$@=[
alter TABLE s100447.tReoRunTSStats
alter COPYUPDATELRSN set data type CHARACTER(10) -- FOR BIT DATA
$]
if tsSta == "37 NAME" then $@=[
-- v10 columns
add REORGCLUSTERSENS BIGINT
WITH DEFAULT NULL
add REORGSCANACCESS BIGINT
WITH DEFAULT NULL
add REORGHASHACCESS BIGINT
WITH DEFAULT NULL
add HASHLASTUSED DATE
WITH DEFAULT NULL
add DRIVETYPE CHARACTER(3) FOR MIXED DATA
NOT NULL
WITH DEFAULT 'HDD'
add LPFACILITY CHARACTER(1) FOR MIXED DATA
WITH DEFAULT NULL
add STATS01 BIGINT
WITH DEFAULT NULL
$]
if tsSta == "44 STATS01" | tsSta == "37 NAME" then $@=[
-- v11 columns
add UPDATESIZE BIGINT
WITH DEFAULT NULL
add LASTDATACHANGE TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
$] else if tsSta \== "46 LASTDATACHANGE" then $@[
call err 'bad s100447.tReoRunTsStats:' tsSta
$]
$$ ;
$] else if $mig == 63 then $@=[
alter TABLE s100447.tReoRunIXStats
drop constraint primaryKey ;
drop INDEX s100447.iReoRunIXStats1;
CREATE UNIQUE INDEX s100447.iReoRunIXStats1
ON s100447.tReoRunIXStats
(tst asc, rng asc, partition asc, isoBid asc, instance)
CLUSTER
USING STOGROUP GSMS
BUFFERPOOL BP1
;
alter TABLE s100447.tReoRunIXStats
add constraint primaryKey primary key
(tst, rng, partition, isobid, instance)
;
alter TABLE s100447.tReoRunTSStats
drop constraint primaryKey ;
drop INDEX s100447.iReoRunTSStats1;
CREATE UNIQUE INDEX s100447.iReoRunTSStats1
ON s100447.tReoRunTSStats
(tst asc, rng asc, partition asc, instance asc)
CLUSTER
USING STOGROUP GSMS
BUFFERPOOL BP1
;
alter TABLE s100447.tReoRunTSStats
add constraint primaryKey primary key
(tst, rng, partition, instance)
;
$]
if $mig << 59 then $@=[
CREATE TABLE s100447.tReoTSParms
(
PRIO SMALLINT NOT NULL WITH DEFAULT 20,
CONSTRAINT PRIO CHECK (PRIO >= 0 AND PRIO <= 99),
DB CHAR(8) NOT NULL,
TS CHAR(8) NOT NULL,
PARTVON smallINT NOT NULL WITH DEFAULT,
CONSTRAINT PARTVON CHECK (PARTVON >= 0),
PARTBIS smallINT NOT NULL WITH DEFAULT,
CONSTRAINT PARTBIS CHECK (PARTBIS <= 9999
AND PARTBIS >= PARTVON),
GUVON DATE NOT NULL WITH DEFAULT,
GUBIS DATE NOT NULL WITH DEFAULT '31.12.9999',
REMARK CHAR(80) FOR SBCS DATA NOT NULL,
REORG CHAR(12) FOR SBCS DATA WITH DEFAULT NULL,
CONSTRAINT REORG
CHECK (REORG IN ('ALWAYS', 'NEVER', 'THRESHOLD')),
UNCLUST INTEGER WITH DEFAULT NULL,
clustRatio INTEGER WITH DEFAULT NULL,
FARINDREF INTEGER WITH DEFAULT NULL,
NEARINDREF INTEGER WITH DEFAULT NULL,
EXTENTS SMALLINT WITH DEFAULT NULL,
REORGDAYS INTEGER WITH DEFAULT NULL,
CONSTRAINT reorgDays
CHECK (reorgDays between 1 and 99999),
INSERTS INTEGER WITH DEFAULT NULL,
UPDATES INTEGER WITH DEFAULT NULL,
DELETES INTEGER WITH DEFAULT NULL,
advisory CHAR(1) WITH DEFAULT NULL,
CONSTRAINT advisory
CHECK (advisory IN ('0', '1')),
datasize INTEGER WITH DEFAULT NULL,
pageSave INTEGER WITH DEFAULT NULL,
rangeI0 INTEGER WITH DEFAULT NULL,
parallel smallInt WITH DEFAULT NULL,
updTst TIMESTAMP NOT NULL with default
)
IN Db2Reo.AReoTPA
AUDIT NONE
DATA CAPTURE NONE
CCSID UNICODE
NOT VOLATILE
;
CREATE UNIQUE INDEX s100447.IReoTSParms
ON s100447.tReoTSParms
(PRIO ASC,
DB ASC,
TS ASC,
PARTVON ASC,
GUBIS ASC
)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
LABEL ON TABLE s100447.tReoTSParms
IS 'TS Thresholds: Defs/Ausnahmen'
;
COMMENT ON TABLE s100447.tReoTSParms is
'Default- und Spezial-Schwellwerte für TS Reorgs'
;
LABEL ON s100447.tReoTSParms
( PRIO is 'Prioritaet: tiefste=0=Default' ,
DB is 'Datenbank Name' ,
TS is 'TableSpace Name' ,
PARTVON is 'Partition von' ,
PARTBIS is 'Partition bis' ,
GUVON is 'Gueltig von' ,
GUBIS is 'Gueltig bis' ,
REORG is 'ALWAYS,NEVER,THRESHOLD' ,
UNCLUST is 'Schwellwert unclustered %' ,
clustRatio is 'Schwellwert clustRatio % ' ,
FARINDREF is 'SchwWe overflow far (>16 pg) %',
NEARINDREF is 'SchwWe overflow near(<16 pg) %',
EXTENTS is 'SchwWe Extents pro TS/Part' ,
REORGDAYS is 'Schwellwert Anzahl Tage' ,
INSERTS is 'Schwellwert inserts %' ,
UPDATES is 'Schwellwert updates %' ,
DELETES is 'Schwellwert deletes %' ,
advisory is 'reorg bei AREO* 1/0' ,
datasize is 'Schwellwert dataSize < %' ,
pageSave is 'Schwellwert pageSave < %' ,
RangeI0 is 'Range Reo time > % i0time' ,
parallel is 'max parllele parts reorg' ,
updTst is 'letzte Aenderung' ,
REMARK is 'Begründung (Freitext)'
)
;
CREATE TABLE s100447.tReoTSParmsHist
as (select e.*, ' ' updOp,
current timestamp endTst, ' ' endOp
from s100447.tReoTSParms e
) with no data
IN Db2Reo.AReoTPH
AUDIT NONE
DATA CAPTURE NONE
CCSID UNICODE
NOT VOLATILE
;
LABEL ON TABLE s100447.tReoTSParmsHist
IS 'History von tReoTSParms'
;
LABEL ON s100447.tReoTSParmsHist
( updTst is 'activation timestamp'
, updOp is 'activation operation (i,u)'
, endTst is 'deactivation timestamp'
, endOp is 'deactivation operation (u,d)'
)
;
CREATE UNIQUE INDEX s100447.IReoTSParmsHist
ON s100447.tReoTSParmsHist
(PRIO ASC,
DB ASC,
TS ASC,
partVon ASC,
guBis ASC,
updTst ASC
)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
commit;
$]
if $mig >>= 54 & $mig << 59 then $@=[
$=par =- if($mig>>570,'parallel','case when 1=0 then 7 else null end')
-- delete from s100447.tReoTSParms;
insert into s100447.tReoTSParms
select PRIO
, DB
, TS
, PARTVON
, PARTBIS
, GUVON
, GUBIS
, REMARK
, REORG
, UNCLUST
, case when 1=0 then 7 else null end -- clustRatio
, FARINDREF
, NEARINDREF
, EXTENTS
, REORGDAYS
, INSERTS
, UPDATES
, DELETES
, ADVISORY
, DATASIZE
, PAGESAVE
, RANGEI0
, $par -- parallel
, UPDTST
from s100447.tReoTSParmsOld
;
select count(*) from s100447.tReoTSParms;
select count(*) from s100447.tReoTSParmsOld;
$=par =- if($mig==571, 'parallel','case when 1=0 then 7 else null end')
-- delete from s100447.tReoTSParmsHist ;
insert into s100447.tReoTSParmsHist
select PRIO
, DB
, TS
, PARTVON
, PARTBIS
, GUVON
, GUBIS
, REMARK
, REORG
, UNCLUST
, case when 1=0 then 7 else null end -- clustRatio
, FARINDREF
, NEARINDREF
, EXTENTS
, REORGDAYS
, INSERTS
, UPDATES
, DELETES
, ADVISORY
, DATASIZE
, PAGESAVE
, RANGEI0
, $par -- parallel
, UPDTST
, UPDOP
, ENDTST
, ENDOP
from s100447.tReoTSParmsHistOld
;
select count(*) from s100447.tReoTSParmsHist;
select count(*) from s100447.tReoTSParmsHistOld;
commit ;
$] else if $mig == 44 then $@=[
insert into s100447.tReoTSParms
( PRIO, DB, TS, partVon, partBis,
guVon, guBis, REORG,
unclust, FARINDREF, nearIndRef, extents, reorgdays,
inserts, updates, deletes,
remark
) select
PRIO, DB, TS, min(partVon, 9999), min(partBis, 9999),
guVon, guBis, REORG,
unclust, FARINDREF, nearIndRef, extents,
min(max(reorgDays, 1), 99999),
inserts, updates, deletes,
left(remark, 80)
from s100447.tRtsReoTSException
where db <> '*' or ts <> '*'
;
$] else if $mig >> 0 & $mig << 59 then $@[
call err 'migration von mig='$mig 'noch nicht implementiert5'
$]
if $mig << 59 then $@=[
TERMINATOR? ;
create trigger s100447.tReoTSParmsBefIns
no cascade before insert on s100447.tReoTSParms
referencing New as n
for each row mode db2sql begin atomic
set updTst = current timestamp;
end
?
create trigger s100447.tReoTSParmsBefUpd
no cascade before update on s100447.tReoTSParms
referencing New as n Old as o
for each row mode db2sql begin atomic
set updTst = current timestamp;
end
?
create trigger s100447.tReoTSParmsAftIns
after insert on s100447.tReoTSParms
referencing New_table as n
for each statement mode db2sql begin atomic
insert into s100447.tReoTSParmsHist
select n.*, 'i', '9999-12-31-23.59.59', ' '
from n
;
end
?
create trigger s100447.tReoTSParmsAftUpdSt
after update on s100447.tReoTSParms
referencing New as n old as o
for each row mode db2sql begin atomic
update s100447.tReoTSParmsHist h
set endTst = n.updTst, endOp = 'u'
where h.PRIO = o.PRIO
and h.DB = o.DB
and h.TS = o.TS
and h.partVon = o.partVon
and h.guBis = o.guBis
and h.updTst = o.updTst
;
end
?
create trigger s100447.tReoTSParmsAftUpd
after update on s100447.tReoTSParms
referencing New_table as n old_table as o
for each statement mode db2sql begin atomic
insert into s100447.tReoTSParmsHist
select n.*, 'u', '9999-12-31-23.59.59', ' '
from n
;
end
?
create trigger s100447.tReoTSParmsAftDelSt
after delete on s100447.tReoTSParms
referencing old as o
for each row mode db2sql begin atomic
update s100447.tReoTSParmsHist h
set endTst = current timestamp, endOp = 'd'
where h.PRIO = o.PRIO
and h.DB = o.DB
and h.TS = o.TS
and h.partVon = o.partVon
and h.guBis = o.guBis
and h.updTst = o.updTst
;
end
?
TERMINATOR; ?
$]
if $mig == 0 then $@=[
insert into s100447.tReoTSParms
( PRIO, DB, TS, partVon, partBis, REORG,
unclust, clustRatio,FARINDREF, nearIndRef, extents, reorgdays,
inserts, updates, deletes,
advisory, dataSize, pageSave, RangeI0, parallel,
remark
) values (
0, '*', '*', 0, 9999, 'THRESHOLD',
10, 90, 5, 5, 500, 365,
999999, 999999, 999999,
'1', 50, 5, 200, 7 ,
'globale default Schwellwerte'
)
;
$] else if $mig >>= 54 & $mig << 59 then $@=[
update s100447.tReoTSParms set parallel = 7
where prio = 0 and db = '*' and ts = '*' and parallel is null;
update s100447.tReoTSParms set clustRatio = 90
where prio = 0 and db = '*' and ts = '*' and clustRatio is null;
$]
$@=[
commit;
-- select * from s100447.tReoTSParms ;
$]
if $mig == 0 then $@=[
CREATE TABLESPACE aReoIPA
IN Db2Reo
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255;
CREATE TABLE s100447.TReoIXParms
(
PRIO SMALLINT NOT NULL WITH DEFAULT 20,
CONSTRAINT PRIO CHECK (PRIO >= 0 AND PRIO <= 99),
DB CHAR(8) NOT NULL,
TS CHAR(8) NOT NULL,
IX CHAR(20) NOT NULL,
PARTVON INT NOT NULL WITH DEFAULT,
CONSTRAINT PARTVON CHECK (PARTVON >= 0),
PARTBIS INT NOT NULL WITH DEFAULT,
CONSTRAINT PARTBIS CHECK (PARTBIS <= 9999
AND PARTBIS >= PARTVON),
GUVON DATE NOT NULL WITH DEFAULT,
GUBIS DATE NOT NULL WITH DEFAULT '31.12.9999',
REMARK CHAR(80) FOR SBCS DATA NOT NULL,
REORG CHAR(12) FOR SBCS DATA WITH DEFAULT NULL,
CONSTRAINT REORG
CHECK (REORG IN ('ALWAYS', 'NEVER', 'THRESHOLD')),
PAGESPLITS INTEGER WITH DEFAULT NULL,
EXTENTS SMALLINT WITH DEFAULT NULL,
REORGDAYS INTEGER WITH DEFAULT NULL,
CONSTRAINT reorgDays
CHECK (reorgDays between 1 and 99999),
INSERTS INTEGER WITH DEFAULT NULL,
DELETES INTEGER WITH DEFAULT NULL,
PSEUDODEL INTEGER WITH DEFAULT NULL,
updTst TIMESTAMP NOT NULL WITH DEFAULT
)
IN Db2Reo.aReoIPA
AUDIT NONE
DATA CAPTURE NONE
CCSID UNICODE
NOT VOLATILE
;
LABEL ON TABLE s100447.TReoIXParms
IS 'Ausnahmen+Defs RtsRorgs Index'
;
COMMENT ON TABLE s100447.TReoIXParms
IS 'diese Tabelle enthaelt die Default-Schwellwerte und die TS mit s
peziellen Schwellwerten für RTS-Reorgs fuer Indexe.'
;
LABEL ON s100447.TReoIXParms
( PRIO is 'Prioritaet: tiefste=0=Default' ,
DB is 'Datenbank Name' ,
TS is 'TableSpace Name' ,
IX is 'Index Name' ,
PARTVON is 'Partition von' ,
PARTBIS is 'Partition bis' ,
GUVON is 'Gueltig von' ,
GUBIS is 'Gueltig bis' ,
REORG is 'ALWAYS,NEVER,THRESHOLD' ,
PAGESPLITS is 'Schwellwert ReorgLeafFar %' ,
EXTENTS is 'SchwWe Extents pro TS/Part' ,
REORGDAYS is 'Schwellwert Anzahl Tage' ,
INSERTS is 'Schwellwert inserts %' ,
DELETES is 'Schwellwert deletes %' ,
PseudoDel is 'Schwellwert PseudoDeletes %' ,
updTst is 'letzter update Timestamp' ,
REMARK is 'Begründung (Freitext)'
) ;
CREATE UNIQUE INDEX s100447.IReoIXParms
ON s100447.TReoIXParms
(PRIO ASC,
DB ASC,
TS ASC,
IX ASC,
PARTVON ASC,
GUBIS ASC
)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
--migr53
--insert into s100447.tReoIxParms
-- select * from tstRts.tRtsReoIxException
--;
CREATE TABLESPACE aReoIPH
IN Db2Reo
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
CREATE TABLE s100447.TReoIXParmsHist
as (select e.*, ' ' updOp,
current timestamp endTst, ' ' endOp
from s100447.TReoIXParms e
) with no data
IN Db2Reo.aReoIPH
AUDIT NONE
DATA CAPTURE NONE
CCSID UNICODE
NOT VOLATILE
;
LABEL ON TABLE s100447.TReoIXParmsHist
IS 'Ausnahmen+Defs RtsReo History'
;
LABEL ON s100447.TReoIXParmsHist
( updTst is 'activation timestamp'
, updOp is 'activation operation (i,u)'
, endTst is 'deactivation timestamp'
, endOp is 'deactivation operation (u,d)'
)
;
CREATE UNIQUE INDEX s100447.IReoIXParmsHist
ON s100447.TReoIXParmsHist
(PRIO ASC,
DB ASC,
TS ASC,
IX ASC,
PARTVON ASC,
GUBIS ASC,
updTst ASC
)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
--migr53
--insert into s100447.tReoIxParmsHist
-- select * from tstRts.tRtsReoIxExceptionHist
--;
TERMINATOR? ;
create trigger s100447.TReoIXParmsBefIns
no cascade before insert on s100447.TReoIXParms
referencing New as n
for each row mode db2sql begin atomic
set updTst = current timestamp;
end
?
create trigger s100447.TReoIXParmsBefUpd
no cascade before update on s100447.TReoIXParms
referencing New as n Old as o
for each row mode db2sql begin atomic
set updTst = current timestamp;
end
?
create trigger s100447.TReoIXParmsAftIns
after insert on s100447.TReoIXParms
referencing New_table as n
for each statement mode db2sql begin atomic
insert into s100447.TReoIXParmsHist
select n.*, 'i', '9999-12-31-23.59.59', ' '
from n
;
end
?
create trigger s100447.TReoIXParmsAftUpdSt
after update on s100447.TReoIXParms
referencing New as n old as o
for each row mode db2sql begin atomic
update s100447.TReoIXParmsHist h
set endTst = n.updTst, endOp = 'u'
where h.PRIO = o.PRIO
and h.DB = o.DB
and h.TS = o.TS
and h.IX = o.IX
and h.partVon = o.partVon
and h.guBis = o.guBis
and h.updTst = o.updTst
;
end
?
create trigger s100447.TReoIXParmsAftUpd
after update on s100447.TReoIXParms
referencing New_table as n old_table as o
for each statement mode db2sql begin atomic
insert into s100447.TReoIXParmsHist
select n.*, 'u', '9999-12-31-23.59.59', ' '
from n
;
end
?
create trigger s100447.TReoIXParmsAftDelSt
after delete on s100447.TReoIXParms
referencing old as o
for each row mode db2sql begin atomic
update s100447.TReoIXParmsHist h
set endTst = current timestamp, endOp = 'd'
where h.PRIO = o.PRIO
and h.DB = o.DB
and h.TS = o.TS
and h.IX = o.IX
and h.partVon = o.partVon
and h.guBis = o.guBis
and h.updTst = o.updTst
;
end
?
TERMINATOR; ?
--migr44 --migr00
insert into s100447.tReoIXParms
( PRIO, DB, TS, IX, partVon, partBis,
REORG, pageSPlits, extents, reorgDays,
inserts, deletes, pseudoDel,
remark
) values (
0, '*', '*', '*', 0, 9999,
'THRESHOLD', 10, 500, 365,
999999, 999999, 999999,
'globale default Schwellwerte'
)
;
--migr44
-- insert into s100447.tReoIXParms
-- ( PRIO, DB, TS, IX,
-- partVon, partBis, guVon, guBis,
-- REORG, pageSPlits, extents, reorgDays,
-- inserts, deletes, pseudoDel,
-- remark
-- ) select
-- 20, db , ts , ix,
-- min(partVon, 9999), min(partBis, 9999), guVon, guBis,
-- REORG, pageSPlits, extents,
-- min(max(reorgDays, 1), 99999),
-- inserts, deletes, pseudoDel,
-- left(remark, 80)
-- from s100447.tRtsReoIXException
-- where db <> '*' or ts <> '*' or ix <> '*'
-- -- where left(dbName, 1) not in ('1')
-- ;
--lect * from s100447.tReoIXParms
;
CREATE TABLESPACE AREOJPA
IN Db2Reo
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
CREATE TABLE s100447.TReoJobParms
(
PRIO SMALLINT NOT NULL WITH DEFAULT 20,
CONSTRAINT PRIO CHECK (PRIO >= 0 AND PRIO <= 99),
Job CHAR(8) NOT NULL,
GUVON DATE NOT NULL WITH DEFAULT,
GUBIS DATE NOT NULL WITH DEFAULT '31.12.9999',
REMARK CHAR(80) FOR SBCS DATA NOT NULL,
tsTime int WITH DEFAULT NULL,
ixTime int WITH DEFAULT NULL,
uncompDef real with default null,
uncompI0 real with default null,
ixSpae char(1) with default null,
CONSTRAINT ixSpae CHECK (ixSpae in ('i', 't', 'n')),
stats char(1) with default null,
CONSTRAINT stats CHECK (stats in ('n', 'j', 'p', 's')),
updTst TIMESTAMP NOT NULL with default
)
IN Db2Reo.AREOJPA
AUDIT NONE
DATA CAPTURE NONE
CCSID UNICODE
NOT VOLATILE
;
CREATE UNIQUE INDEX s100447.IReoJobParms
ON s100447.TReoJobParms
(PRIO ASC,
job ASC,
GUBIS ASC
)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
LABEL ON TABLE s100447.TReoJobParms
IS 'Ausnahmen+Defaults RtsReo Job'
;
COMMENT ON TABLE s100447.TReoJobParms
IS 'diese Tabelle enthaelt die Default-Schwellwerte und die Jobs mit
speziellen Schwellwerten für RTS-Reorgs.'
;
LABEL ON s100447.TReoJobParms
( PRIO is 'Prioritaet: tiefste=0=Default' ,
job is 'Job Name' ,
GUVON is 'Gueltig von' ,
GUBIS is 'Gueltig bis' ,
tsTime is 'max ReoTime fuer TS in secs' ,
ixTime is 'max ReoTime fuer IX in secs' ,
uncompDef is 'max uncompressed data DPSI' ,
uncompI0 is 'max uncompressed data Default' ,
ixSpae is 'ix nach spaeter: ix, ts, no' ,
stats is 'statsColl: job,part,stats,no' ,
updTst is 'letzte Aenderung' ,
REMARK is 'Begruendung (Freitext)'
)
;
--migr53
-- insert into s100447.tReoJobParms
-- select PRIO, job, guvon, gubis, remark, tsTime, ixTime,
-- 8e10/5, 8e10, translate(ixSpae, 'itn','ITN'), 's', updTst
-- from tstRts.tRtsReoJobException
-- ;
CREATE TABLESPACE AREOJPH
IN Db2Reo
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
CREATE TABLE s100447.TReoJobParmsHist
as (select e.*, ' ' updOp,
current timestamp endTst, ' ' endOp
from s100447.TReoJobParms e
) with no data
IN Db2Reo.AREOJPH
AUDIT NONE
DATA CAPTURE NONE
CCSID UNICODE
NOT VOLATILE
;
LABEL ON TABLE s100447.TReoJobParmsHist
IS 'Ausnahmen+Defs RtsReo History'
;
LABEL ON s100447.TReoJobParmsHist
( updTst is 'activation timestamp'
, updOp is 'activation operation (i,u)'
, endTst is 'deactivation timestamp'
, endOp is 'deactivation operation (u,d)'
)
;
CREATE UNIQUE INDEX s100447.IReoJobParmsHist
ON s100447.TReoJobParmsHist
(PRIO ASC,
job ASC,
guBis ASC,
updTst ASC
)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
--migr53
-- insert into s100447.tReoJobParmsHist
-- select PRIO, job, guvon, gubis, remark, tsTime, ixTime,
-- uncompSz/5, uncompSz, ixSpae, stats,
-- updTst, updOp, endTst, endOp
-- from tstRts.tRtsReoJobExceptionHist
--;
TERMINATOR? ;
create trigger s100447.TReoJobParmsBefIns
no cascade before insert on s100447.TReoJobParms
referencing New as n
for each row mode db2sql begin atomic
set updTst = current timestamp;
end
?
create trigger s100447.TReoJobParmsBefUpd
no cascade before update on s100447.TReoJobParms
referencing New as n Old as o
for each row mode db2sql begin atomic
set updTst = current timestamp;
end
?
create trigger s100447.TReoJobParmsAftIns
after insert on s100447.TReoJobParms
referencing New_table as n
for each statement mode db2sql begin atomic
insert into s100447.TReoJobParmsHist
select n.*, 'i', '9999-12-31-23.59.59', ' '
from n
;
end
?
create trigger s100447.TReoJobParmsAftUpdSt
after update on s100447.TReoJobParms
referencing New as n old as o
for each row mode db2sql begin atomic
update s100447.TReoJobParmsHist h
set endTst = n.updTst, endOp = 'u'
where h.PRIO = o.PRIO
and h.job = o.job
and h.guBis = o.guBis
and h.updTst = o.updTst
;
end
?
create trigger s100447.TReoJobParmsAftUpd
after update on s100447.TReoJobParms
referencing New_table as n old_table as o
for each statement mode db2sql begin atomic
insert into s100447.TReoJobParmsHist
select n.*, 'u', '9999-12-31-23.59.59', ' '
from n
;
end
?
create trigger s100447.TReoJobParmsAftDelSt
after delete on s100447.TReoJobParms
referencing old as o
for each row mode db2sql begin atomic
update s100447.TReoJobParmsHist h
set endTst = current timestamp, endOp = 'd'
where h.PRIO = o.PRIO
and h.job = o.job
and h.guBis = o.guBis
and h.updTst = o.updTst
;
end
?
TERMINATOR; ?
--migr44 --migr00
insert into s100447.tReoJobParms
( PRIO, job, tsTime, ixTime, uncompDef, uncompI0,
ixSpae, stats, remark
) values (
0, '*', 18000, 10800, 1e11/5, 1e11, 't', 's',
'globale default Schwellwerte'
)
;
-- select * from s100447.tReoJobParms ;
--;
CREATE TABLESPACE ADBSTA
IN Db2Reo
USING STOGROUP GSMS
SEGSIZE 64
CCSID UNICODE
BUFFERPOOL BP2
LOCKSIZE ANY
COMPRESS YES;
CREATE TABLE s100447.tDbState
(db CHAR(12) NOT NULL,
sp CHAR(12) NOT NULL,
paFr INTEGER NOT NULL,
paTo INTEGER NOT NULL,
ty char(1) not null,
sta char(20) not null
)
IN Db2Reo.ADBSTA
CCSID UNICODE;
--
LABEL ON TABLE s100447.TDbState IS 'spaceStatus';
--
COMMENT ON TABLE s100447.tDbState IS 'space status restrict/advisory';
--
LABEL ON s100447.tDbState
(db IS 'db name',
sp IS 'name of index/tablespace',
paFr is 'partition from',
paTo is 'partition to',
ty is 'type: Ix Tb Db @',
sta is 'status of db or space'
);
CREATE UNIQUE INDEX s100447.iDbStateA1
ON s100447.tDbState
(db asc ,
sp asc ,
paFr asc
)
CLUSTER
USING STOGROUP GSMS
BUFFERPOOL BP1;
insert into s100447.tDbState values (
'', '', 0, 0, '@', '');
CREATE TABLESPACE AREORJA
IN Db2Reo
USING STOGROUP GSMS
SEGSIZE 64
CCSID UNICODE
BUFFERPOOL BP2
LOCKSIZE ANY
COMPRESS YES;
CREATE TABLE s100447.tReoRunJob
(tst timestamp NOT NULL,
constraint primaryKey primary key (tst),
job CHAR(8) NOT NULL,
ty char(2) NOT NULL,
tyInp char(2) NOT NULL,
sta char(1) not null,
eoj timestamp
)
IN Db2Reo.AREORJA
CCSID UNICODE;
--
COMMENT ON TABLE s100447.tReoRunJob IS 'rtsReo Job Status';
LABEL ON TABLE s100447.tReoRunJob IS 'rtsReo Job Status';
LABEL ON s100447.tReoRunJob
(tst IS 'creation timestamp primaryKey',
job IS 'Job',
ty is 'rtsReo Type: TS or IX',
tyInp is 'rtsReo Type from jobParameter',
sta is 'Status: r=reorg, s=r+Spa, 0',
eoj is 'timestamp >= end of job'
);
CREATE UNIQUE INDEX s100447.iReoRunJob1
ON s100447.tReoRunJob
(tst asc)
CLUSTER
USING STOGROUP GSMS
BUFFERPOOL BP1
;
CREATE UNIQUE INDEX s100447.iReoRunJob2
ON s100447.tReoRunJob
(job asc, tst desc)
USING STOGROUP GSMS
BUFFERPOOL BP1
;
--migr53
--insert into s100447.tReoRunJob
-- select * from tstRts.tRtsReoRunJob
-- where job <> '&late'
--;
--commit
--;
CREATE TABLESPACE AREORPA
IN Db2Reo
USING STOGROUP GSMS
SEGSIZE 64
CCSID UNICODE
BUFFERPOOL BP2
LOCKSIZE ANY
COMPRESS YES;
CREATE TABLE s100447.tReoRunPart
(tst timestamp NOT NULL,
constraint parent foreign key (tst)
references s100447.tReoRunJob on delete cascade,
rng int not null,
part smallInt not null,
constraint primaryKey primary key (tst, rng, part),
paVon smallInt not null,
paBis smallInt not null,
rngI0 int not null,
dbId smallInt not null,
spId smallInt not null,
ty char(1) not null,
sta char(1) not null,
reason char(50) not null,
db char(8) not null,
sp char(8) not null,
reoTst timestamp,
reoTime int
)
IN Db2Reo.AREORPA
CCSID UNICODE;
--
COMMENT ON TABLE s100447.tReoRunPart IS 'rtsReo Job Parts';
LABEL ON TABLE s100447.tReoRunPart IS 'rtsReo Job Parts';
LABEL ON s100447.tReoRunPart
(tst IS 'creation timestamp Job',
rng IS 'range id',
part IS 'partition',
paVon IS 'range partition von',
paBis IS 'range partition bis',
rngI0 IS 'range für i0',
dbId IS 'dbId',
spId is 'psId or isoBid',
ty is 't=TS, i=Ix',
sta is 'r=reorg, s=Spa, 0=i0',
reason is 'reason of reorg',
db is 'database',
sp is 'table/indexSpace',
reoTst is 'timestamp of end of reorg'
);
CREATE UNIQUE INDEX s100447.iReoRunPart1
ON s100447.tReoRunPart
(tst asc, rng asc, part asc)
CLUSTER
USING STOGROUP GSMS
BUFFERPOOL BP1
;
--insert into s100447.tReoRunPart
-- select * from tstRts.tRtsReoRunPart
--;
--commit
--;
CREATE TABLESPACE AREORTA
IN Db2Reo
USING STOGROUP GSMS
SEGSIZE 64
CCSID UNICODE
BUFFERPOOL BP2
LOCKSIZE ANY
COMPRESS YES;
CREATE TABLE s100447.tReoRunTSStats
as (select current timestamp tst, int(1) rng, s.*
from sysibm.sysTableSpaceStats s
) with no data
IN Db2Reo.AREORTA
CCSID UNICODE;
CREATE UNIQUE INDEX s100447.iReoRunTSStats1
ON s100447.tReoRunTSStats
(tst asc, rng asc, partition asc)
CLUSTER
USING STOGROUP GSMS
BUFFERPOOL BP1
;
alter TABLE s100447.tReoRunTSStats
add constraint primaryKey primary key (tst, rng, partition);
alter TABLE s100447.tReoRunTSStats
add constraint parent foreign key (tst, rng, partition)
references s100447.tReoRunPart on delete cascade
;
--migr53
--insert into s100447.tReoRunTSStats
-- select * from tstRts.tRtsReoRunTSStats
--;
--commit
--;
CREATE TABLESPACE AREORIA
IN Db2Reo
USING STOGROUP GSMS
SEGSIZE 64
CCSID UNICODE
BUFFERPOOL BP2
LOCKSIZE ANY
COMPRESS YES;
CREATE TABLE s100447.tReoRunIXStats
as (select current timestamp tst, int(1) rng, s.*
from sysibm.sysIndexSpaceStats s
) with no data
IN Db2Reo.AREORIA
CCSID UNICODE;
CREATE UNIQUE INDEX s100447.iReoRunIXStats1
ON s100447.tReoRunIXStats
(tst asc, rng asc, partition asc, isoBid asc)
CLUSTER
USING STOGROUP GSMS
BUFFERPOOL BP1
;
alter TABLE s100447.tReoRunIXStats
add constraint primaryKey primary key (tst,rng,partition,isobid);
alter TABLE s100447.tReoRunIXStats
add constraint parent foreign key (tst, rng, partition)
references s100447.tReoRunPart on delete cascade
;
--migr53
--insert into s100447.tReoRunIXStats
-- select * from tstRts.tRtsReoRunIXStats
--;
commit
;
$]
if $mig == 59 then $@=[
-- mig==59: migrate stats tables from db2v9 to db2v10 format
alter TABLE s100447.tReoRunTSStats
add REORGCLUSTERSENS BIGINT WITH DEFAULT NULL;
alter TABLE s100447.tReoRunTSStats
add REORGSCANACCESS BIGINT WITH DEFAULT NULL;
alter TABLE s100447.tReoRunTSStats
add REORGHASHACCESS BIGINT WITH DEFAULT NULL;
alter TABLE s100447.tReoRunTSStats
add HASHLASTUSED DATE WITH DEFAULT NULL;
alter TABLE s100447.tReoRunTSStats
add DRIVETYPE CHAR(3) FOR MIXED DATA NOT NULL
WITH DEFAULT 'HDD';
alter TABLE s100447.tReoRunTSStats
add LPFACILITY CHAR(1) FOR MIXED DATA WITH DEFAULT NULL;
alter TABLE s100447.tReoRunTSStats
add STATS01 BIGINT WITH DEFAULT NULL;
alter TABLE s100447.tReoRunIXStats
add REORGINDEXACCESS BIGINT WITH DEFAULT NULL;
alter TABLE s100447.tReoRunIXStats
add DRIVETYPE CHAR(3) FOR MIXED DATA NOT NULL
WITH DEFAULT 'HDD';
alter TABLE s100447.tReoRunIXStats
add STATS101 BIGINT WITH DEFAULT NULL
;
commit ;
$]
$/table/
if $fun == 'v' | $fun == 't' then $@=/view/
------------------------------------------------------------------------
-- view vReoTSSchwelle: Schwellwerte pro Catalog Objekt
-- und alle später benötigen Werte aus Catalog Tabellen
--
CREATE VIEW S100447.vReoTSSchwelle AS
with sw as
(
SELECT
CASE WHEN POSSTR(DB, '*') > 0
THEN POSSTR(DB, '*') - 1 ELSE 8 END DBLEN,
CASE WHEN POSSTR(TS, '*') > 0
THEN POSSTR(TS, '*') - 1 ELSE 8 END TSLEN,
substr(right('00' || STRIP(CHAR(PRIO)), 2), 1, 2) prC2,
e.*
FROM s100447.TReoTSParms e
)
select p.dbName db, p.tsName ts, p.partition part,
min(s.dbId) dbid, min(s.psId) psId,
min(s.pgSize) pgSize, min(s.partitions) partitions,
min(s.clone) clone, min(s.instance) base,
min(p.createdTS) createdTS, min(p.compress) compress,
min(p.pageSave) pageSave ,
min(smallint(round(
case when p.freePage > 0 then p.freePage/real(1+p.freePage)
else 1 end
* min(100 - p.pctFree,
case when s.maxrows > 0 and p.avgRowLen > 0
then real(s.maxrows) * p.avgRowLen / pgSize/10.24
else 100 end)
,0))) pctLoad,
substr(max(prC2 || char(reorg )), 3) swReorg ,
int(substr(max(prC2 || char(unclust )), 3)) swUnclust ,
int(substr(max(prC2 || char(clustRatio )), 3)) swClustRatio ,
int(substr(max(prC2 || char(sw.FARINDREF )), 3)) swFARINDREF ,
int(substr(max(prC2 || char(sw.NEARINDREF)), 3)) swNEARINDREF,
int(substr(max(prC2 || char(sw.EXTENTS )), 3)) swEXTENTS ,
int(substr(max(prC2 || char(REORGDAYS )), 3)) swREORGDAYS ,
int(substr(max(prC2 || char(INSERTS )), 3)) swINSERTS ,
int(substr(max(prC2 || char(UPDATES )), 3)) swUPDATES ,
int(substr(max(prC2 || char(DELETES )), 3)) swDELETES ,
substr(max(prC2 || advisory ), 3) swAdvisory ,
int(substr(max(prC2 || char(datasize )), 3)) swDatasize ,
int(substr(max(prC2 || char(sw.pageSave )), 3)) swPageSave ,
int(substr(max(prC2 || char(rangeI0 )), 3)) swRangeI0 ,
smallint(substr(max(prC2 || char(parallel )), 3)) parallel
from
SYSIBM.SYSTABLEPart p
left join SYSIBM.SYSTABLESPACE S
on s.dbName = p.dbName and s.name = p.tsName
left join sw
on left(p.dbname, dbLen) = left(db, dbLen)
and left(p.tsName, tsLen) = left(ts, tsLen)
and p.partition between partVon and partBis
and current date between guVon and guBis
group by p.dbName, p.tsName, p.partition
;
------------------------------------------------------------------------
-- RTS-Stats Werte pro TS-Partition zu Schwellen joinen
-- Kolonnen Namen: sw*: Schwellen, ix* selbe IndexPartion,
-- i0* nicht part. Indexes für part. TS
--
create view S100447.vReoTsStats as
with ix as -- join ix und i0
(
select s.db, s.ts, s.part,
min(s.dbId) ixDbid, min(s.psId) ixPsId,
min(s.pgSize) pgSize, min(s.partitions) partitions,
min(s.clone) clone, min(s.base) base,
min(s.createdTS) createdTS, min(s.compress) compress,
min(s.pageSave ) pageSave ,
min(s.pctLoad ) pctLoad ,
min(swReorg ) swReorg ,
min(swUnclust ) swUnclust ,
min(swClustRatio) swClustRatio,
min(swFARINDREF ) swFARINDREF ,
min(swNEARINDREF) swNEARINDREF,
min(swEXTENTS ) swEXTENTS ,
min(swREORGDAYS ) swREORGDAYS ,
min(swINSERTS ) swINSERTS ,
min(swUPDATES ) swUPDATES ,
min(swDELETES ) swDELETES ,
min(swAdvisory ) swAdvisory ,
min(swDatasize ) swDatasize ,
min(swPageSave ) swPageSave ,
min(swRangeI0 ) swRangeI0 ,
min(parallel ) swParallel ,
min(substr(strip(t.creator) || '.' || strip(t.name), 1,30)) crTb,
sum(case when j.partition= s.part then 1 else 0 end)
ixParts,
sum(case when j.partition= s.part
then totalEntries else 0 end) ixEnt,
sum(case when j.partition= s.part
then real(j.totalEntries)
* log10(max(1e0, j.totalEntries)) else 0 end) ixEntLog,
sum(case when j.partition= s.part
then real(j.space) * 1024 else 0 end) ixSpc,
sum(case when j.partition= s.part
then real(j.space) * 1024 * log10(
max(1e0, real(j.space) * 1024)) else 0 end) ixSpcLog,
sum(case when j.partition= s.part
then real(j.space) * 1024 * log10(
max(1e0, j.totalEntries)) else 0 end) ixSpcLogEnt,
max(case when j.partition= s.part
then j.totalEntries else 0 end) ixEntMax,
max(case when j.partition= s.part
then real(j.totalEntries) * log10(
max(1e0, j.totalEntries)) else 0 end) ixEntLogMax,
max(case when j.partition= s.part
then real(j.space) * 1024 else 0 end) ixSpcMax,
max(case when j.partition= s.part
then real(j.space) * 1024 * log10(
max(1e0, real(j.space) * 1024)) else 0 end) ixSpcLogMax,
max(case when j.partition= s.part
then real(j.space) * 1024 * log10(
max(1e0, j.totalEntries)) else 0 end) ixSpcLogEntMax,
sum(case when j.partition<>s.part then 1 else 0 end)
i0Parts,
sum(case when j.partition<>s.part
then totalEntries else 0 end) i0Ent,
sum(case when j.partition<>s.part
then real(j.totalEntries)
* log10(max(1e0, j.totalEntries)) else 0 end) i0EntLog,
sum(case when j.partition<>s.part
then real(j.space) * 1024 else 0 end) i0Spc,
sum(case when j.partition<>s.part
then real(j.space) * 1024 * log10(
max(1e0, real(j.space) * 1024)) else 0 end) i0SpcLog,
sum(case when j.partition<>s.part
then real(j.space) * 1024 * log10(
max(1e0, j.totalEntries)) else 0 end) i0SpcLogEnt,
max(case when j.partition<>s.part
then j.totalEntries else 0 end) i0EntMax,
max(case when j.partition<>s.part
then real(j.totalEntries) * log10(
max(1e0, j.totalEntries)) else 0 end) i0EntLogMax,
max(case when j.partition<>s.part
then real(j.space) * 1024 else 0 end) i0SpcMax,
max(case when j.partition<>s.part
then real(j.space) * 1024 * log10(
max(1e0, real(j.space) * 1024)) else 0 end) i0SpcLogMax,
max(case when j.partition<>s.part
then real(j.space) * 1024 * log10(
max(1e0, j.totalEntries)) else 0 end) i0SpcLogEntMax,
max(case when i.clustering <> 'Y' then -9
when i.statstime < '2008-01-01-00.00.00' then -8
when i.fullKeyCardf < 100 then -7
when i.clusterratioF <= 0.0 then -6
else clusterRatio end) clusterRatio
from
S100447.vReoTSSchwelle s
left join sysIbm.sysTables t
on t.dbName = s.db and t.tsName = s.ts
-- and t.dbid = s.dbid
and t.type = 'T'
left join sysIbm.sysIndexes i
on i.tbCreator = t.creator and i.tbName = t.name
-- and s.db = i.dbName and s.dbId = i.dbId
left join SYSIBM.SYSINDEXSPACESTATS j
on j.creator = i.creator and j.Name = i.name
and j.dbName = i.dbName and j.indexSpace = i.indexSpace
and j.dbId = i.dbId and j.isoBid = i.isoBid
-- and j.dbId = s.dbId and j.dbName = s.db
and j.creator = i.creator and j.Name = i.name
and j.partition in (0, s.part)
and (j.partition = 0
or j.partition = s.part)
group by s.db, s.ts, s.part
) , tsPa as
( -- join tsPart Stats and calculate first formulas
select i0PARTS * 2.8568338 + i0SpcMax * 1.4917387E-07
+ i0SpcLogMax * 3.4002310E-09 i0Time,
i.*,
max(CAST(r.totalRows as REAL), 100) rRows,
max(coalesce(r.reorgLasttime, i.createdTs)
,coalesce(r.loadRLasttime, i.createdTs)) lastBuilt,
coalesce(real(r.datasize),
real(r.nActive) * i.pgSize * 1024 ) dataSz,
case when r.uncompressedDatasize > 0
and real(r.nActive) * i.pgSize * 102400
/ min(max(100-i.pageSave, 3), 100)
between real(r.uncompressedDatasize) / 7
and real(r.uncompressedDatasize) * 7
then real(r.uncompressedDatasize)
else
real(r.nActive) * i.pgSize * 102400
/ min(max(100-i.pageSave, 3), 100)
end uncompSz,
r.*
from ix i
left join sysIbm.sysTableSpaceStats r
on i.db = r.dbName and i.ts = r.name
and i.ixDbId = r.dbId and i.ixPsId = r.psId
and i.part = r.partition
) -- remaining formulas
select real(2.7397410 + 1 * 2.2918106) -- const + tsParts *
+ rRows * 1.1719944E-05
+ real(space) * (1024 * 4.1178398E-08)
+ uncompSz * 4.7357392E-09
+ ixPARTS * 5.9228624
+ ixSPC * 1.1593550E-08
+ i0Time reorgTime
, tsPa.*
from tsPa
;
------------------------------------------------------------------------
-- vReoTS: SchwellwertUeberschreitungen herausfinden
-- in riesigem case statement
--
create view S100447.vReoTS as
with ts1 as
(
select
case
when swReorg = 'NEVER' then 'no - reorgNever'
when staDb.sta like 'RO%' or staDb.sta like 'STOP%'
then 'no - db status ' || strip(staDb.sta)
when staTs.sta like 'RO%' or staTs.sta like 'STOP%'
then 'no - ts status ' || strip(staTs.sta)
when ',' || strip(staTs.sta) || ',' like '%,REORP,%'
then 'pending reorp ' || strip(stats.sta)
when ',' || strip(staTs.sta) || ',' like '%,AREOR,%'
then 'pending areor ' || strip(stats.sta)
when swReorg = 'ALWAYS' then 'reorgAlways'
when swAdvisory = '1'
and ',' || strip(staTs.sta) || ',' like '%,AREO*,%'
then 'pending areo* ' || strip(staTs.sta)
when dbName is null then 'rtsMissing'
when REORGLASTTIME IS NULL and LOADRLASTTIME IS NULL
then 'rtsNull'
when rRows is null then 'rtsRowsNull'
when totalRows < 0 then 'rtsRows ' || strip(char(totalRows))
|| ' < 0'
when REORGUNCLUSTINS > rRows / 100 * swUnclust
then 'unclust ' || strip(char(REORGUNCLUSTINS)) || ' > '
|| strip(char(swUnclust)) || '% of ' || char(totalRows)
when REORGFARINDREF > rRows / 100 * swFarindref
then 'farIndRef ' || strip(char(reorgFarIndRef)) || ' > '
|| strip(char(swFarIndRef))||'% of '
|| strip(char(totalRows))
when REORGNEARINDREF > rRows / 100 * swNearindref
then 'nearIndRef '||strip(char(reorgnearIndRef)) || ' > '
|| strip(char(swNearIndRef))||'% of '
|| strip(char(totalRows))
when r.extents > swExtents
then 'extents ' || strip(char(r.extents)) || ' > '
|| strip(char(swExtents))
when r.dataSize >= 0 and r.dataSize
< real(nActive) * r.pgSize
* 0.1024 * pctLoad * swdatasize - 1e7
then 'datasize ' || strip(char(real(r.datasize)))
|| ' < ' || strip(char(swdatasize)) || '% activeSz '
|| strip(char(real(nActive) * r.pgSize * 1024))
when compress = 'Y' and pageSave < swpageSave
and reorgInserts > rRows * .9 and rRows > 1e4
then 'pagesave ' || strip(char(pagesave))
|| ' < ' || strip(char(swPageSave)) || ' % '
when reorgInserts > rRows / 100 * swInserts
then 'inserts ' || strip(char(reorgInserts)) || ' > '
|| strip(char(swInserts)) || '% of '
|| strip(char(totalRows))
when reorgUpdates > rRows / 100 * swUpdates
then 'updates ' || strip(char(reorgUpdates)) || ' > '
|| strip(char(swUpdates)) || '% of '
|| strip(char(totalRows))
when reorgDeletes > rRows / 100 * swDeletes
then 'deletes ' || strip(char(reorgDeletes)) || ' > '
|| strip(char(swDeletes)) || '% of '
|| strip(char(totalRows))
when clusterRatio >= 0 and totalRows > 1000
and clusterRatio < swClustRatio
then 'clusterRatio ' || strip(char(clusterRatio))
|| ' < ' || strip(char(swClustRatio))
when CURRENT TIMESTAMP - swReorgDays days > lastBuilt
then 'lastBuilt ' || char(date(lastBuilt)) || ' older '
|| strip(char(swReorgDays)) ||' reorgDays'
else 'no - reorg not required'
end reason,
staTs.paFr, staTs.paTo, staTs.sta staTs, staDb.sta staDb,
1 parts,
r.*
from S100447.vReoTsStats r
left join s100447.tDbState staTs
ON staTs.Ty = 'T'
and staTs.db = r.db
AND staTs.sp = r.ts
AND staTs.paFr <= r.part
AND staTs.paTo >= r.part
left join s100447.tDbState staDb
ON staDb.Ty = 'D'
and staDb.db = r.db
)
-- importance anfügen
select smallint(case
when reason = 'no - reorg not required' then 0
when reason like 'no %' then -1
when reason like 'lastBuilt %' then 3
when reason like 'clusterRatio %' then 3
when reason like 'pending areo*%' then 7
when reason like 'pending %' then 11
when reason like 'reorgAlway%' then 9
else 5
end) importance ,
ts1.*
from ts1
;
------------------------------------------------------------------------
-- view vReoIXSchwelle: Schwellwerte pro Catalog Objekt
-- und alle später benötigen Werte aus Catalog Tabellen
--
create view S100447.vReoIXSchwelle as
with sw as
( select
CASE WHEN POSSTR(DB, '*') > 0
THEN POSSTR(DB, '*') - 1 ELSE 8 END DBLEN,
CASE WHEN POSSTR(TS, '*') > 0
THEN POSSTR(TS, '*') - 1 ELSE 8 END TSLEN,
CASE WHEN POSSTR(IX, '*') > 0
THEN POSSTR(IX, '*') - 1 ELSE 20 END IXLEN,
substr(right('00' || STRIP(CHAR(PRIO)), 2), 1, 2) prC2,
e.*
from s100447.tReoIXParms e
)
select i.creator cr, i.name ix, p.partition part,
substr(max(prC2 || char(reorg )), 3) swReorg ,
int(substr(max(prC2 || char(pageSplits )), 3)) swPageSplits,
int(substr(max(prC2 || char(sw.EXTENTS )), 3)) swEXTENTS ,
int(substr(max(prC2 || char(REORGDAYS )), 3)) swREORGDAYS ,
int(substr(max(prC2 || char(INSERTS )), 3)) swINSERTS ,
int(substr(max(prC2 || char(DELETES )), 3)) swDELETES ,
int(substr(max(prC2 || char(pseudoDel )), 3)) swPseudoDel ,
min(i.dbName) db, min(i.indexSpace) is, min(t.tsName) ts,
min(i.dbId) dbId, min(i.isoBid) isoBid,
min(p.createdTS) createdTS,
min(t.creator) tbCr, min(t.name) tb,
min(s.clone) clone, min(s.instance) base
from
SYSIBM.sysIndexes I
join SYSIBM.SYSTABLES T
on i.tbCreator = t.creator and i.tbName = t.name
and i.dbName = t.dbName
join SYSIBM.SYSTABLESPACE S
on t.dbName = s.dbName and t.tsName = s.name
join SYSIBM.SYSIndexPart p
on p.ixCreator = i.creator and p.ixName = i.name
, sw
where left(i.dbName, dbLen) = left(db, dbLen)
and left(i.name, ixLen) = left(ix, ixLen)
and left(t.tsName, tsLen) = left(ts, tsLen)
and p.partition between partVon and partBis
and current date between guVon and guBis
group by i.creator, i.name, p.partition
;
------------------------------------------------------------------------
-- RTS-Stats Werte pro ix-Partition zu Schwellen joinen
-- Kolonnen Namen: sw*: Schwellen
--
create view S100447.vReoIxStats as
select cr, ix, part, db, is, ts, tbCr, tb, createdTS, clone, base,
swReorg , swPageSplits, swEXTENTS , swREORGDAYS ,
swINSERTS , swDELETES , swPseudoDel ,
max(CAST(TOTALEntries AS REAL), 100) rEntrs,
cast(nActive AS REAL) rActive,
max(coalesce(reorgLastTime, createdTS),
coalesce(loadrLastTime, createdTS),
coalesce(rebuildLastTime, createdTS)) lastBuilt ,
coalesce(real(max(r.space, 0)) * 1024 * 1.3558420E-07 , 0)
+ 1.8626988 reorgTime ,
r.*
from S100447.vReoIXSchwelle s
left join sysibm.sysIndexSpaceStats r
on r.DBID = S.DBID
AND r.ISOBID = S.ISOBID
AND r.DBNAME = S.DB
AND r.indexSpace = S.is
AND r.partition = s.part
;
------------------------------------------------------------------------
-- vReoIx: SchwellwertUeberschreitungen herausfinden
-- in riesigem case statement
--
create view S100447.vReoIX as
with ix1 as
(
select s.*
, value(
( select 'no - db status ' || strip(d.sta)
from s100447.tDbState d
where d.sta like 'STOP%'
and d.Ty = 'D' and d.db = s.db
fetch first row only
)
, ( select 'no - ts status ' || strip(t.sta)
from s100447.tDbState t
where t.sta like 'STOP%'
and t.Ty = 'T'
and t.db = s.db
AND t.sp = s.ts
AND t.paFr <= s.part
AND t.paTo >= s.part
fetch first row only
)
, ( select substr(min(
case when i.sta like 'RO%' or i.sta like 'STOP%'
or i.sta like '%RBD%'
then '1no - ix status ' || strip(i.sta)
when ',' || strip(i.sta) || ',' like '%,REORP,%'
then '2pending reorp ' || strip(i.sta)
when ',' || strip(i.sta) || ',' like '%,AREOR,%'
then '3pending areor ' || strip(i.sta)
else null end), 2)
from s100447.tDbState i
where i.Ty = 'I'
and i.db = s.db
AND i.sp = s.is
and ( i.paFr = 0 or s.part = 0
or ( i.paFr <= s.part
AND i.paTo >= s.part ))
) ) xSta
from S100447.vReoIXStats s
)
, ix2 as
( select
case
when swReorg = 'NEVER' then 'no - reorgNever'
when xSta is not null then xSta
when swReorg = 'ALWAYS' then 'reorgAlways'
when indexSpace is null then 'rtsMissing'
when REORGLASTTIME IS NULL and LOADRLASTTIME IS NULL
and REBUILDLASTTIME IS NULL then 'rtsNull'
when rEntrs is null then 'rtsRowsNull'
when totalEntries < 0 then 'rtsEntries '
|| strip(char(totalEntries)) || ' < 0'
when REORGLEAFFAR > rActive / 100 * swPageSplits
then 'pageSplits ' || strip(char(reorgLeafFar)) || ' > '
|| strip(char(swPageSplits))
|| '% of ' || strip(char(nActive))
when extents > swExtents
then 'extents ' || strip(char(extents)) || ' > '
|| strip(char(swExtents))
when reorgInserts > rEntrs / 100 * swInserts
then 'inserts ' || strip(char(reorgInserts)) || ' > '
|| strip(char(swInserts)) || '% of '
|| strip(char(totalEntries))
when reorgDeletes > rEntrs / 100 * swDeletes
then 'deletes ' || strip(char(reorgDeletes)) || ' > '
|| strip(char(swDeletes)) || '% of '
|| strip(char(totalEntries))
when reorgPseudoDeletes > rEntrs / 100 * swPseudoDel
then 'pseudoDel ' || strip(char(reorgPseudoDeletes))||' > '
|| strip(char(swPseudoDel)) || '% of '
|| strip(char(totalEntries))
when CURRENT TIMESTAMP - swReorgDays days > lastBuilt
then 'lastBuilt ' || char(date(lastBuilt)) || ' older '
|| strip(char(swReorgDays)) ||' reorgDays'
else 'no - reorg not required'
end reason, s.*
from ix1 s
)
-- importance voranstellen
select smallint(case
when reason = 'no - reorg not required' then 0
when reason like 'no %' then -1
when reason like 'lastBuilt %' then 3
when reason like 'pending areo*%' then 7
when reason like 'pending %' then 11
when reason like 'reorgAlway%' then 9
else 5
end) importance,
ix2.*
from ix2
;
------------------------------------------------------------------------
-- vReoJobParms: jobLen und prC2 anfügen
--
CREATE VIEW S100447.vReoJobParms AS
SELECT e.*,
CASE WHEN POSSTR(job, '*') > 0
THEN POSSTR(job, '*') - 1 ELSE 8 END jobLEN,
substr(right('00' || STRIP(CHAR(PRIO)), 2), 1, 2) prC2
FROM s100447.TReoJobParms e
;
CREATE VIEW S100447.vReoRunTsStats AS
with tp as
( select
real(totalRows) rows
, log10(max(real(totalRows),1)) logRows
, real(space) * 1024 spc
, log10(max(real(space), 1)*1024) logSpc
, real(uncompressedDatasize) uds
, log10(max(real(uncompressedDatasize), 1)) logUds
, r.*
from s100447.tReoRunTsStats r
)
, t as
( select tst, rng
, min(partition) partMin
, max(partition) partMax
, count(*) parts
, sum(rows) rows
, max(rows) rowsMax
, sum(rows * logrows) rowsLog
, sum(rows) * max(logRows) rowsLogMax
, sum(spc) spc
, sum(spc * logSpc) spcLog
, sum(spc * logRows) spcLogRows
, max(spc) spcMax
, sum(spc) * max(logSpc) spcLogMax
, sum(Uds) Uds
, sum(Uds * logUds) UdsLog
, sum(Uds * logRows) UdsLogRows
, max(Uds) UdsMax
, sum(Uds) * max(logUds) UdsLogMax
from tp
group by tst, rng
)
, ip as
( select
real(totalEntries) ent,
log10(max(real(totalEntries),1)) logEnt,
real(space) * 1024 spc,
log10(max(real(space), 1)*1024) logSpc,
r.*
from s100447.tReoRunIxStats r
)
, i as
( select tst, rng, count(*) parts
, sum(ent) ent
, max(ent) entMax
, sum(ent * logEnt) entLog
, sum(ent) * max(logEnt) entLogMax
, sum(spc) spc
, sum(spc * logSpc) spcLog
, sum(spc * logEnt) spcLogEnt
, max(spc) spcMax
, sum(spc) * max(logSpc) spcLogMax
from ip
group by tst, rng
)
select j.job, r.*
, partMin tsPartMin
, partMax tsPartMax
, value(t .parts , 0) tsParts
, value(t .rows , 0) tsRows
, value(t .rowsMax , 0) tsRowsMax
, value(t .rowsLog , 0) tsRowsLog
, value(t .rowsLogMax , 0) tsRowsLogMax
, value(t .spc , 0) tsSpc
, value(t .spcLog , 0) tsSpcLog
, value(t .spcLogRows , 0) tsSpcLogRows
, value(t .spcMax , 0) tsSpcMax
, value(t .spcLogMax , 0) tsSpcLogMax
, value(t .Uds , 0) tsUds
, value(t .UdsLog , 0) tsUdsLog
, value(t .UdsLogRows , 0) tsUdsLogRows
, value(t .UdsMax , 0) tsUdsMax
, value(t .UdsLogMax , 0) tsUdsLogMax
, value(ix.parts , 0) ixparts
, value(ix.ent , 0) ixent
, value(ix.entMax , 0) ixentMax
, value(ix.entLog , 0) ixentLog
, value(ix.entLogMax , 0) ixentLogMax
, value(ix.spc , 0) ixspc
, value(ix.spcLog , 0) ixspcLog
, value(ix.spcLogEnt , 0) ixspcLogEnt
, value(ix.spcMax , 0) ixspcMax
, value(ix.spcLogMax , 0) ixspcLogMax
, value(i0.parts , 0) i0parts
, value(i0.ent , 0) i0ent
, value(i0.entMax , 0) i0entMax
, value(i0.entLog , 0) i0entLog
, value(i0.entLogMax , 0) i0entLogMax
, value(i0.spc , 0) i0spc
, value(i0.spcLog , 0) i0spcLog
, value(i0.spcLogEnt , 0) i0spcLogEnt
, value(i0.spcMax , 0) i0spcMax
, value(i0.spcLogMax , 0) i0spcLogMax
from s100447.tReoRunJob j, s100447.tReoRunPart r
left join t on t.tst = r.tst and t.rng = r.rng
left join i ix on ix.tst = r.tst and ix.rng = r.rng
left join i i0 on i0.tst = r.tst and i0.rng = r.rngI0
and r.rngI0 > 0
where j.ty = 'TS' and j.sta <> '0' and r.ty = 't' and r.sta = 'r'
and r.part = r.pavon and r.reoTime is not null
and r.tst = j.tst
;
CREATE VIEW S100447.vReoRunIxStats AS
with ip as
( select
real(totalEntries) ent,
log10(max(1, real(totalEntries))) logEnt,
real(space) * 1024 spc,
log10(max(1, real(space)*1024)) logSpc,
r.*
from s100447.tReoRunIxStats r
)
, i as
( select tst, rng, count(*) parts
, min(partition) partMin
, max(partition) partMax
, sum(ent) ent
, max(ent) entMax
, sum(ent * logEnt) entLog
, sum(ent) * max(logEnt) entLogMax
, sum(spc) spc
, sum(spc * logSpc) spcLog
, sum(spc * logEnt) spcLogEnt
, max(spc) spcMax
, sum(spc) * max(logSpc) spcLogMax
from ip
group by tst, rng
)
select j.job, r.*
, partMin partMin
, partMax partMax
, value(ix.parts , 0) parts
, value(ix.ent , 0) ent
, value(ix.entMax , 0) entMax
, value(ix.entLog , 0) entLog
, value(ix.entLogMax , 0) entLogMax
, value(ix.spc , 0) spc
, value(ix.spcLog , 0) spcLog
, value(ix.spcLogEnt , 0) spcLogEnt
, value(ix.spcMax , 0) spcMax
, value(ix.spcLogMax , 0) spcLogMax
from s100447.tReoRunJob j, s100447.tReoRunPart r
left join i ix on ix.tst = r.tst and ix.rng = r.rng
where j.ty = 'IX' and j.sta <> '0' and r.ty = 'i' and r.sta = 'r'
and r.part = r.pavon and r.reoTime is not null
and r.tst = j.tst
;
create view S100447.vReoTSStatsPlus as
select r.*, s.pgSize, p.pageSave,
case when r.uncompressedDatasize > 0
and real(r.nActive) * s.pgSize * 102400
/ min(max(100-p.pageSave, 3), 100)
between real(r.uncompressedDatasize) / 7
and real(r.uncompressedDatasize) * 7
then bigInt(r.uncompressedDatasize)
else
bigInt(real(r.nActive) * s.pgSize * 102400
/ min(max(100-p.pageSave, 3), 100))
end fixUncomp
from sysibm.sysTableSpaceStats r,
sysibm.sysTableSpace s, sysibm.sysTablePart p
where s.dbName = r.dbName and s.name = r.name
and s.dbid = r.dbId and s.psid = r.psid
and p.dbName = r.dbName and p.tsName = r.name
and p.partition = r.partition
;
create view S100447.vReoTSStatsFix as
select UPDATESTATSTIME
, NACTIVE
, NPAGES
, EXTENTS
, LOADRLASTTIME
, REORGLASTTIME
, REORGINSERTS
, REORGDELETES
, REORGUPDATES
, REORGUNCLUSTINS
, REORGDISORGLOB
, REORGMASSDELETE
, REORGNEARINDREF
, REORGFARINDREF
, STATSLASTTIME
, STATSINSERTS
, STATSDELETES
, STATSUPDATES
, STATSMASSDELETE
, COPYLASTTIME
, COPYUPDATEDPAGES
, COPYCHANGES
, COPYUPDATELRSN
, COPYUPDATETIME
, IBMREQD
, DBID
, PSID
, PARTITION
, INSTANCE
, SPACE
, TOTALROWS
, DATASIZE
, fixUncomp UNCOMPRESSEDDATASIZE
, DBNAME
, NAME
$@[ if $db2v10ENF then $@=[
, REORGCLUSTERSENS -- from here new db2v10 columns
, REORGSCANACCESS
, REORGHASHACCESS
, HASHLASTUSED
, DRIVETYPE
, LPFACILITY
, STATS01
$] $]
from S100447.vReoTSStatsPlus
;
-- check views ------------------------------------------------------
---------------------------------------------------------------------
-- test tReoRunJob (and tReoRunPart.reoTst)
-- message e not null ==> error message ==> see case statement below
-- eo2 = calculate better eoj
---------------------------------------------------------------------
create view s100447.vReoRunJobChk as
with p as -- part gruppiert nach job, nur ty='r'
(select tst, ty, count(*) cnt,
count(reotst) cntReo,
min(reoTst) reoTstVon,
max(reoTst) reoTstBis
from s100447.tReoRunPart
where sta = 'r'
group by tst, ty
), j as -- join to job
(
select j.*,
p.ty pTy, p.cnt pCnt, p.cntReo,
p.reoTstVon, p.reoTstBis,
(select min(tst)
from s100447.tReoRunJob a
where a.job = j.job and a.tst > j.tst
) nextJob
from s100447.tReoRunJob j
left join p
on j.tst = p.tst and p.ty = lower(left(j.ty, 1))
)
----> diese case statement macht error message <-------------
select case when eoj is null and nextJob is not null
then 'null not last'
when reoTstVon < tst then 'reoTst < tst'
when reoTstBis > nextJob then 'reoTst > nextJob'
when reoTstBis > eoj then 'reoTst > eoj'
when eoj > nextJob then 'overlap nextJob'
when reoTstBis - tst > 1000000 then 'reoTst > 1 day'
-- when eoj - tst > 1000000 then 'eoj > 1 day'
else null
end e,
case when pCnt is null or pCnt < 1 then tst
when reoTstBis is not null
and (nextJob is null or nextJob > reoTstBis)
and (tst < current timestamp - 2 days
or pCnt = cntReo) then reoTstBis
when nextJob is not null then
nextJob
else null
end eo2, j.*
from j
;
---------------------------------------------------------------------
-- check tReoRunPart
-- message e not null ==> error message ==> see case statement below
-- newTime = calculate reoTime from current values
---------------------------------------------------------------------
create view S100447.vReoRunRngChk as
with r as -- part gruppiert nach range
(
select
(select j.job from s100447.tReoRunJob j where j.tst = p.tst) job,
p.tst, p.rng,
min(ty) ty,
count(distinct ty) cdTy,
min(sta) sta,
count(distinct sta) cdSta,
sum(case when part = paVon then 1 else 0 end) cPaVon,
sum(case when part = paBis then 1 else 0 end) cPaBis,
count(*) cnt,
count(distinct part) cdPart,
count(reoTst) cReoTst,
min(reoTst) minReoTst,
max(reoTst) maxReoTst,
(select max(b.reoTst) -- letzter reoTst aus vorgehenden Ranges
from s100447.tReoRunPart b
where b.tst = p.tst and b.rng < p.rng) befReoTst,
(select min(sta) || max(sta) -- status und reoTst aus Range -1
|| case when count(*) = count(reotst) then '=' else '0' end
|| coalesce(char(max(reoTst)), '-')
from s100447.tReoRunPart b
where b.tst = p.tst and b.rng = p.rng-1) r1ReoTst,
(select min(sta) || max(sta) -- status und reoTst aus Range -2
|| case when count(*) = count(reotst) then '=' else '0' end
|| coalesce(char(max(reoTst)), '-')
from s100447.tReoRunPart b
where b.tst = p.tst and b.rng = p.rng-2) r2ReoTst,
max(case when part = paVon then reoTime else null end) reoTime
from s100447.tReoRunPart p
group by tst, rng
) , s as -- begTst aus range -1 und range -2 berechnen
( select r.*,
case when rng = 1 then tst
when left(r1ReoTst, 3) = 'rr='
then timestamp(substr(r1ReoTst, 4))
when left(r1ReoTst, 2) <> '00' then null
when rng = 2 then tst
when left(r2ReoTst, 3) = 'rr='
then timestamp(substr(r2ReoTst, 4))
else null
end begTst
from r
), t as -- aktuelle reoZeit berechnen
( select s.*,
(days(maxReoTst) - days(begTst)) * 86400
+ midnight_seconds(maxReoTst) - midnight_seconds(begTst)
+ 1e-6 * (microsecond(maxReoTst)-microsecond(begTst)) newTime
from s
)
select ---> case statement macht error message <--------------------
case when job is null then 'job missing'
when cdTy <> 1 then 'ty not unique'
when cdSta <> 1 then 'sta not unique'
when cPaVon <> 1 then 'part=paVon not unique'
when cPaBis <> 1 then 'part=paBis not unique'
when cdPart <> cnt then 'part not distinct'
when minReoTst < tst then 'reoTst < tst'
-- rng waren früher anders sortiert
when tst < '2011-09-29-00.00.00' then null
when befReoTst > minReoTst then 'before ReoTst > minReoTst'
when abs(reoTime-newTime) >= 1 then 'reoTime <> newTime'
else null
end e,
t.*
from t
;
commit
;
--- end of install mig=$mig----------------------------------
$/view/
$/ddl/