zOs/JCL/GBGRMIDA
$#@
$*( migrate gbGr Daten
1) unload from tadm09a1
2) load into tmpTable p1
3) group and sum into tmpTable p2
4) remove duplicates and add deletest into tmpTable p3
5) only one per week into tmpTable p4
6) delete last migration from tqz006GbGrTsStats
7) insert migration into tqz006GbGrTsStats
8) remove duplicates from tqz006GbGrTsStats
$*)
$=rz=RZY
$=dbSys=DEVG
$=fun = u
$;
$@unload $>$rz/intRdr
$;
call sleep 5
$;
$@load $>intRdr
$;
$@proc $@=/unload/
//A540769W JOB (CP00,KE50),'DB2 REO', 00010000
// MSGCLASS=T,TIME=1440, 00020000
// NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2 00030003
//*MAIN CLASS=LOG 00040003
//DEL EXEC PGM=IEFBR14,TIME=1440
//SD DD DISP=(MOD,DELETE),
// DSN=A540769.TMPGBGR.$rz.$dbSys.A009A.UNL
//SP DD DISP=(MOD,DELETE),
// DSN=A540769.TMPGBGR.$rz.$dbSys.A009A.PUN
//UNL EXEC PGM=DSNUTILB,TIME=1440,
// PARM=($dbSys,'A540769W.UNLOA'),
// REGION=0M
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DSN=$dbSys.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN DD *
TEMPLATE TUNL DSN('A540769.TMPGBGR.$rz.$dbSys.&SN..UNL')
DATACLAS (NULL30) MGMTCLAS(COM#E005)
SPACE (10,120) CYL
TEMPLATE TPUN DSN('A540769.TMPGBGR.$rz.$dbSys.&SN..PUN')
DATACLAS (NULL12) MGMTCLAS(COM#E005)
SPACE (1,10) TRK
UNLOAD DATA
PUNCHDDN TPUN UNLDDN TUNL
SHRLEVEL REFERENCE
FROM TABLE OA1P.TADM09A1
(
, DB_NAME
, TS_NAME
, PARTITIONS_TOTAL
, PARTITIONS_NR
, DS_SIZE
, LARGE
, HI_U_RBA
, DATUM
)
$/unload/
$@proc $@=/load/
//A540769W JOB (CP00,KE50),'DB2 REO', 00010000
// MSGCLASS=T,TIME=1440, 00020000
// NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2 00030003
//*MAINCLASS=LOG 00040003
//LOAD EXEC PGM=DSNUTILB,TIME=1440,
// PARM=(DP4G,'A540769W.LOAD'),
// REGION=0M
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DSN=DP4G.DBAA.LISTDEF(TEMPL),DISP=SHR
//SU DD DISP=SHR,SUBSYS=(CSM,'SYSTEM=$rz'),
// DSN=A540769.TMPGBGR.$rz.$dbSys.A009A.UNL
//SYSIN DD *
LOAD DATA LOG NO
SORTKEYS
SORTDEVT DISK
WORKDDN(TSYUTS,TSOUTS)
MAPDDN TMAPD ERRDDN TERRD
EBCDIC CCSID(00500,00000,00000)
STATISTICS INDEX(ALL) REPORT NO UPDATE ALL
INTO TABLE OA1P.TQZ006TMP PART 1
RESUME NO REPLACE COPYDDN(TCOPYD)
INDDN SU
(
DB POSITION( 00003:00012) CHAR(00010) STRIP TRAILING
, TS POSITION( 00013:00022) CHAR(00010) STRIP TRAILING
, PARTS POSITION( 00023:00026) INTEGER
, PARTITION POSITION( 00027:00030) INTEGER
, DSSIZE POSITION( 00031:00034) FLOAT(21)
, LARGE POSITION( 00035:00035) CHAR(00001)
, HIUSE POSITION( 00036:00039) FLOAT(21)
, DATUM POSITION( 00040:00049) DATE EXTERNAL
)
EXEC SQL
DECLARE CUR1 CURSOR FOR
SELECT 2 p, DB, TS, value(MAX(PARTS), -99) parts, partition
, value(max(dsSize), -99) dsSize
, value(max(large), '?') large
, value(sum(hiUse) / 1024, -99) hiUse
, datum
FROM OA1P.TQZ006TMP
where p=1 and datum < '22.07.2014'
GROUP BY db, ts, partition, datum
ENDEXEC
LOAD DATA LOG NO
SORTKEYS
SORTDEVT DISK
WORKDDN(TSYUTS,TSOUTS)
MAPDDN TMAPD ERRDDN TERRD
STATISTICS INDEX(ALL) REPORT NO UPDATE ALL
INTO TABLE OA1P.TQZ006TMP PART 2
RESUME NO REPLACE COPYDDN(TCOPYD)
INCURSOR CUR1
EXEC SQL
DECLARE CUR2 CURSOR FOR
with m as
(
SELECT DB, TS, partition, max(datum) datum
FROM OA1P.TQZ006TMP
where p=2
group by DB, TS, partition
) -- remove duplicates
SELECT 3 p, DB, TS, parts, partition, dsSize, large, hiUse, datum
FROM OA1P.TQZ006TMP i
where p=2
and i.hiUse <> (select hiUse FROM OA1P.TQZ006TMP o
where p=2 and o.db = i.db and o.ts = i.ts
and o.partition = i.partition and o.datum < i.datum
order by o.datum desc
fetch first 1 row only
)
union all ( -- add deleted
select 3 p, db, ts, -77 parts, partition
, -77 dsSize, '-' large, 0 hiUse
, value(datum + 1 day, '11.11.1111') datum
from m
where not exists (select 1 from oa1p.tqz006GbGrTsStats s
where s.rz = '$rz' and s.dbSys = '$dbSys'
and loadTs >= timestamp('22.07.2014')
and s.dbName = m.db and s.name = m.ts
and s.partition = m.partition
) )
ENDEXEC
LOAD DATA LOG NO
SORTKEYS
SORTDEVT DISK
WORKDDN(TSYUTS,TSOUTS)
MAPDDN TMAPD ERRDDN TERRD
STATISTICS INDEX(ALL) REPORT NO UPDATE ALL
INTO TABLE OA1P.TQZ006TMP PART 3
RESUME NO REPLACE COPYDDN(TCOPYD)
INCURSOR CUR2
EXEC SQL
DECLARE CUR3 CURSOR FOR -- find last entry per week ending friday
select 4 p, db, ts, parts, partition
, dsSize, large, hiUse
, datum
from oa1p.tqz006tmp w
where p=3 and datum <= '20.07.2012'
and not exists (select 1 from oa1p.tqz006tmp n
where n.p = 3 and w.db = n.db and w.ts = n.ts
and w.partition = n.partition
and w.datum < n.datum
and n.datum <= date(trunc_timestamp(
timestamp(w.datum) + 9 days, 'iw')
-3 days) -- next friday
)
ENDEXEC
LOAD DATA LOG NO
SORTKEYS
SORTDEVT DISK
WORKDDN(TSYUTS,TSOUTS)
MAPDDN TMAPD ERRDDN TERRD
STATISTICS INDEX(ALL) REPORT NO UPDATE ALL
INTO TABLE OA1P.TQZ006TMP PART 4
RESUME NO REPLACE COPYDDN(TCOPYD)
INCURSOR CUR3
EXEC SQL
delete
from OA1P.TQZ006GBGRTSSTATS
where rz = '$rz' and dbSys = '$dbSys'
and loadTS < timestamp('22.07.2014')
ENDEXEC
EXEC SQL
DECLARE CUR4 CURSOR FOR -- load into tsStats
with u as
(
select *
from oa1p.tqz006tmp
where p = 3 and datum > '20.07.2012'
union all select *
from oa1p.tqz006tmp
where p = 4 and datum <= '20.07.2012' and datum >= '01.07.2009'
union all select *
from oa1p.tqz006tmp m
where p = 4 and datum < '01.07.2009'
and not exists (select 1 from oa1p.tqz006tmp n
where p = 4 and m.db = n.db and m.ts = n.ts
and m.partition = n.partition
and n.datum > m.datum
and n.datum < date(trunc_timestamp(
timestamp(m.datum + 1 month)
, 'mon'))
)
)
select case when large = '-' then 'd' else 'a' end state
, timestamp(datum) loadTS
, '$rz' rz
, '$dbSys' dbSys
, db dbName
, ts name
, partition
, 1 instance
, large tsType
, ' ' tsTy
, 1 pgSize
, 0 segSize
, parts
, 0 maxParts
, dsSize
, 0 dsGb
, 0 limGb
, 0 limPart
, 0 obid
, '?' clone
, 1 tsInst
, '' tbCr
, '' tb
, '' tbTy
, 0 tbObId
, 0 dbid
, 0 psid
, ' ' ibmReqD
, timestamp(datum) updateStatsTime
, int(case when large <> '-' then hiUse else null end)
nActive
from u
ENDEXEC
LOAD DATA LOG NO RESUME YES
SORTKEYS
SORTDEVT DISK
WORKDDN(TSYUTS,TSOUTS)
MAPDDN TMAPD ERRDDN TERRD
INCURSOR CUR4
INTO TABLE OA1P.TQZ006GBGRTSSTATS
LISTDEF C#LIST 00010000
INCLUDE TABLESPACE QZ01A1P.A006A PARTLEVEL 8620
COPY LIST C#LIST
SCOPE PENDING
FULL YES
COPYDDN TCOPYD
PARALLEL
SHRLEVEL CHANGE
EXEC SQL
delete
from OA1P.TQZ006GBGRTSSTATS a
where rz = '$rz' and dbSys = '$dbSys'
and loadTS < timestamp('20.07.2012')
and a.nActive = (select nActive FROM OA1P.TQZ006GBGRTSSTATS o
where a.rz = o.rz and a.dbSys = o.dbSys
and a.dbName = o.dbName and a.name = o.name
and a.instance = o.instance
and a.partition = o.partition and a.loadTs > o.loadTs
order by o.loadTs desc
fetch first 1 row only
)
ENDEXEC
//*
$/load/