By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,702 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

SQL explain plan

P: n/a
JAW
This plan seems like it should perform well. Does anyone see anything.
SQL Statement Text:

DECLARE MTR - RDG - EST - CSR CURSOR

FOR

SELECT A.KY_MTR_BILL_GRP , A.KY_MRDG_ASGNMT , A.KY_MRDG_SEQ_ACTL ,

A.CD_MPT_TYPE , A.KY_PREM_NO , A.KY_MRDG_SEQ_NO , A.KY_BA ,

A.DT_MTR_GRP_EFF , A.DT_NXT_SCH_RDG , A.CD_SIC , A.FL_AC ,
A.KY_SPT ,
A.KY_MPT_NO , A.KY_RDG , A.CD_MPT_STAT , A.QY_MTR_MULT ,
A.CD_MTR_MFGR ,
A.KY_MTR_NO , A.QY_NO_OF_DIAL , A.CD_TAR_SCH , A.CD_TAR_TYPE ,

A.QY_CONS_UNIT , A.CD_STAT

FROM MRDG_STAT A , BILL_ACCT B , OFFICE C , CO_RULE D

WHERE A.KY_BA = B.KY_BA

AND B.CD_OFFICE = C.CD_OFFICE

AND C.CD_CO = D.CD_CO

AND D.KY_RULE_NO = :CU05TB08.KY-RULE-NO

AND

(

(

A.KY_MTR_BILL_GRP = :MISC-APPL-VARIABLES.WS-KY-BILL-GRP-3

AND D.QY_RULE_DATA = 3

)

OR

(

A.KY_MTR_BILL_GRP = :MISC-APPL-VARIABLES.WS-KY-BILL-GRP-1

AND D.QY_RULE_DATA = 1

)

)

AND A.FL_READ_REC = :CU04TB28.FL-READ-REC

AND A.KY_BA >= :WS-COUNTERS.WS-MRDG-STAT-KY-BA-LOW

AND A.KY_BA <= :WS-COUNTERS.WS-MRDG-STAT-KY-BA-HIGH

ORDER BY A.KY_BA , A.KY_MRDG_SEQ_ACTL , A.CD_MPT_TYPE

Access Path Analysis:

PQbkNo: 0 QblkNo: 1 PlanNo: 1 MxOpSq: 0 TBTyp:
TABLE
Access: IXDATA TSLock: IS Prefet: S QblkTy: SEL TabNo: 2

TBCrtr: CISC50 TBName: BILL_ACCT CorrNm: B

IXCrtr: CISC50 IXName: X01002U1 MCols : 1

PllMod: QUERY CP AcDegr: 0 AcPgrp: 1 SrtnPg:

PQbkNo: 0 QblkNo: 1 PlanNo: 2 MxOpSq: 0 TBTyp:
TABLE
Access: IXDATA TSLock: IS Prefet: QblkTy: SEL TabNo: 3

Method: Nested Loop Join JoinTy: Inner MergJC:

TBCrtr: CISC50 TBName: OFFICE CorrNm: C

IXCrtr: CISC50 IXName: X01148U1 MCols : 1

PllMod: QUERY CP AcDegr: 0 AcPgrp: 1 SrtnPg:

JnDegr: 0 JnPgrp: 1 SrtcPg:

PPA REL. 2.3 --------- Plan Analyzer Enhanced Explain -------------
Page
1 2006/05/08 20:54
PAGE 5
------------------------------------------------------------- USER
ID: OPRJAW0
DATE 05-08-06 SQL Statement Detail Reports
TIME 20:4


PQbkNo: 0 QblkNo: 1 PlanNo: 3 MxOpSq: 0 TBTyp:
TABLE
Access: IXDATA TSLock: IS Prefet: QblkTy: SEL TabNo: 4

Method: Nested Loop Join JoinTy: Inner MergJC:

TBCrtr: CISC50 TBName: CO_RULE CorrNm: D

IXCrtr: CISC50 IXName: X01307U1 MCols : 2

PllMod: QUERY CP AcDegr: 0 AcPgrp: 1 SrtnPg:

JnDegr: 0 JnPgrp: 1 SrtcPg:

PQbkNo: 0 QblkNo: 1 PlanNo: 4 MxOpSq: 0 TBTyp:
TABLE
Access: IXDATA TSLock: IS Prefet: S QblkTy: SEL TabNo: 1

Method: Nested Loop Join JoinTy: Inner MergJC:

TBCrtr: CISC50 TBName: MRDG_STAT CorrNm: A

IXCrtr: CISC50 IXName: X01040U2 MCols : 0

PllMod: QUERY CP AcDegr: 0 AcPgrp: 1 SrtnPg:

JnDegr: 0 JnPgrp: 1 SrtcPg:

PQbkNo: 0 QblkNo: 1 PlanNo: 5 MxOpSq: 0 TBTyp:

Method: Result Table Sorted

Sorts Composite Unique: N Join : N Ordrby: Y Grpby: N

SQL Dependency Analysis:

Tablespace Database L Buffpool Pgsze PS/SS
NTbl
Table/Alias/View/Synm Creator Encoding

Index (# Cols) Type Creator U Buffpool Pgsze

----------------------- -------- -------- - ---------- ----- -----
-----
S01G040 Segment DCIS02C1 A BP2 4 64 1

MRDG_STAT Table CISC50 EBCDIC

X01040D1 Type-2 CISC50 D BP3 4096

1

X01040U1 C Type-2 CISC50 U BP3 4096

(UNIQUE,CLUSTERI 4

X01040U2 C Type-2 CISC50 U BP3 4096

(UNIQUE) 6

S01G148 Segment DCIS02C1 A BP2 4 4 1

OFFICE Table CISC50 EBCDIC

X01148U1 C Type-2 CISC50 U BP3 4096

(UNIQUE,CLUSTERI 1

S01G307 Segment DCIS02C1 A BP2 4 4 1

CO_RULE Table CISC50 EBCDIC

X01307U1 Type-2 CISC50 U BP3 4096

(UNIQUE,CLUSTERI 4

S01P002 Parttn DCIS02C1 A BP2 4 4 1

BILL_ACCT Table CISC50 EBCDIC

X01002D2 Type-2 CISC50 D BP3 4096

4

X01002D3 Type-2 CISC50 D BP3 4096

1

1 2006/05/08 20:54
PAGE 6
------------------------------------------------------------- USER
ID: OPRJAW0
X01002D4 Type-2 CISC50 D BP3 4096

1

X01002U1 C Type-2 CISC50 U BP2 4096

(UNIQUE,CLUSTERI 1

X01002U2 C Type-2 CISC50 U BP3 4096

PPA REL. 2.3 --------- Plan Analyzer Enhanced Explain -------------
Page
DATE 05-08-06 SQL Statement Detail Reports
TIME 20:4
(UNIQUE) 2

X01002U3 Type-2 CISC50 U BP3 4096

(UNIQUE) 5

X01002U5 C Type-2 CISC50 U BP3 4096

(UNIQUE) 2

SQL Predicate Analysis:


Filter
Predicate Index
Sarge Factor
-------------------------------------------------------- -----
----- -------
STATEMENT #001

---> A . KY_BA = B . KY_BA YES YES
0.00001
---> B . CD_OFFICE = C . CD_OFFICE YES YES
0.01724
---> C . CD_CO = D . CD_CO YES YES
0.50000
---> D . KY_RULE_NO = :CU05TB08.KY-RULE-NO YES YES
0.40000
---> A . KY_MTR_BILL_GRP = YES YES
0.16666
:MISC-APPL-VARIABLES.WS-KY-BILL-GRP-3

---> D . QY_RULE_DATA = 3 YES YES
0.40000
---> A . KY_MTR_BILL_GRP = YES YES
0.16666
:MISC-APPL-VARIABLES.WS-KY-BILL-GRP-1

---> D . QY_RULE_DATA = 1 YES YES
0.40000
---> A . FL_READ_REC = :CU04TB28.FL-READ-REC YES YES
0.40000
---> A . KY_BA >= :WS-COUNTERS.WS-MRDG-STAT-KY-BA-LOW YES YES
0.33333
---> A . KY_BA <= :WS-COUNTERS.WS-MRDG-STAT-KY-BA-HIGH YES YES
0.33333


PPA REL. 2.3 --------- Plan Analyzer Enhanced Explain -------------
Page 1
DATE 05-08-06 Performance Report
TIME 20:4
Performance data not Available.

Performance Group id 0

1 2006/05/08 20:54
PAGE 7
------------------------------------------------------------- USER
ID: OPRJAW0
Statement id 0

PPA REL. 2.3 --------- Plan Analyzer Enhanced Explain -------------
Page 1
DATE 05-08-06 SQL Statement Detail Reports
TIME 20:4
Source DB2 SSID: DB2D Date/time: 05-08-06,
13:16:09:7
Collection: CISC5 DB2 SSID : DB2D

Package : CUBES001 SQL Code : +0

Stmt : 5,865 (5) Message :

Version :

Hint Used : N/A

May 9 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.