zOs/SQL/EXPLAHAR
set current sqlId = 'A540769';
select char(left(v.version, 10), 10), v.* from cmnbatch.plan_view5a v
where v.progName = 'YYWM05E'
and v.version in ('A18Q002827','WSIN000043','WSIN000056')
and v.bind_time in
('2008-09-11-13.36.40.058999',
'2008-02-07-15.41.51.027494')
order by v.applname, v.progname,
v.queryNo, v.version, v.qblockno, v.planno
with ur
;
;
x
select * from cmnbatch.plan_view5
where progName = 'YUU1022'
-- and stmt = '000982'
-- and version = 'WS8B000620'
-- and bind_time = '2008-05-19-08.34.31.808699'
order by applname, progname, timestamp ,
queryNo, qblockno, planno
;
sdf
xelect PROCMS, PROCSU, float(procSu) / float(procMs) * 1000
from cmnbatch.dsn_statemnt_table
-- where progname = 'MF7270' and queryNo = 149
where group_member = 'DOF1' and procMS > 1000
fetch first 100 rows only
;
x
delete from A540769.plan_table
;
explain plan for -- set queryno = 149 for
SELECT count(*)
FROM oa1p.TWM005A1
WHERE D$GUEA <= ? AND D$GUEB >= ?
AND CSTAT BETWEEN ? AND ? AND CDEFAULT BETWEEN ? AND ?
AND CKAT = ? AND NVALOR = ? QUERYNO 149
for fetch only
;
select * from plan_view5
where queryno in ( 149, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback
;
x
select * from cmnbatch.plan_view5
where progName = 'YYWM51E'
and stmt = '000149'
-- and version = 'WS8B000620'
-- and bind_time = '2008-05-19-08.34.31.808699'
order by applname, progname, timestamp ,
queryNo, qblockno, planno
;
------- dangelmeier
xelect * from cmnbatch.plan_view5a
where progName = 'YDWGBG'-- and timestamp > '200802'
and stmt = '000754'
and version = 'DEPO0000535AA7DD34MVS'
order by applname, progname, timestamp ,
queryNo, qblockno, planno
;
xelect *
from sysibm.syscoldist
where tbname like 'TWU11%'
-- and name = 'WU111003'
;
xelect a.* , b.*
from
(select case when 1=0 then 1 else null end k, 'a' t
from sysibm.sysdummy1 ) a
join
(select case when 1=0 then 1 else null end k, 'b' t
from sysibm.sysdummy1 ) b
on a.k = b.k --or (a.k is null and b.k is null)
;
x
select count(*), wu110021
from oa1p.twu110a1
group by wu110021
order by 1 desc
with ur
;
xelect count(*), wu111003
from oa1p.twu111a1
group by wu111003
order by 1 desc
with ur
;
x
set current sqlId = 'A540769';
delete from A540769.plan_table
;
explain plan set queryno = 111 for
select T111.auftrnr, -- /* Alle Daten */
t111.rectyp,
t111.ahnr,
t111.shlnr,
t111.shbu,
t111.uuid,
t111.bestauftr,
t111.briefnr,
t111.briefvar,
t111.antnr,
t111.antvar,
t111.antterm,
t111.sprache,
t111.verarbstat,
t111.ausgrund,
t110.paketid
from oa1p.VWU110A1V T110, -- /* Bestandestable */
oa1p.VWU111A1V T111 -- /* Auftragstable */
where t110.auftrnr = t111.auftrnr -- /* Auftragsnummer */
and t111.ahnr = 'abcdefgh' -- /* Aktionsheader */
and t110.paketid = 'abcedef' -- Paket-Nummer */
and t111.verarbstat = 123 -- /* Status */
group by T111.auftrnr, -- je Gruppe einmal
t111.rectyp,
t111.ahnr,
t111.shlnr,
t111.shbu,
t111.uuid,
t111.bestauftr,
t111.briefnr,
t111.briefvar,
t111.antnr,
t111.antvar,
t111.antterm,
t111.sprache,
t111.verarbstat,
t111.ausgrund,
t110.paketid
order by t111.ahnr, -- Sortierreihenfolge
t111.shlnr,
t111.shbu,
t111.auftrnr
for fetch only
;
select * from plan_view5
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback
;
xelect queryNo, qBlockno, planno, timestamp, bind_time, version
from cmnbatch.plan_table
where progName = 'YDWGBG'-- and timestamp > '200802'
-- and stmt = '000754'
order by applname, progname, version , timestamp ,
queryNo, qblockno, planno
;
select * from cmnbatch.plan_view5
where progName = 'YDWGBG'-- and timestamp > '200802'
-- and stmt = '000754'
order by applname, progname, timestamp ,
queryNo, qblockno, planno
;
x -- Härdi 16.5.08
select * from cmnbatch.plan_view5
where progName = 'YYWM51E'-- and timestamp > '200802'
and stmt = '000153'
order by applname, progname, timestamp ,
queryNo, qblockno, planno
;
x
select ckat, count(*) from oa1p.tWM005A1
group by ckat
order by 2 desc
;
set current sqlId = 'A540769';
delete from A540769.plan_table
;
explain plan set queryno = 111 for
select
c.dbname, c.tsName, c.dsNum, s.pgSize,
icType, copyPagesf, dsName, timestamp,
case when timestamp > current timestamp - 24 hours then 'h'
else 'g' end gest
from sysibm.syscopy c, sysibm.systablespace s
where icType in ('F', 'I') and c.dbName like 'M%'
and s.dbName = c.dbName and s.name = c.tsName
;
explain plan set queryno = 222 for
select
c.dbname, c.tsName, c.dsNum, s.pgSize,
icType, copyPagesf, dsName, timestamp,
case when timestamp > current timestamp - 24 hours then 'h'
else 'g' end gest
from sysibm.syscopy c, sysibm.systablespace s
where icType in ('F', 'I') and c.dbName like 'M%'
and s.dbName = c.dbName and s.name = c.tsName
order by 1, 2,3, timestamp desc
;
select * from plan_view5a
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
select count(*), jobname, max(partition), min(partition)
from sysibm.syscopy c, sysibm.systablepart p
where c.dbName = p.dbName and c.tsName = p.tsname
and c.dsNum in (0, p.partition)
group by jobName
order by 1 desc
;
select * from plan_view5a
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
xelect *
from CMNBATCH.plan_view5
where progName = 'DBWK3'
order by applname, progname, queryNo, qblockno, planno
;
xelect count(*), auftrags_nummer, pm_id
from oa1t.vpw210a1V
group by auftrags_nummer, pm_id
order by 1 desc
;
--select count(*) from oa1t.TSN100A1
-- with ur
-- ;
select *
from CMNBATCH.plan_view5
where progName = 'YPW2KPI' and queryNo = 4235
order by applname, progname, queryNo, qblockno, planno
;
x
set current sqlid = 'A540769'
;
delete from plan_table where queryno in (111, 222)
;
select current timestamp from sysibm.sysdummy1
;
explain plan set queryno = 111 for
SELECT T.* FROM oa1t.VNI600A101A C
, oa1t.VNI203A101A T
WHERE T.PERENDDT >= DATE(C.CTRLTIMESTMPFROM)
AND C.CTRLID = 'NI6220R'
;
select current timestamp from sysibm.sysdummy1
;
select * from plan_view5a
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
explain plan set queryno = 222 for
WITH NI600 (PERENDDT) AS
(SELECT DATE(C.CTRLTIMESTMPFROM) AS ENDDT
FROM oa1t.VNI600A101A C
WHERE C.CTRLID = 'NI6220R'
)
SELECT T.* FROM NI600
,oa1t.VNI203A101A T
WHERE T.PERENDDT >= NI600.PERENDDT
;
select * from plan_view5a
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
select * from plan_view5
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback
;
x
select *
from cmnbatch.plan_view5
where progName = 'YRPNIMP' and queryNo = 866
order by applname, progname, queryNo, qblockno, planno
;
x
d.bType, p.collid, p.Name, p.version,
p.conToken, p.timeStamp, p.type,
p.validate, p.isolation, p.valid, p.operative,
p.owner, p.qualifier
m sysibm.syspackdep d join sysibm.syspackage p
on p.location = d.dLocation and p.collid = d.dCollid
and p.name = d.dName and p.conToken = d.dConToken 2.22.129867'
re
(bQualifier, bName, bType) in
( select dbName, name, 'R'
from sysIbm.sysTablespace
where dbName in ('NF01A1A', 'NF02A1A', 'NF03A1A')
)
from gdb0283.vnz240a1v
where SearchValue01 = ?
-- dec(SearchValue01,15,3) = ?
and CreateDateTime < ?
and dec(SearchValue02,15,3) >= ?
and dec(SearchValue02,15,3) <= ?
and Objtype ^= 'PORDSPLT'
order by CreateDateTime
fetch first 101 rows only
optimize for 101 rows
with ur
;
select * from plan_view5A
where queryno = 999
order by applname, progname, queryNo, qblockno, planno
;
rollback
;
select * from plan_table
order by applname, progname, queryNo, qblockno, planno
;
rollback
;