zOs/SQL/GBGRMIKT
//A540769T JOB (CP00,KE50),'DB2 REO', 00010000
// MSGCLASS=T,TIME=1440,CLASS=M1, 00020000
// NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2ALL 00030003
//S1 EXEC PGM=DSNUTILB,PARM='DP4G,A540769T.LOAD' 00020001
//SYSMAP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSERR DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTEMPL DD DSN=DP4G.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN DD *
EXEC SQL
DECLARE CURT CURSOR FOR
select
RZ
, DBSYS
, updateStatsTime validBegin
, value ( ( select e.updateStatsTime
from oa1p.tqz006GbGrTsHjjj e
where o.rz = e.rz and o.dbSys = e.dbSys
and o.dbName = e.dbName and o.name = e.name
and o.partition = e.partition
and o.instance = e.instance
and o.updateStatsTime < e.updateStatsTime
order by e.updateStatsTime asc
fetch first 1 row only
), '9999-12-30-00.00.00') validEnd
, LOADTS
, TSTYPE
, TSTY
, PGSIZE
, SEGSIZE
, PARTS
, MAXPARTS
, DSSIZE
, DSGB
, LIMGB
, LIMPART
, OBID
, CLONE
, TSINST
, TBCR
, TB
, TBTY
, TBOBID
, origSTATSTIME 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
, UNCOMPRESSEDDATASIZE
, DBNAME
, NAME
, REORGCLUSTERSENS
, REORGSCANACCESS
, REORGHASHACCESS
, HASHLASTUSED
, DRIVETYPE
, LPFACILITY
, STATS01
, UPDATESIZE
, LASTDATACHANGE
from oa1p.tqz006GbGrTsHjjj o
where state = 'a'
-- and rz = 'RR2'
ENDEXEC
LOAD DATA INCURSOR CURT LOG NO RESUME NO
REPLACE COPYDDN(TCOPYD) STATISTICS INDEX(ALL)
SORTDEVT DISK
WORKDDN(TSYUTS,TSOUTS)
INTO TABLE OA1P.TQZ006GBGRTSSTATS
LISTDEF TPLIST
INCLUDE TABLESPACE QZ01A1P.A006A PARTLEVEL
---- COPY ----------------------------------------
COPY LIST TPLIST COPYDDN(TCOPYD)
FULL YES SCOPE PENDING
PARALLEL
SHRLEVEL CHANGE
//
insert into oa1p.tqz006GbGrTsNew
select
RZ
, DBSYS
, updateStatsTime
, value ( ( select e.updateStatsTime
from oa1p.tqz006GbGrTsStats e
where o.rz = e.rz and o.dbSys = e.dbSys
and o.dbName = e.dbName and o.name = e.name
and o.partition = e.partition
and o.instance = e.instance
and o.updateStatsTime < e.updateStatsTime
order by e.updateStatsTime asc
fetch first 1 row only
), '9999-12-30-00.00.00') validEnd
, LOADTS
, TSTYPE
, TSTY
, PGSIZE
, SEGSIZE
, PARTS
, MAXPARTS
, DSSIZE
, DSGB
, LIMGB
, LIMPART
, OBID
, CLONE
, TSINST
, TBCR
, TB
, TBTY
, TBOBID
, origSTATSTIME
, 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
, UNCOMPRESSEDDATASIZE
, DBNAME
, NAME
, REORGCLUSTERSENS
, REORGSCANACCESS
, REORGHASHACCESS
, HASHLASTUSED
, DRIVETYPE
, LPFACILITY
, STATS01
, UPDATESIZE
, LASTDATACHANGE
from oa1p.tqz006GbGrTsStats o
where state = 'a'
and rz = 'RZZ' -- and dbSys = 'DE0G' and dbName like 'M%'
;
commit
;x;
--t current sqlid = 'S100447';
--ter table oa1p.tqz006GbGrTsNew
-- alter validEnd set default '9999-12-30-00.00.00';
insert into oa1p.tqz006GbGrTsNew
( rz, dbSys -- , validBegin
, LOADTS
, TSTYPE
, TSTY
, PGSIZE
, SEGSIZE
, PARTS
, MAXPARTS
, DSSIZE
, DSGB
, LIMGB
, LIMPART
, OBID
, CLONE
, TSINST
, TBCR
, TB
, TBTY
, TBOBID
, 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
, UNCOMPRESSEDDATASIZE
, DBNAME
, NAME
, REORGCLUSTERSENS
, REORGSCANACCESS
, REORGHASHACCESS
, HASHLASTUSED
, DRIVETYPE
, LPFACILITY
, STATS01
, UPDATESIZE
, LASTDATACHANGE
)
select
'??'
, '??'
-- , current timestamp - 1 year
, LOADTS
, TSTYPE
, TSTY
, PGSIZE
, SEGSIZE
, PARTS
, MAXPARTS
, DSSIZE
, DSGB
, LIMGB
, LIMPART
, OBID
, CLONE
, TSINST
, TBCR
, TB
, TBTY
, TBOBID
, 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
, UNCOMPRESSEDDATASIZE
, DBNAME
, NAME
, REORGCLUSTERSENS
, REORGSCANACCESS
, REORGHASHACCESS
, HASHLASTUSED
, DRIVETYPE
, LPFACILITY
, STATS01
, UPDATESIZE
, LASTDATACHANGE
from oa1p.tqz006GbGrTsNew
where
rz = 'RZZ' and dbSys = 'DE0G' and dbName = 'MF01A1P'
fetch first 1 row only
;
commit; xx;