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
