zOs/SQL/REOCRT44
----------------------------------------------------------------------
-- tRtsReo*Parms und *run* und tdbState
-- tables für rts reorg erstellen
-- default werte einfüllen
-- datenübernahme aus alten exception tables
-- w. keller, 27.09.2010 v5.4
--
-- Teile für Migration sind mit --migr markiert
-- --migr53 Migration von v5.3
-- --migr44 Migration von v4.4
-- --migr00 Neuerstellung, keine alten Tb vorhanden
-- drop/stop auf die alten DB's DB2RTS, TSTRTSDB und DB2REORG
-- muss extra gemacht werden|
--
SET CURRENT SQLID='s100447';
--drop DATABASE Db2Reo;
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 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,
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,
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 'diese Tabelle enthaelt die Default-Schwellwerte und die TS mit s
peziellen Schwellwerten für 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 %' ,
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' ,
updTst is 'letzte Aenderung' ,
REMARK is 'Begründung (Freitext)'
)
;
--migr53
--insert into s100447.tReoTSParms
-- select * from tstRts.tRtsReoTSException
--;
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
;
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;
--migr53
--insert into s100447.tReoTSParmsHist
-- select * from tstRts.tRtsReoTSExceptionHist
--;
--#SET 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
?
--#SET TERMINATOR ;
--migr44 --migr00
insert into s100447.tReoTSParms
( PRIO, DB, TS, partVon, partBis, REORG,
unclust, FARINDREF, nearIndRef, extents, reorgdays,
inserts, updates, deletes,
advisory, dataSize, pageSave, RangeI0,
remark
) values (
0, '*', '*', 0, 9999, 'THRESHOLD',
10, 5, 5, 500, 365,
999999, 999999, 999999,
'1', 50, 5, 200,
'globale default Schwellwerte'
)
;
--migr44
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 <> '*'
;
select * from s100447.tReoTSParms
;
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
--;
--#SET 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
?
--#SET 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')
;
select * 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
--;
--#SET 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
?
--#SET 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
;
--commit
--;
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