zOs/SQL/EXPLAIW5
WITH V5A AS
(
SELECT
SUBSTR(DIGITS(A.QUERYNO),5) AS STMT,
QBLOCK_TYPE AS TYPE,
CASE
WHEN METHOD = 3 THEN ' '
ELSE CHAR(S.PROCSU)
END AS MSU,
CASE
WHEN PRIMARY_ACCESSTYPE = 'D' THEN 'DIR.ROW'
WHEN ACCESSTYPE = 'I ' THEN 'IX-SCAN'
WHEN ACCESSTYPE = 'I1' THEN 'IX-ONE '
WHEN ACCESSTYPE = 'R ' THEN 'TS-SCAN'
WHEN ACCESSTYPE = 'N ' THEN 'IX-ACC '
WHEN ACCESSTYPE = 'M ' THEN 'IX-SC.M'
WHEN ACCESSTYPE = 'MX' THEN 'IX-SC.X'
WHEN ACCESSTYPE = 'MI' THEN 'IX-SC.I'
WHEN ACCESSTYPE = 'MU' THEN 'IX-SC.U'
WHEN ACCESSTYPE = 'T' THEN 'IX-SPRS'
WHEN ACCESSTYPE = ' ' THEN ' '
ELSE NULL
END AS ACCESS,
CASE WHEN ACCESSTYPE = 'R ' THEN ' '
WHEN PRIMARY_ACCESSTYPE = 'D'
THEN ' '
ELSE SUBSTR(ACCESSNAME,1,12)
END AS INDEX,
SUBSTR(TNAME,1,12) AS TABLE,
CASE
WHEN TABLE_TYPE IS NULL THEN ' '
ELSE TABLE_TYPE
END
AS TTYP,
CASE
WHEN METHOD = 3 THEN ' '
WHEN ACCESSTYPE = 'R ' THEN ' '
ELSE
SUBSTR(DIGITS(MATCHCOLS),5,1) CONCAT ' ' CONCAT INDEXONLY
END
AS MC_O,
CASE METHOD
WHEN 0 THEN ' '
WHEN 1 THEN 'NLJOIN'
WHEN 2 THEN 'SMJOIN'
WHEN 3 THEN 'SORT '
WHEN 4 THEN 'HYJOIN'
ELSE NULL END
AS METHOD,
CASE JOIN_TYPE
WHEN 'F' THEN 'FULL '
WHEN 'L' THEN 'LEFT '
WHEN 'S' THEN 'STAR '
ELSE ' ' END
AS JOIN,
SORTN_UNIQ CONCAT SORTN_JOIN CONCAT
SORTN_ORDERBY CONCAT SORTN_GROUPBY
AS UJOG,
SORTC_UNIQ CONCAT SORTC_JOIN CONCAT
SORTC_ORDERBY CONCAT SORTC_GROUPBY
AS UJOC,
TSLOCKMODE AS LCK,
SUBSTR(CORRELATION_NAME,1,4)
AS CORR,
CASE PARALLELISM_MODE
WHEN 'C' THEN 'CPU'
WHEN 'I' THEN 'I-O'
WHEN 'X' THEN 'SYSPLEX'
ELSE NULL END
AS PARAL,
STRIP(DIGITS(ACCESS_DEGREE),LEADING,'0')
CONCAT ' ' CONCAT STRIP(DIGITS(JOIN_DEGREE),LEADING,'0')
AS AJ_DEG,
STRIP(DIGITS(ACCESS_PGROUP_ID),LEADING,'0')
CONCAT ' ' CONCAT
STRIP(DIGITS(JOIN_PGROUP_ID),LEADING,'0')
AS PG_DEG,
STRIP(DIGITS(MERGE_JOIN_COLS),LEADING,'0')
AS MC,
PREFETCH AS PRE,
STRIP(DIGITS(A.QUERYNO),LEADING,'0')
AS QNO,
SUBSTR(DIGITS(QBLOCKNO),4,2) CONCAT ' '
CONCAT SUBSTR(DIGITS(PLANNO),4,2)
AS BL_PL,
A.PARENT_QBLOCKNO,
A.QBLOCKNO,
A.PLANNO,
A.TNAME,
A.ACCESSNAME,
A.OPTHINT,
A.HINT_USED,
A.APPLNAME,
A.COLLID,
A.PROGNAME,
A.VERSION,
A.TIMESTAMP,
A.QUERYNO ,
A.MIXOPSEQ,
A.BIND_TIME,
S.COST_CATEGORY,
S.PROCMS,
S.PROCSU,
S.REASON
FROM CMNBATCH.PLAN_TABLE A
LEFT JOIN CMNBATCH.DSN_STATEMNT_TABLE S
ON -- S.COLLID = A.COLLID
S.APPLNAME = A.APPLNAME
AND S.PROGNAME = A.PROGNAME
AND S.QUERYNO = A.QUERYNO
AND S.EXPLAIN_TIME = A.BIND_TIME
) ----------------------------------------------
SELECT * FROM V5A
where progName = 'YNZHIST'
-- and stmt = '000982'
AND VERSION = 'ZVHI0000395ACB65FEMVS'
-- AND VERSION = 'ZVHI0000395B3A344DMVS'
-- and bind_time = '2008-05-19-08.34.31.808699'
order by applname, progname, timestamp ,
queryNo, qblockno, planno
;
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
;