set current sqlid = 'A540769'; 00010000
--SET CURRENT SQLID = 'CNMBATCH'; 00011000
--SET CURRENT DEGREE= '1' ; 00012000
--SET CURRENT DEGREE= 'ANY' ; 00013000
-- 00013100
---------------------------------------------------------------- 00013200
-- TEMPORÄRER CATALOG-UPDATE MIT WERT VON PTA ------------------ 00013300
--UPDATE SYSIBM.SYSTABLES SET CARDF = 0 00013401
-- WHERE CREATOR = 'VDPS2' AND NAME = 'XTREQSCANKEY' ; 00013501
---------------------------------------------------------------- 00015100
EXPLAIN ALL SET QUERYNO = 77897 FOR 00015300
SELECT OUT_IDSYMBOL_2 00016500
,'CH ' 00016600
,ROWSTATUS_FLTSYMB 00016700
,SYMBOLCSFI,SYMBOLCH 00016800
,SYMBOLISIN 00016900
,SYMBOLUS 00017000
,SYMBOLGB 00017100
,SYMBOLDE,CATEGORYVDPS,CATEGORYCSGTA,CSSTATUSTYPE_FLTSYMB 00017200
,CSRISKINDIDSYMBOL,CSRISKINDSCHEMESYM,CSRISKINSTGRPIDSYM 00017300
,CSRISKINSGRPSCHSYM,CSRISKCURRENCY_I1,CSRISKCURRENCY_I2 00017400
,CSRISKCURRENCY_WS80,CSRISINSTRUMENT_CH 00017500
,CSRISKCOUNTRY_A2 00017600
,CSRISKCOUNTRY_WS80,UNDERINSTRUMENT_CH 00017700
,UNDERINSTRUMENT_ISIN 00017800
,UNDERINSTRUMENT_CSFI,ORIGINALISSUER_GK 00017900
,ISSUER_GK,COMMISSIONCODETYPE 00018000
,CSFUNDTYPE,NAME45_G,NAME45_E,NAME45_F,NAME45_I 00018100
,NAME45_N 00018200
,STATUSTYPE_ORG_ISSUER,INDUSTRYCS_ORG_ISSUER 00018300
,INDUSTRYTKBN_ORG_ISSUER 00018400
,INDUSTRYMSCI_ORG_ISSUER,SHORTNAME_G_ORG_ISSUER 00018500
,SHORTNAME_E_ORG_ISSUER,SHORTNAME_F_ORG_ISSUER 00018600
,SHORTNAME_I_ORG_ISSUER 00018700
,SHORTNAME_N_ORG_ISSUER,STATUSTYPE_ISSUER,INDUSTRYMSCI_ISSUER 00018800
,SHORTNAME_G_ISSUER 00018900
,SHORTNAME_E_ISSUER,SHORTNAME_F_ISSUER 00019000
,SHORTNAME_I_ISSUER,SHORTNAME_N_ISSUER,CSPRODUCTCATEGORY 00019100
,UPDATETIMESTAMP_INSTR,ISCSRISKASPECTOTC 00019200
,ISCSRISKASPECTCOMMODITIES 00019300
,ISCSRISKASPECTREALESTATE,ISCSRISKASPECTEM 00019400
,RISKINADVICEDSERVICE 00019500
FROM OA1P.VVV_XTREQSCANKEY 00019600
INNER JOIN OA1P.VVV_IDS_VINOP9000 00019700
ON OA1P.VVV_IDS_VINOP9000.INSTRUMENTID 00019800
= OA1P.VVV_XTREQSCANKEY.TEMP_DBKEY 00019900
WHERE OA1P.VVV_XTREQSCANKEY.PARTITION_NUMBER = 2 00020000
WITH UR 00021500
FOR FETCH ONLY 00021600
; 00021700
SELECT SUBSTR(DIGITS(PT.QBLOCKNO),4,2) ||' '|| 00021800
SUBSTR(DIGITS(PT.PLANNO),4,2) ||' '|| 00021900
SUBSTR(DIGITS(PT.METHOD),4,2) ||' '|| 00022000
SUBSTR(PT.TNAME,1,20) AS B#_P#_ME_TBL, 00022100
ACCESSTYPE ||' '|| 00022200
SUBSTR(DIGITS(PT.MATCHCOLS),4,2) ||' '|| 00022300
SUBSTR(PT.ACCESSNAME,1,20) AS AT_MC_INDEX , 00022400
PT.INDEXONLY ||' '|| 00022500
PT.SORTC_UNIQ ||' '|| 00022600
PT.SORTC_JOIN ||' '|| 00022700
PT.SORTC_ORDERBY ||' '|| 00022800
PT.SORTC_GROUPBY AS IO_U_J_O_GC , 00022900
PT.SORTN_UNIQ ||' '|| 00023000
PT.SORTN_JOIN ||' '|| 00024000
PT.SORTN_ORDERBY ||' '|| 00025000
PT.SORTN_GROUPBY AS U_J_O_GN , 00026000
DEC(DC.COMPCOST ,15,3) AS COMPCOST , 00027000
PT.PREFETCH AS P, 00028000
SUBSTR(DIGITS(PT.ACCESS_DEGREE),4,2) AS DG , 00029000
PT.QBLOCK_TYPE AS TYP , 00030000
INTEGER(TB.CARDF) AS TBCARD , 00040000
TB.NPAGES AS TSPAGES , 00050000
SUBSTR(DIGITS(SMALLINT(IX.CLUSTERRATIOF*100)),3,3) AS CLR, 00060000
CLUSTERING AS C, 00070000
INTEGER(IX.FIRSTKEYCARDF) AS FIRSTKEYCARD , 00080000
INTEGER(IX.FULLKEYCARDF) AS FULLKEYCARD , 00090000
SUBSTR(DIGITS(IX.NLEVELS),3,3) AS LVL , 00100000
IX.NLEAF AS LEAFPAGES , 00110000
PT.PARENT_QBLOCKNO AS PQB , 00120000
' ' , 00130000
PT.TIMESTAMP 00140000
FROM PLAN_TABLE PT 00150000
INNER JOIN (SELECT DISTINCT 00160000
PROGNAME 00170000
,QUERYNO 00180000
,QBLOCKNO 00190000
,PLANNO 00200000
,EXPLAIN_TIME 00210000
,COMPCOST 00220000
,ONECOMPROWS 00230000
,DMROWS 00240000
,RDSROW 00250000
FROM DSN_DETCOST_TABLE) AS DC 00260000
ON PT.QUERYNO = DC.QUERYNO 00270000
AND PT.QBLOCKNO = DC.QBLOCKNO 00280000
AND PT.PLANNO = DC.PLANNO 00290000
AND PT.BIND_TIME = DC.EXPLAIN_TIME 00300000
LEFT OUTER JOIN SYSIBM.SYSINDEXES IX 00310000
ON PT.ACCESSCREATOR = IX.CREATOR 00320000
AND PT.ACCESSNAME = IX.NAME 00330000
LEFT OUTER JOIN SYSIBM.SYSTABLES TB 00340000
ON PT.TNAME = TB.NAME 00350000
AND PT.CREATOR = TB.CREATOR 00360000
WHERE PT.QUERYNO=77897 00370000
AND PT.PROGNAME='DSNESM68' 00380000
ORDER BY 1,2,3,4 00390000
; 00400000
SELECT COST_CATEGORY AS CC, 00410000
PROCMS, 00420000
PROCSU, 00430000
REASON 00440000
FROM DSN_STATEMNT_TABLE 00450000
WHERE QUERYNO=77897 00460000
AND PROGNAME='DSNESM68' 00470000
; 00480000
select * from plan_view1 00489900
order by -- collid, progName, version, bind_time, 00490000
queryNo, qblockno, planno, mixOpSeq 00490100
; 00490200
select * from plan_view2 00490300
order by -- collid, progName, version, bind_time, 00490400
queryNo, qblockno, planno, mixOpSeq 00490500
; 00490600
select * from plan_view2det 00490700
order by -- collid, progName, version, bind_time, 00490800
queryNo, qblockno, planno, mixOpSeq 00490900
; 00491000
select * 00491100
from plan_viewPred 00491200
order by --collid, progName, applName, explain_time, 00491300
queryNo, qBlockNo, planno, 00491400
stage, predNo 00491500
with ur 00491600
; 00491700
rollback 00491800
; 00491900
ROLLBACK 00492000
; 00500000