zOs/SQL/TEL350
select current timestamp from sysibm.sysDummy1
;
delete
from oa1A.tel350a1
where EL350001 = '0550021'
;
select current timestamp from sysibm.sysDummy1
;
rollback
;
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 3 for
delete
from oa1A.tel350a1
where EL350001 = '0550021'
;
select * from plan_view5a
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
select current timestamp from sysibm.sysDummy1
;
rollback
;
select * from cmnbatch.plan_view5
where progName = 'YDG130' and queryno in ( 2453, 2454)
order by applname, progname, queryNo, qblockno, planno
;
x
SELECT WI10102A,WI10102S,WI10112
FROM GDB0112.TWI101A1
WHERE WI10101T = ?
AND WI10101D = ?
AND WI10101L = ?
AND WI10101G IN (? ,'')
AND WI10101V IN (? ,'')
AND WI10101B IN (? ,'')
AND WI10108 = ?
AND WI10101I <= ?
ORDER BY WI10112 DESC,WI10101I DESC
FETCH FIRST ROW ONLY
WITH UR;
explain plan set queryno = 7 for
SELECT WI10102A,WI10102S,WI10112
FROM OA1A.TWI101A1
WHERE WI10101T = ?
AND WI10101D = ?
AND WI10101L = ?
AND WI10101G IN (? ,'')
AND WI10101V IN (? ,'')
AND WI10101B IN (? ,'')
AND WI10108 = ?
AND WI10101I <= ?
ORDER BY WI10112 DESC,WI10101I DESC
FETCH FIRST ROW ONLY
WITH UR;
select * from plan_view5a
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
xxx
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 3 for
SELECT SYMBOLCH,SYMBOLCSFI,SYMBOLISIN,IDSYMBOL_RATING,SCHEME_RATING,
raTINGDATE,RATINGSTATUSTYPE,WATCHLISTSTATTYPE
FROM oa1t.VVV_XTREQSCANKEY INNER
JOIN oa1t.VVV_IDS_STD814
ON oa1t.VVV_IDS_STD814.INSTRUMENTID
= oa1t.VVV_XTREQSCANKEY.TEMP_DBKEY
-- WHERE oa1t.VVV_XTREQSCANKEY.PARTITION_NUMBER = 8
WITH UR
fOR FETCH ONLY
;
select * from plan_view5a
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 3 for
select *
from a540769.vrtsReoTs
where dbName like 'MF%'
;
select * from plan_view5a
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 3 for
-- find the startpoint for a recovery or logDiscontinuity
with recSta as
( select c.*
from sysibm.syscopy c
where c.timestamp =
( select max(a.timestamp)
from sysibm.syscopy a
where c.dbName = a.dbName and c.tsName = a.tsName
and c.dsNum = a.dsNum
and icType in ('F', 'R', 'S', 'W', 'Y')
)
)
select * from recSta
where dbName like 'D%' and icType <> 'F'
order by dbName, tsName, dsNum
;
-- find the startpoint for a recovery or logDiscontinuity
explain plan set queryno = 9 for
with recSta as
( select p.dbName db, p.tsName ts, p.partition pa, q.*
from sysibm.systablepart p
left join
( select c.*
from sysibm.syscopy c
where c.timestamp =
( select max(a.timestamp)
from sysibm.syscopy a
where c.dbName = a.dbName and c.tsName = a.tsName
and c.dsNum = a.dsNum
and icType in ('F', 'R', 'S', 'W', 'Y')
)
) q
on q.dbName = p.dbName and q.tsName = p.tsName
and p.partition = q.dsNum
)
select * from recSta
where dbName like 'D%' and icType <> 'F'
order by dbName, tsName, dsNum
;
select * from plan_view5a
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
----- query 1532 YXEBV2E, 30.6.09
----- Kumar Sandeep will sequenz TKS835, TKS833, TKS843, TKS843
----- seit einem Jahr bind TKS843, TKS835, TKS833, TKS843
-----
----- 3: partner_*=? in 833 und join : original
explain plan set queryno = 3 for
SELECT 1
FROM oA1P.VKS833A1V V833, oA1P.VKS835A1V V835, oA1P.VKS843A1V V843S,
oA1P.VKS843A1V V843O
WHERE V833.PARTNER_KEY = ? AND V833.CONTRACT_COLL = ?
AND V833.PARTNER_TYPE = 3 AND V833.PARTNER_KEY = V835.PARTNER_KEY AND
V833.CONTRACT_KEY = V835.CONTRACT_KEY AND V833.PARTNER_TYPE =
V835.PARTNER_TYPE AND V835.ARRANGEMENT_TYPE = ? AND
V835.ARRANGEMENT_ID = ? AND V835.SERVICE_KEY = V843S.TAB_CODE AND
V843S.TAB_ART = 'SERVICE_ID' AND V843O.TAB_ART = 'OTHER_CONDITION' AND
V843O.TAB_CODE = V843S.TAB_TEXT3 AND V843O.BU = ? AND CURRENT DATE
BETWEEN V835.VALID_FROM_DATE AND V835.VALID_TO_DATE FETCH FIRST ROW ONLY
;
----- 5: partner_*=? in 835 und join
explain plan set queryno = 5 for
SELECT 1
FROM oA1P.VKS833A1V V833, oA1P.VKS835A1V V835, oA1P.VKS843A1V V843S,
oA1P.VKS843A1V V843O
WHERE V835.PARTNER_KEY = ? AND V833.CONTRACT_COLL = ?
AND V835.PARTNER_TYPE = 3 AND V833.PARTNER_KEY = V835.PARTNER_KEY AND
V833.CONTRACT_KEY = V835.CONTRACT_KEY AND V833.PARTNER_TYPE =
V835.PARTNER_TYPE AND V835.ARRANGEMENT_TYPE = ? AND
V835.ARRANGEMENT_ID = ? AND V835.SERVICE_KEY = V843S.TAB_CODE AND
V843S.TAB_ART = 'SERVICE_ID' AND V843O.TAB_ART = 'OTHER_CONDITION' AND
V843O.TAB_CODE = V843S.TAB_TEXT3 AND V843O.BU = ? AND CURRENT DATE
BETWEEN V835.VALID_FROM_DATE AND V835.VALID_TO_DATE FETCH FIRST ROW ONLY
;
----- 35: partner_*=? in 833 und 835 und join: fake filtering
explain plan set queryno = 35 for
SELECT 1
FROM oA1P.VKS833A1V V833, oA1P.VKS835A1V V835, oA1P.VKS843A1V V843S,
oA1P.VKS843A1V V843O
WHERE V833.PARTNER_KEY = ? AND V833.CONTRACT_COLL = ?
AND V833.PARTNER_TYPE = 3
and V835.PARTNER_KEY = ? AND V835.PARTNER_TYPE = 3
AND V833.PARTNER_KEY = V835.PARTNER_KEY AND
V833.CONTRACT_KEY = V835.CONTRACT_KEY AND V833.PARTNER_TYPE =
V835.PARTNER_TYPE AND V835.ARRANGEMENT_TYPE = ? AND
V835.ARRANGEMENT_ID = ? AND V835.SERVICE_KEY = V843S.TAB_CODE AND
V843S.TAB_ART = 'SERVICE_ID' AND V843O.TAB_ART = 'OTHER_CONDITION' AND
V843O.TAB_CODE = V843S.TAB_TEXT3 AND V843O.BU = ? AND CURRENT DATE
BETWEEN V835.VALID_FROM_DATE AND V835.VALID_TO_DATE FETCH FIRST ROW ONLY
;
----- 359: partner_*=? in 833 und 835 NO join
explain plan set queryno = 359 for
SELECT 1
FROM oA1P.VKS833A1V V833, oA1P.VKS835A1V V835, oA1P.VKS843A1V V843S,
oA1P.VKS843A1V V843O
WHERE V833.PARTNER_KEY = ? AND V833.CONTRACT_COLL = ?
AND V833.PARTNER_TYPE = 3
and V835.PARTNER_KEY = ? AND V835.PARTNER_TYPE = 3
and V833.CONTRACT_KEY = V835.CONTRACT_KEY
AND V835.ARRANGEMENT_TYPE = ? AND
V835.ARRANGEMENT_ID = ? AND V835.SERVICE_KEY = V843S.TAB_CODE AND
V843S.TAB_ART = 'SERVICE_ID' AND V843O.TAB_ART = 'OTHER_CONDITION' AND
V843O.TAB_CODE = V843S.TAB_TEXT3 AND V843O.BU = ? AND CURRENT DATE
BETWEEN V835.VALID_FROM_DATE AND V835.VALID_TO_DATE FETCH FIRST ROW ONLY
;
select * from plan_view5a
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
select * from CMNbatch.plan_view5
where queryno between 142 and 147
and progname = 'YCI017C'
order by applname, progname, timestamp, queryNo, qblockno, planno
;
x
explain plan set queryno = 111 for
select * from cmnbatch.plan_view5
-- where progname in ('XBIM33')
where progname in ('XBIM31', 'XBIM32', 'XBIM33', 'XBIM34' ,
'XBIM35', 'XBIM42')
order by applname, progname, queryNo, qblockno, planno
;
explain plan set queryno = 222 for
select * from cmnbatch.plan_view5ss
-- where progname in ('XBIM33')
where progname in ('XBIM31', 'XBIM32', 'XBIM33', 'XBIM34' ,
'XBIM35', 'XBIM42')
order by applname, progname, queryNo, qblockno, planno
;
select * from plan_view5a
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
set current sqlid = 'A540769';
explain plan set queryno = 111 for
UPDATE bua.TXBH111
SET ESSTATE = 3 WHERE EAUUIDMQ IN (SELECT
EAUUIDMQ FROM bua.TXBH111 WHERE EAUUIDHT = '?' )
;
select * from plan_view5a
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
set current sqlId = 'A540769';
delete from A540769.plan_table
;
explain plan set queryno = 111 for
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
;