zOs/SQL/CATCOPY
select *
from sysibm.sysCopy c
where icType in ('F', 'I')
-- and dsName like 'CH.S00%'
and dsName like 'A470264%'
order by timestamp desc
with ur
;x;
select count(*), substr(dsName, 1, 5), min(dsName), max(dsName)
from sysibm.sysCopy c
where icType in ('F', 'I')
group by substr(dsName, 1, 5)
with ur
;x;
select count(*), date(timestamp)
from sysibm.sysCopy c
where dbName = 'DCMN050' and tsname = 'CMN$AAPR'
and ictype = 'A'
group by date(timestamp)
order by 2 desc
; x;
select timestamp, icType, instance
from sysibm.sysCopy c
where dbName = 'DCMN050' and tsname = 'CMN$AAPR'
and ictype = 'A'
order by 1 desc
; x;
select timestamp, icType, hex(start_rba), hex(pit_rba)
from sysibm.sysCopy c
where dbName = 'DA540769' and tsname = 'A924A'
order by 1
;
x
select left(c.jobName, 2), count(distinct c.jobName),
min(c.jobName), max(c.jobName),
count(*) cnt,
sum(c.copyPagesF * 1024 * s.pgSize) bytes
from sysibm.sysCopy c , sysibm.systableSpace s
where c.timestamp between '2008-06-27-19.00.00'
and '2008-06-30-06.00.00'
and s.dbName = c.dbName and s.name = c.tsName
and c.icType in ('F', 'I')
group by left(c.jobName, 2)
order by 6 desc
;
x
select date(timestamp),
count(*) cnt,
sum(c.copyPagesF * 1024 * s.pgSize) bytes
from sysibm.sysCopy c , sysibm.systableSpace s
where --c.timestamp between '2008-06-27-19.00.00'
-- and '2008-06-30-06.00.00'
s.dbName = c.dbName and s.name = c.tsName
and c.icType in ('F', 'I')
group by date(timestamp)
order by 1 asc
;
x
select c.jobName, count(*) cnt,
sum(c.copyPagesF * 1024 * s.pgSize) bytes
from sysibm.sysCopy c , sysibm.systableSpace s
where s.partitions > 0 and c.timestamp > current timestamp - 20 days
and s.dbName = c.dbName and s.name = c.tsName
and c.dsNum = 0
and c.icType in ('F', 'I')
group by c.jobName
order by 3 desc
;
having sum(case when c.dsNum = 0 then 1 else 0 end) >0
) x
select sum(cnt), sum(total) total, sum(totBy) totBy, sum(maxBy) maxBy,
sum(part), sum(paBy)
from (
-- where dbName like 'MF%'
;
xelect ictype, timestamp, hex(timestamp), dsName
from sysibm.sysCopy c
where dbName = 'XR01A1P' AND TSNAME = 'A200A'
AND DSNUM = 69
order by 2 desc
fetch first 100 rows only
;
select ictype, timestamp, hex(timestamp), dsName
from sysibm.sysCopy c
where dbName = 'XR01A1P' AND TSNAME = 'A200A'
AND DSNUM = 69
order by 2 desc
fetch first 100 rows only
;
Xselect dsName , copyPagesF * 1024 * pgSize
from sysibm.sysCopy c , sysibm.systableSpace s
where copyPagesF > 10 * 1024 * 1024
and s.dbName = c.dbName and s.name = c.tsName
order by 2 desc
fetch first 100 rows only
;
x
select current timestamp from sysibm.sysdummy1
;
select count(*) from
(
select dbName, tsName, dsNum, max(timestamp) new,
max(case when timestamp <= '2008-03-07-11.11.11'
then timestamp else null end) last
from sysibm.sysCopy
where icType = 'F' -- and dbName = 'MF01A1P'
group by dbName, tsName, dsNum
) x ,
sysibm.sysCopy c
where c.icType in ('F', 'I')
and c.dbName = x.dbName
and c.tsName = x.tsName
and c.dsNum = x.dsNum
and c.timestamp <= x.new
and c.timestamp >= x.last
with ur
;
select current timestamp from sysibm.sysdummy1
;
select count(*), icType, date(timestamp)
from sysibm.syscopy
group by icType, date(timestamp)
order by 2, 3
;
x
select da, icType,
sum(copied) copied, sum(coSize) coSize,
sum(coChgd) coChgd, sum(catSpc) catSpc,
sum(coCnt) coCntz, sum(paCnt) paCnt
from
( select date(timestamp) da, icType,
sum(copyPagesF * pgSize * 1024) copied,
sum(nPagesF * pgSize * 1024) coSize,
sum(cPagesF * pgSize * 1024) coChgd,
max(p.spacef * s.pgSize * 1024) catSpc,
count(*) coCnt,
1 paCnt
from sysibm.syscopy c, sysibm.systablespace s,
sysIbm.sysTablePart p
where icType in ('F', 'I') and c.dbName like 'MF01%'
and p.dbName = s.dbName and p.tsName = s.name
and c.dbName = p.dbName and c.tsName = p.tsName
and c.dsNum = p.partition
group by date(timestamp), icType,
s.dbName, s.name, p.partition
) pa
group by da, icType
order by 1 desc, 2
with ur
;
x
with tsDet as
( select s.dbname db, s.name ts,
sum(real(s.nActive) * s.pgSize* 1024) cAct,
sum(s.spaceF * 1024) cSpa,
sum(real(r.space) * 1024) rSpa,
sum(real(r.nActive) * s.pgSize * 1024) rAct
from sysibm.sysTablespace s,
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
left Join sysibm.tablespacestats r
on r.dbid = s.dbid and r.psid = s.psid
and r.dbName= s.dbName and r.name = s.name
and r.partition = s.partition
left join sysibm.sysCopy c
on c.dbName = s.dbName and c.tsName = s.name
and c.partition = p.partition
icType, sum(pgSize * copypagesf), sum(pgSize* nPagesF)
, sum(pgSize * cPagesF)
from sysibm.syscopy c, sysibm.systablespace s, m
where icType in ('F', 'I') -- and c.dbName = 'MF01A1P'
and s.dbName = c.dbName and s.name = c.tsName
and c.dbName = m.dbName and c.tsName = m. tsName
and c.dsNum = m.dsNum and c.timestamp >= m.maxTst
from sysibm.tablespacestats r, sysibm.sysTablespace s
where r.dbid = s.dbid and r.psid = s.psid
and r.dbName= s.dbName and r.name = s.name
group by s.dbid, s.psid, s.dbname, s.name
)
select * from tsDet where db like 'MF01%'
;
select current timestamp from sysibm.sysdummy1;
with a as
(
select current timestamp from sysibm.sysdummy1;
x
with m as
( select max(timestamp) maxTst, dbName, tsName, dsNum
from sysibm.syscopy
where icType = 'F'
group by dbName, tsName, dsNum
)
select
icType, sum(pgSize * copypagesf), sum(pgSize* nPagesF)
, sum(pgSize * cPagesF)
from sysibm.syscopy c, sysibm.systablespace s, m
where icType in ('F', 'I') -- and c.dbName = 'MF01A1P'
and s.dbName = c.dbName and s.name = c.tsName
and c.dbName = m.dbName and c.tsName = m. tsName
and c.dsNum = m.dsNum and c.timestamp >= m.maxTst
group by icType
with ur
;
select current timestamp from sysibm.sysdummy1;