zOs/SQL/PDBGENNJ
-- select * from tstRts.TrtsReoTSException;
set current sqlid = 'S100447';
set current schema = 'RZ2XX';
select current timestamp from sysibm.sysDummy1;
SELECT t.*, p.PLAN_NAME, substr(PCK_ID, 1, 10),
count(*) "count",
avg(class7_elapsed) "avgElapsed" ,
sum(class7_elapsed) "totElapsed" ,
min(class7_elapsed) "minElapsed" ,
max(class7_elapsed) "maxElapsed" ,
min(timestamp -class7_elapsed seconds) "minStart",
max(timestamp) "maxEnd"
from TACCT_Program p, A540769.TPDB t
where -- timestamp > timestamp(date(current_timestamp - 2 days)
-- , '00.00.00') and
( (PCK_COLLECTION_ID = 'NI'
and pck_id in ( 'NI6210',
'NI6260',
'NI6860',
'NI7600',
'NI9700',
'NI9740',
'YNI0131',
'YNI0183',
'YNI0350',
'YNI56SR',
'YNICAMU',
'YNICRSS',
'YNICTEU',
'YNINP03',
'YNIPACG',
'YNIPAMG',
'YNIPAMU',
'YNIPFLG',
'YNIPREG',
'YNIPTEG',
'YNIPTEU',
'YNIREST'))
or (PCK_COLLECTION_ID in ('NI01', 'NI02')
and pck_id in ( 'YNICL03',
'YNINP03',
'YNIPACG'))
or (PCK_COLLECTION_ID = 'OA1P'
and pck_id in ( 'NI660AAI')))
and p.timestamp + 0 seconds >= t.von
and p.timestamp - p.class7_elapsed seconds <= t.bis
group by t.PLAN, t.von, t.bis,
p.plan_name, p.pck_id
order by 1, 2, 3, 5, 6
with ur
;
select current timestamp from sysibm.sysDummy1;
;
;x;
insert into A540769.TPDB
select plan_name plan,
min(timestamp - class1_elapsed seconds) von,
(max(timestamp)) bis
from TACCT_general
where -- timestamp > timestamp(date(current_timestamp - 2 days)
-- , '00.00.00') and
plan_name in ('NI5340', 'NI5310')
group by date(timestamp), plan_name
;
select current timestamp from sysibm.sysDummy1;
commit;
select current timestamp from sysibm.sysDummy1;
;x;
drop TABLESPACE DA540769.TPDB;
commit;
CREATE TABLESPACE TPDB
IN DA540769
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 10 PCTFREE 12
GBPCACHE CHANGED
TRACKMOD YES
segsize 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255;
create table A540769.TPDB as
(
select plan_name plan,
min(timestamp - class1_elapsed seconds) von,
(max(timestamp)) bis
from TACCT_general
where -- timestamp > timestamp(date(current_timestamp - 2 days)
-- , '00.00.00') and
plan_name in ('NI5340', 'NI5310')
group by date(timestamp), plan_name
-- order by date(timestamp), plan_name
) with no data
in DA540769.Tpdb
;
CREATE UNIQUE INDEX A540769.IPDB1
ON A540769.TPDB
(von, bis, plan)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
BUFFERPOOL BP1
COPY NO
DEFER NO
DEFINE YES;
commit
;x;
set current path oa1p;
set current schema = RZ2XX;
set current sqlid = 'A540769';
delete from A540769.plan_table;
explain plan set queryno = 3 for
with t as
(
select plan_name,
min(timestamp - class1_elapsed seconds) von,
(max(timestamp)) bis
from TACCT_general
where -- timestamp > timestamp(date(current_timestamp - 2 days)
-- , '00.00.00') and
plan_name in ('NI5340', 'NI5310')
group by date(timestamp), plan_name
order by date(timestamp), plan_name
)
SELECT t.*, p.PLAN_NAME, substr(PCK_ID, 1, 10),
count(*) "count",
avg(class7_elapsed) "avgElapsed" ,
sum(class7_elapsed) "totElapsed" ,
min(class7_elapsed) "minElapsed" ,
max(class7_elapsed) "maxElapsed" ,
min(timestamp -class7_elapsed seconds) "minStart",
max(timestamp) "maxEnd"
from TACCT_Program p, t
where -- timestamp > timestamp(date(current_timestamp - 2 days)
-- , '00.00.00') and
( (PCK_COLLECTION_ID = 'NI'
and pck_id in ( 'NI6210',
'NI6260',
'NI6860',
'NI7600',
'NI9700',
'NI9740',
'YNI0131',
'YNI0183',
'YNI0350',
'YNI56SR',
'YNICAMU',
'YNICRSS',
'YNICTEU',
'YNINP03',
'YNIPACG',
'YNIPAMG',
'YNIPAMU',
'YNIPFLG',
'YNIPREG',
'YNIPTEG',
'YNIPTEU',
'YNIREST'))
or (PCK_COLLECTION_ID in ('NI01', 'NI02')
and pck_id in ( 'YNICL03',
'YNINP03',
'YNIPACG'))
or (PCK_COLLECTION_ID = 'OA1P'
and pck_id in ( 'NI660AAI')))
and p.timestamp >= t.von
and p.timestamp -class7_elapsed seconds <= t.bis
group by t.PLAN_NAME, t.von, t.bis,
p.plan_name, p.pck_id
order by 1, 2, 3, 5, 6
with ur
;
select * from A540769.plan_view2
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
xxx
select * from cmnbatch.plan_view5
where progName = 'YDG130' and queryno in ( 2453, 2454)
order by applname, progname, queryNo, qblockno, planno
;
x
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
;
set current path oa1p;
set current schema = RZ2XX;
with t as
(
select plan_name,
min(timestamp - class1_elapsed seconds) von,
(max(timestamp)) bis
from TACCT_general
where -- timestamp > timestamp(date(current_timestamp - 2 days)
-- , '00.00.00') and
plan_name in ('NI5340', 'NI5310')
group by date(timestamp), plan_name
order by date(timestamp), plan_name
)
SELECT t.*, p.PLAN_NAME, substr(PCK_ID, 1, 10),
count(*) "count",
avg(class7_elapsed) "avgElapsed" ,
sum(class7_elapsed) "totElapsed" ,
min(class7_elapsed) "minElapsed" ,
max(class7_elapsed) "maxElapsed" ,
min(timestamp -class7_elapsed seconds) "minStart",
max(timestamp) "maxEnd"
from TACCT_Program p, t
where -- timestamp > timestamp(date(current_timestamp - 2 days)
-- , '00.00.00') and
( (PCK_COLLECTION_ID = 'NI'
and pck_id in ( 'NI6210',
'NI6260',
'NI6860',
'NI7600',
'NI9700',
'NI9740',
'YNI0131',
'YNI0183',
'YNI0350',
'YNI56SR',
'YNICAMU',
'YNICRSS',
'YNICTEU',
'YNINP03',
'YNIPACG',
'YNIPAMG',
'YNIPAMU',
'YNIPFLG',
'YNIPREG',
'YNIPTEG',
'YNIPTEU',
'YNIREST'))
or (PCK_COLLECTION_ID in ('NI01', 'NI02')
and pck_id in ( 'YNICL03',
'YNINP03',
'YNIPACG'))
or (PCK_COLLECTION_ID = 'OA1P'
and pck_id in ( 'NI660AAI')))
and p.timestamp >= t.von
and p.timestamp -class7_elapsed seconds <= t.bis
group by t.PLAN_NAME, t.von, t.bis,
p.plan_name, p.pck_id
order by 1, 2, 3, 5, 6
with ur
;