zOs/SQL/GBGRDDL
-- ddl gbGrenzen
-- 2. 5.16 mit lastDataChange etc. in view
-- 7. 1.16 mit validBegin validEnd in Zuerichtime
-- and updateStatsTime and loadTs original
-- 6. 1.16 mit origStatsTime original
-- und updateStatsTime shifted to local time
-- 19. 2.15 v11 Kolonnen
-- 12.12.14 Zusätzliche Cols in vQZ006GBGRENZE für Elar subSelect
--
set current sqlid = 'S100447';
$@ if 1 then $@=/view/ $***** recreate view **************************
drop view OA1P.vQZ006GBGRENZE ;
create view OA1P.vQZ006GBGRENZE as
with t as -- Daten der neuesten Table Partitionen (von t2)
(
select t.rz, t.dbSys, t.dbName, t.Name, min(t.tsTy) tsTy
, smallInt(t.partition * t.limPart) partition
, t.instance
, min(t.limGb) limGb
, min(t.parts) parts
, min(t.clone) clone
, min(t.tsInst) tsInst
, max(t.updateStatsTime) updateStatsTime
, max(validBegin) validBegin
, real(sum(real(t.nActive) * t.pgSize / 1048576)) actGB
, sum(cast(t.nActive as bigInt)) nActive
, sum(cast(t.nPages as bigInt)) nPages
, sum(t.space) space
, sum(t.totalRows) totalRows
, sum(t.dataSize) dataSize
, sum(cast(t.REORGINSERTS as bigInt)) REORGINSERTS
, max(lastDataChange) lastDataChange
, max(t.pgSize) pgSize
from OA1P.TQZ006GBGRTSSTATS t
where current timestamp >= validBegin
and current timestamp < validEnd
group by t.rz, t.dbSys, t.dbName, t.name
, smallInt(t.partition * t.limPart)
, t.instance
)
, i as -- Daten der neuest Index Partitionen (wie i2)
(
select i.*
, real(real(nActive) * ixPgSz / 1048576) actGB
from OA1P.TQZ007GBGRIxSTATS i
where current timestamp >= validBegin
and current timestamp < validEnd
)
-- ix und ts daten union
, u (limGb, actGb, db, ts, ix, part, inst, rz, dbSys
, tsTy, tsLimGb, tsParts, tsClone, tsInst
, updateStatsTime, validBegin
, nActive, nPages, space, totalRows, dataSize
, reorgInserts, lastDataChange, pgSize
) as
(
select limGb, actGb, dbName, name, ' --ts--'
, partition, instance, rz, dbSys
, tsTy, limGb, parts, clone , tsInst
, updateStatsTime, validBegin
, nActive, nPages, space, totalRows, dataSize
, reorgInserts, lastDataChange, pgSize
from t
union all select limGb, actGb, dbName, ts , name
, partition, instance, rz, dbSys
, tsTy, tslimGb, tsParts, tsClone, tsInst
, updateStatsTime, validBegin
, nActive, nPages, space, totalEntries
, cast( null as bigint)
, reorgInserts, cast(null as timestamp), ixPgSz
from i
)
, s as -- passenden Schwellwert aus MQT lesen
(
select u.*
, (select max(info) from oa1p.tqz008GbGrSchweExp e
where e.rz = u.rz and e.dbSys = u.dbSys
and left(e.db , e.dbLen) = left(u.db , e.dbLen)
and left(e.ts , e.tsLen) = left(u.ts , e.tsLen)
and e.part in(u.part, 0)
and e.tsTy in (u.tsTy, ' ')
and e.dsMin <= u.tslimGB
and validBegin <= current date
and validEnd > current date
) schwInfo
from u
)
, v as -- Schwelle extrahieren oder Fehler falls null
(
select int(case when schwInfo is not null
then int(substr(schwInfo, 16, 6))
else raise_error(70001, 'schwelle null ts='
|| db || '.' || ts || '#' || part)
end) schwelle
, s.*
from s
-- order by db, ts, part, ix
)
select * from v
;
commit;
$/view/
$@ if 0 then $@=/altV11/ $***** alter columns vor v11 *****************
alter TABLE OA1P.TQZ007GbGrIxSTATS
alter COPYUPDATELRSN set data type CHARACTER(10) -- FOR BIT DATA
;
alter TABLE OA1P.tqz006gbgrtsStats
alter COPYUPDATELRSN set data type CHARACTER(10) -- FOR BIT DATA
-- v11 columns
add UPDATESIZE BIGINT
WITH DEFAULT NULL
add LASTDATACHANGE TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
;
$/altV11/
$@ if 0 then $@=/creStatsH/ $***** create stats tables 7.1.16 ****
?rop TABLESPACE qz01a1p.a007a;
commit ;
?rop TABLESPACE qz01a1p.a006a;
commit ;
;?;
-- ts stats -----------
CREATE TABLESPACE a006a
IN qz01a1p
numparts 15
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255
;
--
CREATE TABLE OA1P.tqz006gbgrtsStats
(
primary key (rz, dbSys, dbName, name, partition, instance
, validBegin)
-- , business_time without overlaps)
, rz char(3) not null with default '?'
, dbSys char(4) not null with default '?'
, validBegin TIMESTAMP(6) WITHOUT TIME ZONE not null with default
, validEnd timestamp(6) WITHOUT TIME ZONE not null
with default '9999-12-30-00.00.00'
constraint validCheck
check ( validEnd > validBegin )
, loadTS TIMESTAMP(6) WITHOUT TIME ZONE not null
with default '9999-12-30-00.00.00'
, tsType char(1) not null
, tsTy char(1) not null
, pgSize smallint not null
, segSize smallint not null
, parts smallint not null
, maxParts smallint not null
, dsSize int not null
, dsGB real not null
, limGB real not null
, limPart smallint not null
, obId smallint not null
, clone char(1) not null
, tsInst smallint not null
, tbCr varchar(128) not null
, tb varchar(128) not null
, tbTy char(1) not null
, tbObId smallint not null
, UPDATESTATSTIME TIMESTAMP (6) WITHOUT TIME ZONE not null
,NACTIVE INTEGER
WITH DEFAULT NULL
,NPAGES INTEGER
WITH DEFAULT NULL
,EXTENTS SMALLINT
WITH DEFAULT NULL
,LOADRLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,REORGLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,REORGINSERTS INTEGER
WITH DEFAULT NULL
,REORGDELETES INTEGER
WITH DEFAULT NULL
,REORGUPDATES INTEGER
WITH DEFAULT NULL
,REORGUNCLUSTINS INTEGER
WITH DEFAULT NULL
,REORGDISORGLOB INTEGER
WITH DEFAULT NULL
,REORGMASSDELETE INTEGER
WITH DEFAULT NULL
,REORGNEARINDREF INTEGER
WITH DEFAULT NULL
,REORGFARINDREF INTEGER
WITH DEFAULT NULL
,STATSLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,STATSINSERTS INTEGER
WITH DEFAULT NULL
,STATSDELETES INTEGER
WITH DEFAULT NULL
,STATSUPDATES INTEGER
WITH DEFAULT NULL
,STATSMASSDELETE INTEGER
WITH DEFAULT NULL
,COPYLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,COPYUPDATEDPAGES INTEGER
WITH DEFAULT NULL
,COPYCHANGES INTEGER
WITH DEFAULT NULL
,COPYUPDATELRSN CHARACTER(10) FOR BIT DATA
WITH DEFAULT NULL
,COPYUPDATETIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,IBMREQD CHARACTER(1) FOR MIXED DATA
NOT NULL
,DBID SMALLINT
NOT NULL
,"PSID" SMALLINT
NOT NULL
,"PARTITION" SMALLINT
NOT NULL
,INSTANCE SMALLINT
NOT NULL
WITH DEFAULT 1
,SPACE BIGINT
WITH DEFAULT NULL
,TOTALROWS BIGINT
WITH DEFAULT NULL
,DATASIZE BIGINT
WITH DEFAULT NULL
,UNCOMPRESSEDDATASIZE BIGINT
WITH DEFAULT NULL
,DBNAME char(8)
NOT NULL
WITH DEFAULT
,"NAME" char(8)
NOT NULL
WITH DEFAULT
,REORGCLUSTERSENS BIGINT
WITH DEFAULT NULL
,REORGSCANACCESS BIGINT
WITH DEFAULT NULL
,REORGHASHACCESS BIGINT
WITH DEFAULT NULL
,HASHLASTUSED DATE
WITH DEFAULT NULL
,DRIVETYPE CHARACTER(3) FOR MIXED DATA
NOT NULL
WITH DEFAULT 'HDD'
,LPFACILITY CHARACTER(1) FOR MIXED DATA
WITH DEFAULT NULL
,STATS01 BIGINT
WITH DEFAULT NULL
-- v11 columns
, UPDATESIZE BIGINT
WITH DEFAULT NULL
, LASTDATACHANGE TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,CONSTRAINT DSNTSS_INSTANCE CHECK
(INSTANCE = 1 OR INSTANCE = 2
)
)
in qz01a1p.a006A
partition by (rz, dbSys, dbName)
( partition 1 ending ('?')
, partition 2 ending ('RQ2')
, partition 3 ending ('RR2', 'DBOF')
, partition 4 ending ('RR2' )
, partition 5 ending ('RZ2', 'DBOF', 'NI03A1P' )
, partition 6 ending ('RZ2', 'DBOF' )
, partition 7 ending ('RZ2', 'DP2G' )
, partition 8 ending ('RZ2', 'DVBP', 'XBDNR001' )
, partition 9 ending ('RZ2', 'DVBP', 'XBFC2002' )
, partition 10 ending ('RZ2', 'DVBP' )
, partition 11 ending ('RZ4' )
, partition 12 ending ('RZX')
, partition 13 ending ('RZY')
, partition 14 ending ('RZZ', 'DE0G', 'NI03A1P' )
, partition 15 ending ('RZZ')
)
AUDIT NONE
DATA CAPTURE changes
CCSID unicode
NOT VOLATILE;
CREATE UNIQUE INDEX oa1p.iQZ006A1
ON oa1p.tqz006gbGrTsStats
(rz, dbsys, dbName, name, partition, instance, validBegin desc )
include(validEnd, updateStatsTime, loadTS)
partitioned
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
;
commit
;
-- index stats ---------------------------------------------------------
CREATE TABLESPACE a007A
IN qz01a1p
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
numparts 15
dssize 16 g
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255
;
--
CREATE TABLE OA1P.tqz007gbGrIxStats
( primary key (rz, dbSys, dbName, ts, indexSpace, partition, instance
, validBegin)
, rz char(3) not null with default '?'
, dbSys char(4) not null with default '?'
, validBegin TIMESTAMP(6) WITHOUT TIME ZONE not null with default
, validEnd timestamp(6) WITHOUT TIME ZONE not null
with default '9999-12-30-00.00.00'
constraint validCheck
check ( validEnd > validBegin )
, loadTS TIMESTAMP(6) WITHOUT TIME ZONE not null
with default '9999-12-30-00.00.00'
, indexType char(1) not null
, compress char(1) not null
, ixParts smallint not null
, ixPgSz smallint not null
, pieceSize int not null
, pieceGB real not null
, limGB real not null
, tbCreator varchar(128) not null
, tbName varchar(128) not null
, ts char(8) not null
, tsTy char(1) not null
, tsParts smallint not null
, tsClone char(1) not null
, tsInst smallInt not null
, tsDsSize int not null
, tsDsGb real not null
, tsLimGB real not null
, tsLimPart smallint not null
, tsPgSz smallint not null
, UPDATESTATSTIME TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL
,NLEVELS SMALLINT
WITH DEFAULT NULL
,NPAGES INTEGER
WITH DEFAULT NULL
,NLEAF INTEGER
WITH DEFAULT NULL
,NACTIVE INTEGER
WITH DEFAULT NULL
,SPACE INTEGER
WITH DEFAULT NULL
,EXTENTS SMALLINT
WITH DEFAULT NULL
,LOADRLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,REBUILDLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,REORGLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,REORGINSERTS INTEGER
WITH DEFAULT NULL
,REORGDELETES INTEGER
WITH DEFAULT NULL
,REORGAPPENDINSERT INTEGER
WITH DEFAULT NULL
,REORGPSEUDODELETES INTEGER
WITH DEFAULT NULL
,REORGMASSDELETE INTEGER
WITH DEFAULT NULL
,REORGLEAFNEAR INTEGER
WITH DEFAULT NULL
,REORGLEAFFAR INTEGER
WITH DEFAULT NULL
,REORGNUMLEVELS INTEGER
WITH DEFAULT NULL
,STATSLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,STATSINSERTS INTEGER
WITH DEFAULT NULL
,STATSDELETES INTEGER
WITH DEFAULT NULL
,STATSMASSDELETE INTEGER
WITH DEFAULT NULL
,COPYLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,COPYUPDATEDPAGES INTEGER
WITH DEFAULT NULL
,COPYCHANGES INTEGER
WITH DEFAULT NULL
,COPYUPDATELRSN CHARACTER(10) FOR BIT DATA
WITH DEFAULT NULL
,COPYUPDATETIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,LASTUSED DATE
WITH DEFAULT NULL
,IBMREQD CHARACTER(1) FOR MIXED DATA
NOT NULL
,DBID SMALLINT
NOT NULL
,"ISOBID" SMALLINT
NOT NULL
,"PSID" SMALLINT
NOT NULL
,"PARTITION" SMALLINT
NOT NULL
,INSTANCE SMALLINT
NOT NULL
WITH DEFAULT 1
,TOTALENTRIES BIGINT
WITH DEFAULT NULL
,DBNAME char(8) not null
,"NAME" VARCHAR(128) FOR MIXED DATA
NOT NULL
WITH DEFAULT
,CREATOR VARCHAR(128) FOR MIXED DATA
NOT NULL
WITH DEFAULT
,INDEXSPACE char(8) not null
WITH DEFAULT
,REORGINDEXACCESS BIGINT
WITH DEFAULT NULL
,DRIVETYPE CHARACTER(3) FOR MIXED DATA
NOT NULL
WITH DEFAULT 'HDD'
,STATS101 BIGINT
WITH DEFAULT NULL
,CONSTRAINT DSNISS_INSTANCE CHECK
(INSTANCE = 1 OR INSTANCE = 2
)
)
partition by (rz, dbSys, dbName)
( partition 1 ending ('?')
, partition 2 ending ('RQ2')
, partition 3 ending ('RR2', 'DBOF')
, partition 4 ending ('RR2' )
, partition 5 ending ('RZ2', 'DBOF', 'NI03A1P' )
, partition 6 ending ('RZ2', 'DBOF' )
, partition 7 ending ('RZ2', 'DP2G' )
, partition 8 ending ('RZ2', 'DVBP', 'XBDNR001' )
, partition 9 ending ('RZ2', 'DVBP', 'XBFC2002' )
, partition 10 ending ('RZ2', 'DVBP' )
, partition 11 ending ('RZ4' )
, partition 12 ending ('RZX')
, partition 13 ending ('RZY')
, partition 14 ending ('RZZ', 'DE0G', 'NI03A1P' )
, partition 15 ending ('RZZ')
)
in qz01a1p.a007A
AUDIT NONE
DATA CAPTURE changes
CCSID unicode
NOT VOLATILE;
CREATE UNIQUE INDEX oa1p.iQZ007A1
ON oa1p.tqz007gbGrIxStats
(rz, dbsys, dbName, ts, indexSpace, partition, instance
, validBegin )
include(validEnd, updateStatstime, loadTs)
partitioned
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
$/creStatsH/
$@ if 0 then $@=/creStatG/ $***** create stats tables 5.1.16 *******
xrop TABLESPACE qz01a1p.a006a;
commit ;
xrop TABLESPACE qz01a1p.a007a;
commit ;
;
-- ts stats -----------
CREATE TABLESPACE a006a
IN qz01a1p
numparts 15
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255
;
--
CREATE TABLE OA1P.tqz006gbgrtsStats
( state char(1) not null with default '?'
, rz char(3) not null with default '?'
, dbSys char(4) not null with default '?'
, tsType char(1) not null
, tsTy char(1) not null
, pgSize smallint not null
, segSize smallint not null
, parts smallint not null
, maxParts smallint not null
, dsSize int not null
, dsGB real not null
, limGB real not null
, limPart smallint not null
, obId smallint not null
, clone char(1) not null
, tsInst smallint not null
, tbCr varchar(128) not null
, tb varchar(128) not null
, tbTy char(1) not null
, tbObId smallint not null
, loadTS TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL WITH DEFAULT
, ORIGSTATSTIME TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL WITH DEFAULT
, UPDATESTATSTIME TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL WITH DEFAULT
,NACTIVE INTEGER
WITH DEFAULT NULL
,NPAGES INTEGER
WITH DEFAULT NULL
,EXTENTS SMALLINT
WITH DEFAULT NULL
,LOADRLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,REORGLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,REORGINSERTS INTEGER
WITH DEFAULT NULL
,REORGDELETES INTEGER
WITH DEFAULT NULL
,REORGUPDATES INTEGER
WITH DEFAULT NULL
,REORGUNCLUSTINS INTEGER
WITH DEFAULT NULL
,REORGDISORGLOB INTEGER
WITH DEFAULT NULL
,REORGMASSDELETE INTEGER
WITH DEFAULT NULL
,REORGNEARINDREF INTEGER
WITH DEFAULT NULL
,REORGFARINDREF INTEGER
WITH DEFAULT NULL
,STATSLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,STATSINSERTS INTEGER
WITH DEFAULT NULL
,STATSDELETES INTEGER
WITH DEFAULT NULL
,STATSUPDATES INTEGER
WITH DEFAULT NULL
,STATSMASSDELETE INTEGER
WITH DEFAULT NULL
,COPYLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,COPYUPDATEDPAGES INTEGER
WITH DEFAULT NULL
,COPYCHANGES INTEGER
WITH DEFAULT NULL
,COPYUPDATELRSN CHARACTER(10) FOR BIT DATA
WITH DEFAULT NULL
,COPYUPDATETIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,IBMREQD CHARACTER(1) FOR MIXED DATA
NOT NULL
,DBID SMALLINT
NOT NULL
,"PSID" SMALLINT
NOT NULL
,"PARTITION" SMALLINT
NOT NULL
,INSTANCE SMALLINT
NOT NULL
WITH DEFAULT 1
,SPACE BIGINT
WITH DEFAULT NULL
,TOTALROWS BIGINT
WITH DEFAULT NULL
,DATASIZE BIGINT
WITH DEFAULT NULL
,UNCOMPRESSEDDATASIZE BIGINT
WITH DEFAULT NULL
,DBNAME char(8)
NOT NULL
WITH DEFAULT
,"NAME" char(8)
NOT NULL
WITH DEFAULT
,REORGCLUSTERSENS BIGINT
WITH DEFAULT NULL
,REORGSCANACCESS BIGINT
WITH DEFAULT NULL
,REORGHASHACCESS BIGINT
WITH DEFAULT NULL
,HASHLASTUSED DATE
WITH DEFAULT NULL
,DRIVETYPE CHARACTER(3) FOR MIXED DATA
NOT NULL
WITH DEFAULT 'HDD'
,LPFACILITY CHARACTER(1) FOR MIXED DATA
WITH DEFAULT NULL
,STATS01 BIGINT
WITH DEFAULT NULL
-- v11 columns
, UPDATESIZE BIGINT
WITH DEFAULT NULL
, LASTDATACHANGE TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,CONSTRAINT DSNTSS_INSTANCE CHECK
(INSTANCE = 1 OR INSTANCE = 2
)
)
in qz01a1p.a006a
partition by (rz, dbSys, dbName)
( partition 1 ending ('?')
, partition 2 ending ('RQ2')
, partition 3 ending ('RR2', 'DBOF')
, partition 4 ending ('RR2' )
, partition 5 ending ('RZ2', 'DBOF', 'NI03A1P' )
, partition 6 ending ('RZ2', 'DBOF' )
, partition 7 ending ('RZ2', 'DP2G' )
, partition 8 ending ('RZ2', 'DVBP', 'XBDNR001' )
, partition 9 ending ('RZ2', 'DVBP', 'XBFC2002' )
, partition 10 ending ('RZ2', 'DVBP' )
, partition 11 ending ('RZ4' )
, partition 12 ending ('RZX')
, partition 13 ending ('RZY')
, partition 14 ending ('RZZ', 'DE0G', 'NI03A1P' )
, partition 15 ending ('RZZ')
)
AUDIT NONE
DATA CAPTURE changes
CCSID unicode
NOT VOLATILE;
CREATE UNIQUE INDEX oa1p.iQZ006a1
ON oa1p.tqz006gbGrTsStats
(rz, dbsys, dbName, name, partition, instance, updateStatsTime desc)
include(state, origStatsTime, loadTS)
partitioned
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
; $*( ???????????
CREATE UNIQUE INDEX oa1p.iQZ006a2
ON oa1p.tqz006gbGrTsStats
(rz, dbsys, dbName, name, partition, instance, loadTS desc)
include(state, origstatstime, updateStatsTime)
partitioned
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
not CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
DEFER YES
; ???????? $*)
commit
;
-- index stats ---------------------------------------------------------
CREATE TABLESPACE a007a
IN qz01a1p
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
numparts 15
dssize 16 g
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255
;
--
CREATE TABLE OA1P.tqz007gbGrIxStats
( state char(1) not null with default '?'
, rz char(3) not null with default '?'
, dbSys char(4) not null with default '?'
, indexType char(1) not null
, compress char(1) not null
, ixParts smallint not null
, ixPgSz smallint not null
, pieceSize int not null
, pieceGB real not null
, limGB real not null
, tbCreator varchar(128) not null
, tbName varchar(128) not null
, ts char(8) not null
, tsTy char(1) not null
, tsParts smallint not null
, tsClone char(1) not null
, tsInst smallInt not null
, tsDsSize int not null
, tsDsGb real not null
, tsLimGB real not null
, tsLimPart smallint not null
, tsPgSz smallint not null
, loadTs TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL WITH DEFAULT
, origSTATSTIME TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL WITH DEFAULT
, UPDATESTATSTIME TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL WITH DEFAULT
,NLEVELS SMALLINT
WITH DEFAULT NULL
,NPAGES INTEGER
WITH DEFAULT NULL
,NLEAF INTEGER
WITH DEFAULT NULL
,NACTIVE INTEGER
WITH DEFAULT NULL
,SPACE INTEGER
WITH DEFAULT NULL
,EXTENTS SMALLINT
WITH DEFAULT NULL
,LOADRLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,REBUILDLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,REORGLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,REORGINSERTS INTEGER
WITH DEFAULT NULL
,REORGDELETES INTEGER
WITH DEFAULT NULL
,REORGAPPENDINSERT INTEGER
WITH DEFAULT NULL
,REORGPSEUDODELETES INTEGER
WITH DEFAULT NULL
,REORGMASSDELETE INTEGER
WITH DEFAULT NULL
,REORGLEAFNEAR INTEGER
WITH DEFAULT NULL
,REORGLEAFFAR INTEGER
WITH DEFAULT NULL
,REORGNUMLEVELS INTEGER
WITH DEFAULT NULL
,STATSLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,STATSINSERTS INTEGER
WITH DEFAULT NULL
,STATSDELETES INTEGER
WITH DEFAULT NULL
,STATSMASSDELETE INTEGER
WITH DEFAULT NULL
,COPYLASTTIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,COPYUPDATEDPAGES INTEGER
WITH DEFAULT NULL
,COPYCHANGES INTEGER
WITH DEFAULT NULL
,COPYUPDATELRSN CHARACTER(10) FOR BIT DATA
WITH DEFAULT NULL
,COPYUPDATETIME TIMESTAMP (6) WITHOUT TIME ZONE
WITH DEFAULT NULL
,LASTUSED DATE
WITH DEFAULT NULL
,IBMREQD CHARACTER(1) FOR MIXED DATA
NOT NULL
,DBID SMALLINT
NOT NULL
,"ISOBID" SMALLINT
NOT NULL
,"PSID" SMALLINT
NOT NULL
,"PARTITION" SMALLINT
NOT NULL
,INSTANCE SMALLINT
NOT NULL
WITH DEFAULT 1
,TOTALENTRIES BIGINT
WITH DEFAULT NULL
,DBNAME char(8) not null
,"NAME" VARCHAR(128) FOR MIXED DATA
NOT NULL
WITH DEFAULT
,CREATOR VARCHAR(128) FOR MIXED DATA
NOT NULL
WITH DEFAULT
,INDEXSPACE char(8) not null
WITH DEFAULT
,REORGINDEXACCESS BIGINT
WITH DEFAULT NULL
,DRIVETYPE CHARACTER(3) FOR MIXED DATA
NOT NULL
WITH DEFAULT 'HDD'
,STATS101 BIGINT
WITH DEFAULT NULL
,CONSTRAINT DSNISS_INSTANCE CHECK
(INSTANCE = 1 OR INSTANCE = 2
)
)
partition by (rz, dbSys, dbName)
( partition 1 ending ('?')
, partition 2 ending ('RQ2')
, partition 3 ending ('RR2', 'DBOF')
, partition 4 ending ('RR2' )
, partition 5 ending ('RZ2', 'DBOF', 'NI03A1P' )
, partition 6 ending ('RZ2', 'DBOF' )
, partition 7 ending ('RZ2', 'DP2G' )
, partition 8 ending ('RZ2', 'DVBP', 'XBDNR001' )
, partition 9 ending ('RZ2', 'DVBP', 'XBFC2002' )
, partition 10 ending ('RZ2', 'DVBP' )
, partition 11 ending ('RZ4' )
, partition 12 ending ('RZX')
, partition 13 ending ('RZY')
, partition 14 ending ('RZZ', 'DE0G', 'NI03A1P' )
, partition 15 ending ('RZZ')
)
in qz01a1p.a007a
AUDIT NONE
DATA CAPTURE changes
CCSID unicode
NOT VOLATILE;
CREATE UNIQUE INDEX oa1p.iQZ007a1
ON oa1p.tqz007gbGrIxStats
(rz, dbsys, dbName, ts, indexSpace, partition
, instance, updateStatsTime desc)
include(state, origStatstime, loadTs)
partitioned
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
$/creStatG/
$@ if 0 then $@=/creSchw/ $***** create SchwellWert Tb ****************
xrop TABLESPACE qz01a1p.a008A;
xrop TABLESPACE qz01a1p.a008H;
xrop TABLESPACE qz01a1p.a008E;
commit
;
CREATE TABLESPACE a008A
IN QZ01A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
maxpartitions 16
dsSize 4 G
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255;
;
CREATE TABLESPACE a008H
IN QZ01A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
maxpartitions 16
dsSize 4 G
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255;
--
CREATE TABLE OA1P.tqz008GbGrSchwelle
( prio smallint not null
, constraint cprio check (prio >= 0 and prio <= 99
or prio >= -99 and prio <= -70)
, rz CHAR(3) not null
, dbSys CHAR(4) not null
, db CHAR(8) not null
, ts CHAR(8) not null
, part smallint not null
, constraint cpart check (part >= 0 and part <= 4096)
, tsTy char(1) not null
, dsMin int not null
, constraint cDsMin check (dsMin between 0 and 999999)
, schwelle smallint not null
, constraint cschwelle check ( schwelle between 0 and 100)
, validBegin date NOT NULL WITH DEFAULT
, validEnd date NOT NULL WITH DEFAULT
, erfasser char(8) not null
, grUse smallint not null with default -99
, grund varchar(160) not null
, rowBegin not null generated always as row begin
, rowEnd not null generated always as row end
, rowtrans not null generated always as transaction start id
, period business_time (validBegin, validEnd)
, period system_time (rowBegin, rowEnd)
, primary key (prio, rz, dbSys, db, ts, part, tsTy, dsMin
, business_time without overlaps)
)
in QZ01A1P.a008A
AUDIT NONE
DATA CAPTURE changes
CCSID unicode
NOT VOLATILE;
CREATE UNIQUE INDEX oa1p.iqz008S1
ON oa1p.tqz008GbGrSchwelle
( rz, dbSys, db, ts, part, tsTy, dsMin, prio
, business_time without overlaps )
include (schwelle)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
CREATE INDEX oa1p.iqz008S2
ON oa1p.tqz008GbGrSchwelle
(rz || dbSys || left(db, 1), db, ts, part, tsTy, dsMin, prio
, validBegin, validEnd, schwelle)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
not CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
CREATE TABLE oa1p.tqz008GbGrSchwHist
like oa1p.tqz008GbGrSchwelle
in QZ01A1P.a008H
AUDIT NONE
DATA CAPTURE changes
CCSID unicode
NOT VOLATILE
;
CREATE INDEX oa1p.Iqz008H1
ON oa1p.tqz008GbGrSchwHist
(prio, rz, dbSys, db, ts, part, tsTy, dsMin, validBegin)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
;
alter table oa1p.tqz008GbGrSchwelle add versioning
use history table oa1p.tqz008GbGrSchwHist
;
CREATE TABLESPACE a008E
IN QZ01A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
maxpartitions 16
dsSize 4 G
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255;
;
CREATE TABLE OA1P.tqz008GbGrSchweExp as
(
select
gRZ rz
, gSYS dbSys
, DB
, TS
, PART
, TSTY
, DSMIN
, SCHWELLE
, char(value(INFO, ''), 50) info
, smallint(value(DBLEN, 99)) dbLen
, smallint(value(tsLen, 99)) TSLEN
, VALIDBEGIN
, VALIDEND
from
( -- t
select
row_number() over(partition by gRz, gSys, db, ts, part
, tsTy, dsMin
order by info desc
) rn
, g.*, s2.*
from
( -- g
select rz gRz, dbSys gSys
from oa1p.tqz008gbGrSchwelle
where prio = -99
) g join
( -- s2
select right(' ' || prio, 3) || rzLen || syLen || dbLen || tsLen
|| case when part = 0 then 'a' else 'p' end
|| tsTy || right(' ' || dsMin, 5)
|| '?' || right(' ' || schwelle, 6)
|| '@' || left(strip(rz) ||'/'|| strip(dbSys) ||':'|| strip(db)
|| '.' || strip(ts) ||'#'|| strip(part) || tsTy || dsMin
, 28) info
, s1.*
from
( -- s1
select posStr(translate(left(rz , 3)||'%', '%','*'), '%') -1 rzLen
, posStr(translate(left(dbSys, 3)||'%', '%','*'), '%') -1 syLen
, posStr(translate(left(db , 8)||'%', '%','*'), '%') -1 dbLen
, posStr(translate(left(ts , 8)||'%', '%','*'), '%') -1 tsLen
, s.*
from oa1p.tqz008gbGrSchwelle s
where prio >= 0
) s1
) s2
on left(grz, rzLen) = left(rz, rzLen)
and left(gSys, syLen) = left(dbSys, syLen)
) t
where rn = 1 and validEnd > current date
) data initially deferred refresh deferred
maintained by system disable query optimization
in QZ01A1P.a008E
AUDIT NONE
DATA CAPTURE changes
CCSID unicode
NOT VOLATILE
;
CREATE INDEX oa1p.iqz008E1 -- unique not allowed on MQT|
ON oa1p.tqz008GbGrSchweExp
( rz, dbSYs, info, db, ts, part, tsTy, dsMin
, dbLen, tsLen, validBegin, validEnd, schwelle)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
$/creSchw/
$@ if 0 then $@=/creSchwTri/
xrop trigger oa1p.tqz008GbGrSchwelleIns ;
xrop trigger oa1p.tqz008GbGrSchwelleUpd ;
xrop trigger oa1p.tqz008GbGrSchwelleDel ;
create trigger oa1p.tqz008GbGrSchwelleIns after insert
on oa1p.tqz008GbGrSchwelle
for each statement mode db2sql
refresh table oa1p.tqz008GbGrSchweExp
;
create trigger oa1p.tqz008GbGrSchwelleUpd after update
on oa1p.tqz008GbGrSchwelle
for each statement mode db2sql
refresh table oa1p.tqz008GbGrSchweExp
;
create trigger oa1p.tqz008GbGrSchwelleDel after delete
on oa1p.tqz008GbGrSchwelle
for each statement mode db2sql
refresh table oa1p.tqz008GbGrSchweExp
;
refresh table oa1p.tqz008GbGrSchweExp;
;
commit
;
$/creSchwTri/
$@ if 0 then $@=/creHist/
-- ddl gbGrenzen
-- History Tabelle für alte Version
-- 6. 1.16 neu
--
set current sqlid = 'S100447';
CREATE TABLESPACE a006J
IN qz01a1p
numparts 15
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255
;
CREATE TABLE OA1P.tqz006gbgrTsHjjj
like OA1P.tqz006gbgrTsStats
in qz01A1p.a006J
partition by (rz, dbSys, dbName)
( partition 1 ending ('?')
, partition 2 ending ('RQ2')
, partition 3 ending ('RR2', 'DBOF')
, partition 4 ending ('RR2' )
, partition 5 ending ('RZ2', 'DBOF', 'NI03A1P' )
, partition 6 ending ('RZ2', 'DBOF' )
, partition 7 ending ('RZ2', 'DP2G' )
, partition 8 ending ('RZ2', 'DVBP', 'XBDNR001' )
, partition 9 ending ('RZ2', 'DVBP', 'XBFC2002' )
, partition 10 ending ('RZ2', 'DVBP' )
, partition 11 ending ('RZ4' )
, partition 12 ending ('RZX')
, partition 13 ending ('RZY')
, partition 14 ending ('RZZ', 'DE0G', 'NI03A1P' )
, partition 15 ending ('RZZ')
)
AUDIT NONE
DATA CAPTURE changes
CCSID unicode
NOT VOLATILE;
;
CREATE UNIQUE INDEX oa1p.iQZ006j1
ON oa1p.tqz006gbGrTsHjjj
(rz, dbsys, dbName, name, partition, instance, updateStatsTime desc)
include(state, loadTs, origStatsTime)
partitioned
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
;
CREATE TABLESPACE a007J
IN qz01a1p
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
numparts 15
dssize 16 g
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255
;
CREATE TABLE OA1P.tqz007gbGrIxHjjj
like OA1P.tqz007gbGrIxStats
in qz01A1p.a007j
partition by (rz, dbSys, dbName)
( partition 1 ending ('?')
, partition 2 ending ('RQ2')
, partition 3 ending ('RR2', 'DBOF')
, partition 4 ending ('RR2' )
, partition 5 ending ('RZ2', 'DBOF', 'NI03A1P' )
, partition 6 ending ('RZ2', 'DBOF' )
, partition 7 ending ('RZ2', 'DP2G' )
, partition 8 ending ('RZ2', 'DVBP', 'XBDNR001' )
, partition 9 ending ('RZ2', 'DVBP', 'XBFC2002' )
, partition 10 ending ('RZ2', 'DVBP' )
, partition 11 ending ('RZ4' )
, partition 12 ending ('RZX')
, partition 13 ending ('RZY')
, partition 14 ending ('RZZ', 'DE0G', 'NI03A1P' )
, partition 15 ending ('RZZ')
)
AUDIT NONE
DATA CAPTURE changes
CCSID unicode
NOT VOLATILE;
;
CREATE UNIQUE INDEX oa1p.iQZ007j1
ON oa1p.tqz007gbGrIxHjjj
(rz, dbsys, dbName, ts, indexSpace, partition
, instance, updateStatsTime desc)
include(state, loadTS, origstatstime)
partitioned
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
;
commit
;
$/creHist/
$@ if 0 then $@=/cre666/
?rop tablespace qz01a1p.a666a;
?rop tablespace qz01a1p.a667a;
$/cre666/
$@ if 0 then $@=/creStaTriNew/ $***** create stats ttiggr new *******
?rop TRIGGER oa1p.qz006gbgrtsNewInsert;
CREATE TRIGGER oa1p.qz006gbgrtsNewInsert
-- after insert ON oa1p.tqz006GbGrTsNew
no cascade before insert ON oa1p.tqz006GbGrTsNew
REFERENCING new AS n
FOR EACH ROW MODE DB2SQL
update oa1p.tqz006GbGrTsNew o
set o.validEnd = n.validBegin
where o.rz = n.rz and o.dbSys = n.dbSys
and o.dbName = n.dbName and o.name = n.name
and o.partition = n.partition
and o.instance = n.instance
and n.validBegin > o.validBegin
and n.validBegin < o.validEnd
;
$/creStaTriNew/
$@ if 0 then $@=/creKey/ $***** create key table for mig **************
?ROP TABLESPACE QZ01A1P.a666b;
?ROP TABLESPACE QZ01A1P.a667b;
commit;
;?;
CREATE TABLESPACE a666b
IN qz01a1p
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255
;
--
CREATE TABLE OA1P.tqz666gbgrtsKey
(
rz char(3) not null with default '?'
, dbSys char(4) not null with default '?'
, loadTS TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL WITH DEFAULT
, UPDATESTATSTIME TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL WITH DEFAULT
,di INTEGER not null
)
in qz01a1p.a666b
;
CREATE UNIQUE INDEX oa1p.iQZ666b1
ON oa1p.tqz666gbGrTSKey
(rz, dbsys, loadTs)
include(updateStatstime, di)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
;
commit
;
set current sqlid = 'S100447';
CREATE TABLESPACE a667b
IN qz01a1p
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255
;
--
CREATE TABLE OA1P.tqz667gbgrIxKey
(
rz char(3) not null with default '?'
, dbSys char(4) not null with default '?'
, loadTS TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL WITH DEFAULT
, UPDATESTATSTIME TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL WITH DEFAULT
,di INTEGER not null
)
in qz01a1p.a667b
;
CREATE UNIQUE INDEX oa1p.iQZ667b1
ON oa1p.tqz667gbGrIxKey
(rz, dbsys, loadTs)
include(updateStatstime, di)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
$/creKey/