zOs/SQL/GBGRFUN2
-- versuch mit einer Table SQL Funktion
-- die richtigen Zeitreihen schlüssel zu selektieren
-- gescheitert, aus Performance Gründen:
-- db2 generiert unsinnig Casts,
-- die aus matching predicates stage2 predicates machen
--
set current sqlid = 'S100447';
drop function oa1p.fQz006Day(aRz char(3), aDbSys char(4)
, ts char(8), aTs char(8)
, aNew date, aOld date, aStep smallint);
create function oa1p.fQz006Day(aRz char(3), aDbSys char(4)
, aDb char(8), aTs char(8)
, aNew date, aOld date, aStep smallint)
returns table (gDay date, rz char(3), dbSys char(4)
, db char(8), ts char(8), inst smallInt, part smallInt
, loadTs timestamp)
parameter ccsid unicode
return
with n (g, l, rz, dbSys, db, ts, inst, part, loadTs) as
(
select aNew, 0
, rz, dbSys, dbName, name, instance, partition
, max(loadTs)
from oa1p.tqz006GbGrTsSTats
where rz = aRz and dbSys like aDbSys and dbName like aDb
and name like aTs
and loadTs < timestamp(aNew + 1 day)
group by rz, dbSys, dbName, name, instance, partition
union all select g - aStep days, l+1
, rz, dbSys, db, ts, inst, part
, (select max(o.loadTs)
from oa1p.tqz006GbGrTsSTats o
where n.rz = o.rz
and n.dbSys = o.dbSys
and n.db = o.dbName
and n.ts = o.name
and n.inst = o.instance
and n.part = o.partition
and timestamp(n.g - (aStep-1) days, '00.00.00')
> o.loadTS
)
from n where l < 3000 and g - aStep days >= aOld
)
select g, rz, dbSys, db, ts, inst, part, loadTs from n
;
commit
;
select *
from table(oa1p.fQz006Day('RZ4', 'DP4G', 'QZ01A1%', 'A006%'
, current date, current date - 12 days, 3)) a
order by db, ts, inst, part, gDay desc
;
;x;
set current path oa1p;
select dbName, name, instance, partition
, fosFmte7(real(nActive) * pgSize * 1024) used
, fosFmte7(totalRows) rows
, loadTs, updateStatsTime, r.*
from oa1p.tqz006GbGrTsSTats r
where rz = 'RZ2' and dbSys = 'DP2G' and dbName = 'DB2ADMIN'
and name = 'A013A'
order by dbName, name, instance, partition, loadTs desc
;x;
select loadTs, dbSys, count(*)
, sum(case when state = 'a' then 1 else 0 end) act
, sum(case when state = 'd' then 1 else 0 end) del
from oa1p.tqz006GbGrTsSTats r
where rz = 'RZ2' -- and dbSys = 'DVBP'
group by loadTs, dbSys
order by 1 desc, dbSys
;x;
select *
from oa1p.tqz006GbGrTsSTats r
where rz = 'RZ2' and dbSys = 'DVBP'
and dbName = 'XB058287'
and loadTs < '2014-07-01-00.00.00'
order by loadTs desc, name, partition
;x;
select dbName, count(*)
from oa1p.tqz006GbGrTsSTats r
where rz = 'RZ2' and dbSys = 'DVBP'
and loadTs < '2014-07-01-00.00.00'
group by dbName
;x;
delete from oa1p.tqz006GbGrTsSTats d
where rz = 'RZ2' and dbSys = 'DP2G'
and loadTS >= '2014-07-22-00.00.00' and state = 'd'
and (select loadTs
from oa1p.tqz006GbGrTsSTats b
where d.rz = b.rz and d.dbSys = b.dbSys
and d.dbName = b.dbName
and d.name = b.name
and d.instance = b.instance
and d.partition = b.partition
and d.loadTS > b.loadTs
order by b.loadTS desc
fetch first 1 rows only
) < '2014-07-22-00.00.00'
;
commit
;
select dbName, name, instance, partition, loadTs
, (select max(char(loadTs) || state)
from oa1p.tqz006GbGrTsSTats b
where d.rz = b.rz and d.dbSys = b.dbSys
and d.dbName = b.dbName
and d.name = b.name
and d.instance = b.instance
and d.partition = b.partition
and d.loadTS > b.loadTs
)
from oa1p.tqz006GbGrTsSTats d
where rz = 'RZ2' and dbSys = 'DP2G'
and state = 'd' and loadTs > '2014-07-01-00.00.00'
order by 6 desc
-- order by dbName, name, instance, partition
;x;
select loadTs, count(*)
, sum(case when state = 'a' then 1 else 0 end) act
, sum(case when state = 'd' then 1 else 0 end) del
from oa1p.tqz006GbGrTsSTats r
where rz = 'RZ2' and dbSys = 'DP2G'
group by loadTs
order by 1 desc
;x;
;x;
select trunc_timestamp(loadTs, 'iw'), count(*)
, sum(case when state = 'a' then 1 else 0 end) act
, sum(case when state = 'd' then 1 else 0 end) del
from oa1p.tqz006GbGrTsSTats r
where rz = 'RZ4' and dbSys = 'DBOL'
group by trunc_timestamp(loadTs, 'iw')
order by 1 desc
;x;
;
;x;
set current path oa1p;
select trunc_timestamp(loadTs, 'iw'), count(*)
, sum(case when state = 'a' then 1 else 0 end) act
, sum(case when state = 'd' then 1 else 0 end) del
from oa1p.tqz006GbGrTsSTats r
where rz = 'RZ4' and dbSys = 'DBOL'
group by trunc_timestamp(loadTs, 'iw')
order by 1 desc
;x;
;x;
set current path oa1p;
select trunc_timestamp(loadTs, 'iw'), count(*)
, sum(case when state = 'a' then 1 else 0 end) act
, sum(case when state = 'd' then 1 else 0 end) del
from oa1p.tqz006GbGrTsSTats r
where rz = 'RZ4' -- and dbSys = 'DP4G'
group by trunc_timestamp(loadTs, 'iw')
order by 1 desc
;x;
select *
from oa1p.tqz006tmp
where p = 3 and db = 'MF01A1P'
and ts = 'A150A'
order by db, ts, partition, datum desc
;x;
set current path oa1p;
with n (g, l, rz, dbSys, db, ts, inst, part, loadTs) as
(
select current date, 0
, rz, dbSys, dbName, name, instance, partition
, max(loadTs)
from oa1p.tqz006GbGrTsSTats
where rz = 'RZ4' and dbSys = 'DP4G' and dbName = 'DB2ADMIN'
and name = 'A009A'
-- and partition <> 1
group by rz, dbSys, dbName, name, instance, partition
union all select g - 1 day, l+1
, rz, dbSys, db, ts, inst, part
, (select max(loadTs)
from oa1p.tqz006GbGrTsSTats o
where n.rz = o.rz
and n.dbSys = o.dbSys
and n.db = o.dbName
and n.ts = o.name
and n.inst = o.instance
and n.part = o.partition
and timestamp(n.g, '00.00.00') > o.loadTS
)
from n where l < 3000
)
select n.g, n.db, n.ts, n.inst, n.part
, fosFmte7(real(j.nActive) * j.pgSize * 1024) used
, fosFmte7(totalRows) rows
, n.loadTs, j.updateStatsTime
, j.*
from n
left join oa1p.tqz006GbGrTsSTats j
on j.rz = n.rz and j.dbSys = n.dbSys
and j.dbName = n.db and j.Name = n.ts
and j.partition = n.part
and j.instance = n.inst
and j.loadTs = n.loadTs
where n.loadTs is not null and date(n.loadTs) = n.g
order by n.rz, n.dbSys, n.db, n.ts,
n.inst, n.part, n.g desc
;x;
select * from sysibm.sysTablespace where clone = 'Y'
;x;
select p, large, count(*)
from oa1p.tqz006tmp
group by p, large
;x;
select *
from oa1p.tqz006tmp
where db = 'DB2ADMIN'
and ts = 'APRSRC'
and p = 3
order by db, ts, partition, datum desc
;x;
select n.g, n.rz, n.dbSys, n.db, n.ts
, fosFmte7(sum(real(j.nActive) * j.pgSize) * 1024) used
-- , n.*, j.*
from n
left join oa1p.tqz006GbGrTsSTats j
on j.rz = n.rz and j.dbSys = n.dbSys
and j.dbName = n.db and j.Name = n.ts
and j.partition = n.part
and j.instance = n.inst
and j.loadTs = n.loadTs
-- where n.loadTs is not null
group by n.g, n.rz, n.dbSys, n.db, n.ts
order by n.rz, n.dbSys, n.db, n.ts, n.g desc
;x;
select * from plan_view1
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2det
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select *
from plan_viewPred
order by collid, progName, explain_time,
queryNo, qBlockNo, predNo, orderNo, mixOpSeqNo
with ur
;
rollback
;;;;
;x;
select *
from oa1p.tqz006GbGrTsSTats
order by rz, dbSys, dbName, name, instance, partition, loadts desc