|
Hello,
one sql statement is causing severe performance issue. The problem occurs only in UDB environment, the same statemnt on the mainframe is running fine. I have an explain output from the sql. The statement itself is not that complicated, it is 3 selects and union all. Explain output is pretty big, but I could not find anything unusual. I'm new to db2 and I could be missing stuff. I am posting the explain output below and I really appreciate your response.
Thank you
SQL Statement:
SELECT DISTINCT CUS_CID_CUST_ID , 'AGENT', CUS_NME_SHORT_NAME
FROM S2USER.VLS_CUSTOMER , S2USER.VLS_DEAL_ADMIN ,
S2USER.VLS_FAM_GLOBAL2 , S2USER.VLS_CUST_CONTACT,
S2USER.VLS_CUST_CON_PURP, S2USER.VLS_CONT_PURP
WHERE DAD_PID_DEAL ='-Q6V797H'AND DAD_CID_CUST_ID =CUS_CID_CUST_ID
AND CON_CID_CUST_ID =CUS_CID_CUST_ID AND CCP_RID_CONTACT =
CON_RID_CONTACT AND CCP_CDE_PURPOSE =GB2_CDE_CODE AND
CPU_CDE_CONT_PURP =CON_CDE_PURPOSE AND GB2_TID_TABLE_ID ='
CPU'AND GB2_CDE_FKEY1 ='CRDT'
UNION ALL
SELECT DISTINCT CUS_CID_CUST_ID , 'LENDR', CUS_NME_SHORT_NAME
FROM S2USER.VLS_CUSTOMER , S2USER.VLS_FAC_COMMIT_TRN,
S2USER.VLS_FAC_TR_SG_SHR , S2USER.VLS_FACILITY ,
S2USER.VLS_FAM_GLOBAL2 , S2USER.VLS_CUST_CONTACT ,
S2USER.VLS_CUST_CON_PURP, S2USER.VLS_CONT_PURP
WHERE FAC_PID_DEAL ='-Q6V797H'AND FTR_PID_FACILITY =
FAC_PID_FACILITY AND FTS_CID_LENDER =CUS_CID_CUST_ID AND
FTS_RID_FAC_TRAN =FTR_RID_COMMIT_TRN AND CON_CID_CUST_ID =
FTS_CID_LENDER AND CCP_RID_CONTACT =CON_RID_CONTACT AND
CCP_CDE_PURPOSE =GB2_CDE_CODE AND CPU_CDE_CONT_PURP =
CON_CDE_PURPOSE AND GB2_TID_TABLE_ID ='CPU'AND
GB2_CDE_FKEY1 ='CRDT'
UNION ALL
SELECT DISTINCT CUS_CID_CUST_ID , 'BORR', CUS_NME_SHORT_NAME
FROM S2USER.VLS_CUSTOMER , S2USER.VLS_DEAL_BORROWER,
S2USER.VLS_FAM_GLOBAL2 , S2USER.VLS_CUST_CONTACT,
S2USER.VLS_CUST_CON_PURP, S2USER.VLS_CONT_PURP
WHERE DBR_PID_DEAL ='-Q6V797H'AND DBR_CID_CUST_ID =CUS_CID_CUST_ID
AND CON_CID_CUST_ID =CUS_CID_CUST_ID AND CCP_RID_CONTACT =
CON_RID_CONTACT AND CCP_CDE_PURPOSE =GB2_CDE_CODE AND
CPU_CDE_CONT_PURP =CON_CDE_PURPOSE AND GB2_TID_TABLE_ID ='
CPU'AND GB2_CDE_FKEY1 ='CRDT'
FOR
FETCH ONLY
Intra-Partition Parallelism Degree = 2
Section Code Page = 819
Estimated Cost = 6585.949219
Estimated Cardinality = 0.000003
(
| Process Using 2 Subagents
| | Access Table Name = S2USER.TLS_DEAL_ADMIN ID = 3,534
| | | Index Scan: Name = S2USER.XDADUC01 ID = 1
| | | | Regular Index (Clustered)
| | | | Index Columns:
| | | | | 1: DAD_PID_DEAL (Ascending)
| | | #Columns = 1
| | | Parallel Scan
| | | Single Record
| | | Fully Qualified Unique Key
| | | Skip Inserted Rows
| | | Skip Deleted Rows
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: '-Q6V797H'
| | | | Stop Key: Inclusive Value
| | | | | | 1: '-Q6V797H'
| | | Data Prefetch: None
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | Nested Loop Join
| | | Access Table Name = S2USER.TLS_CUST_CONTACT ID = 3,12
| | | | Index Scan: Name = S2USER.XCONNC02 ID = 4
| | | | | Regular Index (Clustered)
| | | | | Index Columns:
| | | | | | 1: CON_CID_CUST_ID (Ascending)
| | | | #Columns = 3
| | | | Skip Inserted Rows
| | | | Skip Deleted Rows
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | | 1: ?
| | | | | Stop Key: Inclusive Value
| | | | | | | 1: ?
| | | | Data Prefetch: None
| | | | Index Prefetch: None
| | | | Lock Intents
| | | | | Table: Intent Share
| | | | | Row : Next Key Share
| | | | Sargable Predicate(s)
| | | | | Process Build Table for Hash Join
| | Hash Join
| | | Early Out: Single Match Per Inner Row
| | | Estimated Build Size: 4391
| | | Estimated Probe Size: 4407
| | | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
| | | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
| | | | | Regular Index (Clustered)
| | | | | Index Columns:
| | | | | | 1: GB2_TID_TABLE_ID (Ascending)
| | | | | | 2: GB2_CDE_CODE (Ascending)
| | | | #Columns = 2
| | | | Parallel Scan
| | | | Skip Inserted Rows
| | | | Skip Deleted Rows
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | | 1: 'CPU'
| | | | | Stop Key: Inclusive Value
| | | | | | | 1: 'CPU'
| | | | Index-Only Access
| | | | Index Prefetch: None
| | | | Lock Intents
| | | | | Table: Intent Share
| | | | | Row : Next Key Share
| | | | Sargable Index Predicate(s)
| | | | | Process Probe Table for Hash Join
| | Nested Loop Join
| | | Access Table Name = S2USER.TLS_CUSTOMER ID = 3,3083
| | | | Index Scan: Name = S2USER.XCUSUN01 ID = 4
| | | | | Regular Index (Not Clustered)
| | | | | Index Columns:
| | | | | | 1: CUS_CID_CUST_ID (Ascending)
| | | | #Columns = 2
| | | | Single Record
| | | | Fully Qualified Unique Key
| | | | Skip Inserted Rows
| | | | Skip Deleted Rows
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | | 1: ?
| | | | | Stop Key: Inclusive Value
| | | | | | | 1: ?
| | | | Data Prefetch: None
| | | | Index Prefetch: None
| | | | Lock Intents
| | | | | Table: Intent Share
| | | | | Row : Next Key Share
| | Nested Loop Join
| | | Access Table Name = S2USER.TLS_CUST_CON_PURP ID = 3,19
| | | | Index Scan: Name = S2USER.XCCPUC01 ID = 1
| | | | | Regular Index (Clustered)
| | | | | Index Columns:
| | | | | | 1: CCP_RID_CONTACT (Ascending)
| | | | | | 2: CCP_CDE_PURPOSE (Ascending)
| | | | #Columns = 1
| | | | Skip Inserted Rows
| | | | Skip Deleted Rows
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | | 1: ?
| | | | | Stop Key: Inclusive Value
| | | | | | | 1: ?
| | | | Index-Only Access
| | | | Index Prefetch: None
| | | | Lock Intents
| | | | | Table: Intent Share
| | | | | Row : Next Key Share
| | Nested Loop Join
| | | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
| | | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
| | | | | Regular Index (Clustered)
| | | | | Index Columns:
| | | | | | 1: GB2_TID_TABLE_ID (Ascending)
| | | | | | 2: GB2_CDE_CODE (Ascending)
| | | | #Columns = 1
| | | | Single Record
| | | | Fully Qualified Unique Key
| | | | Skip Inserted Rows
| | | | Skip Deleted Rows
| | | | #Key Columns = 2
| | | | | Start Key: Inclusive Value
| | | | | | | 1: 'CPU'
| | | | | | | 2:
| | | | | Stop Key: Inclusive Value
| | | | | | | 1: 'CPU'
| | | | | | | 2:
| | | | Data Prefetch: None
| | | | Index Prefetch: None
| | | | Lock Intents
| | | | | Table: Intent Share
| | | | | Row : Next Key Share
| | | | Sargable Predicate(s)
| | | | | #Predicates = 1
| | Insert Into Asynchronous Local Table Queue ID = q1
| Access Local Table Queue ID = q1 #Columns = 2
| Distinct Filter #Columns = 1
UNION
| Access Table Name = S2USER.TLS_FACILITY ID = 3,1536
| | Index Scan: Name = S2USER.XFACUN02 ID = 6
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: FAC_PID_DEAL (Ascending)
| | | | 2: FAC_NME_FACILITY (Ascending)
| | #Columns = 1
| | Skip Inserted Rows
| | Skip Deleted Rows
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: '-Q6V797H'
| | | Stop Key: Inclusive Value
| | | | | 1: '-Q6V797H'
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| Nested Loop Join
| | Access Table Name = S2USER.TLS_FAC_COMMIT_TRN ID = 3,1552
| | | Index Scan: Name = S2USER.XFTRNC02 ID = 3
| | | | Regular Index (Clustered)
| | | | Index Columns:
| | | | | 1: FTR_PID_DEAL (Ascending)
| | | | | 2: FTR_PID_FACILITY (Ascending)
| | | #Columns = 1
| | | Skip Inserted Rows
| | | Skip Deleted Rows
| | | Evaluate Index Predicates Before Locking Key
| | | #Key Columns = 0
| | | | Start Key: Beginning of Index
| | | | Stop Key: End of Index
| | | Data Prefetch: Eligible 15
| | | Index Prefetch: Eligible 15
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Index Predicate(s)
| | | | #Predicates = 1
| Nested Loop Join
| | Data Stream 1:
| | | Not Piped
| | | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
| | | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
| | | | | Regular Index (Clustered)
| | | | | Index Columns:
| | | | | | 1: GB2_TID_TABLE_ID (Ascending)
| | | | | | 2: GB2_CDE_CODE (Ascending)
| | | | #Columns = 2
| | | | Skip Inserted Rows
| | | | Skip Deleted Rows
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | | 1: 'CPU'
| | | | | Stop Key: Inclusive Value
| | | | | | | 1: 'CPU'
| | | | Data Prefetch: None
| | | | Index Prefetch: None
| | | | Lock Intents
| | | | | Table: Intent Share
| | | | | Row : Next Key Share
| | | | Sargable Predicate(s)
| | | | | #Predicates = 1
| | | | | Process Build Table for Hash Join
| | | Hash Join
| | | | Early Out: Single Match Per Outer Row
| | | | Estimated Build Size: 4001
| | | | Estimated Probe Size: 4776574
| | | | Bit Filter Size: 800
| | | | Access Table Name = S2USER.TLS_CUST_CON_PURP ID = 3,19
| | | | | Index Scan: Name = S2USER.XCCPUC01 ID = 1
| | | | | | Regular Index (Clustered)
| | | | | | Index Columns:
| | | | | | | 1: CCP_RID_CONTACT (Ascending)
| | | | | | | 2: CCP_CDE_PURPOSE (Ascending)
| | | | | #Columns = 2
| | | | | Skip Inserted Rows
| | | | | Skip Deleted Rows
| | | | | #Key Columns = 0
| | | | | | Start Key: Beginning of Index
| | | | | | Stop Key: End of Index
| | | | | Index-Only Access
| | | | | Index Prefetch: Eligible 1206
| | | | | Lock Intents
| | | | | | Table: Intent Share
| | | | | | Row : Next Key Share
| | | | | Sargable Index Predicate(s)
| | | | | | Process Probe Table for Hash Join
| | | Nested Loop Join
| | | | Access Table Name = S2USER.TLS_CUST_CONTACT ID = 3,12
| | | | | Index Scan: Name = S2USER.XCONUN01 ID = 3
| | | | | | Regular Index (Not Clustered)
| | | | | | Index Columns:
| | | | | | | 1: CON_RID_CONTACT (Ascending)
| | | | | #Columns = 2
| | | | | Single Record
| | | | | Fully Qualified Unique Key
| | | | | Skip Inserted Rows
| | | | | Skip Deleted Rows
| | | | | #Key Columns = 1
| | | | | | Start Key: Inclusive Value
| | | | | | | | 1: ?
| | | | | | Stop Key: Inclusive Value
| | | | | | | | 1: ?
| | | | | Data Prefetch: None
| | | | | Index Prefetch: None
| | | | | Lock Intents
| | | | | | Table: Intent Share
| | | | | | Row : Next Key Share
| | | Nested Loop Join
| | | | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
| | | | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
| | | | | | Regular Index (Clustered)
| | | | | | Index Columns:
| | | | | | | 1: GB2_TID_TABLE_ID (Ascending)
| | | | | | | 2: GB2_CDE_CODE (Ascending)
| | | | | #Columns = 1
| | | | | Single Record
| | | | | Fully Qualified Unique Key
| | | | | Skip Inserted Rows
| | | | | Skip Deleted Rows
| | | | | #Key Columns = 2
| | | | | | Start Key: Inclusive Value
| | | | | | | | 1: 'CPU'
| | | | | | | | 2:
| | | | | | Stop Key: Inclusive Value
| | | | | | | | 1: 'CPU'
| | | | | | | | 2:
| | | | | Index-Only Access
| | | | | Index Prefetch: None
| | | | | Lock Intents
| | | | | | Table: Intent Share
| | | | | | Row : Next Key Share
| | | Nested Loop Join
| | | | Access Table Name = S2USER.TLS_CUSTOMER ID = 3,3083
| | | | | Index Scan: Name = S2USER.XCUSUN01 ID = 4
| | | | | | Regular Index (Not Clustered)
| | | | | | Index Columns:
| | | | | | | 1: CUS_CID_CUST_ID (Ascending)
| | | | | #Columns = 2
| | | | | Single Record
| | | | | Fully Qualified Unique Key
| | | | | Skip Inserted Rows
| | | | | Skip Deleted Rows
| | | | | #Key Columns = 1
| | | | | | Start Key: Inclusive Value
| | | | | | | | 1: ?
| | | | | | Stop Key: Inclusive Value
| | | | | | | | 1: ?
| | | | | Data Prefetch: None
| | | | | Index Prefetch: None
| | | | | Lock Intents
| | | | | | Table: Intent Share
| | | | | | Row : Next Key Share
| | | Insert Into Temp Table ID = t1
| | | | #Columns = 3
| | End of Data Stream 1
| | Access Temp Table ID = t1
| | | #Columns = 3
| | | Relation Scan
| | | | Prefetch: Eligible
| | | Sargable Predicate(s)
| | | | Insert Into Sorted Temp Table ID = t2
| | | | | #Columns = 4
| | | | | #Sort Key Columns = 1
| | | | | | Key 1: (Ascending)
| | | | | Sortheap Allocation Parameters:
| | | | | | #Rows = 1
| | | | | | Row Width = 60
| | | | | Piped
| Sorted Temp Table Completion ID = t2
| Access Temp Table ID = t2
| | #Columns = 4
| | Relation Scan
| | | Prefetch: Eligible
| Nested Loop Join
| | Access Table Name = S2USER.TLS_FAC_TR_SG_SHR ID = 3,1559
| | | Index Scan: Name = S2USER.XFTSNC02 ID = 4
| | | | Regular Index (Clustered)
| | | | Index Columns:
| | | | | 1: FTS_RID_FAC_TRAN (Ascending)
| | | #Columns = 1
| | | Single Record
| | | Skip Inserted Rows
| | | Skip Deleted Rows
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | | 1: ?
| | | Data Prefetch: None
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | #Predicates = 1
| Distinct Filter #Columns = 1
UNION
| Access Table Name = S2USER.TLS_DEAL_BORROWER ID = 3,536
| | Index Scan: Name = S2USER.XDBRNC02 ID = 2
| | | Regular Index (Clustered)
| | | Index Columns:
| | | | 1: DBR_PID_DEAL (Ascending)
| | | | 2: DBR_RID_LOCATION (Ascending)
| | #Columns = 1
| | Skip Inserted Rows
| | Skip Deleted Rows
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: '-Q6V797H'
| | | Stop Key: Inclusive Value
| | | | | 1: '-Q6V797H'
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| Nested Loop Join
| | Access Table Name = S2USER.TLS_CUST_CONTACT ID = 3,12
| | | Index Scan: Name = S2USER.XCONNC02 ID = 4
| | | | Regular Index (Clustered)
| | | | Index Columns:
| | | | | 1: CON_CID_CUST_ID (Ascending)
| | | #Columns = 3
| | | Skip Inserted Rows
| | | Skip Deleted Rows
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | | 1: ?
| | | Data Prefetch: None
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | Process Build Table for Hash Join
| Hash Join
| | Early Out: Single Match Per Inner Row
| | Estimated Build Size: 4533
| | Estimated Probe Size: 4407
| | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
| | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
| | | | Regular Index (Clustered)
| | | | Index Columns:
| | | | | 1: GB2_TID_TABLE_ID (Ascending)
| | | | | 2: GB2_CDE_CODE (Ascending)
| | | #Columns = 2
| | | Skip Inserted Rows
| | | Skip Deleted Rows
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: 'CPU'
| | | | Stop Key: Inclusive Value
| | | | | | 1: 'CPU'
| | | Index-Only Access
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Index Predicate(s)
| | | | Process Probe Table for Hash Join
| Nested Loop Join
| | Access Table Name = S2USER.TLS_CUST_CON_PURP ID = 3,19
| | | Index Scan: Name = S2USER.XCCPUC01 ID = 1
| | | | Regular Index (Clustered)
| | | | Index Columns:
| | | | | 1: CCP_RID_CONTACT (Ascending)
| | | | | 2: CCP_CDE_PURPOSE (Ascending)
| | | #Columns = 1
| | | Skip Inserted Rows
| | | Skip Deleted Rows
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | | 1: ?
| | | Index-Only Access
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| Nested Loop Join
| | Access Table Name = S2USER.TLS_CUSTOMER ID = 3,3083
| | | Index Scan: Name = S2USER.XCUSUN01 ID = 4
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: CUS_CID_CUST_ID (Ascending)
| | | #Columns = 2
| | | Single Record
| | | Fully Qualified Unique Key
| | | Skip Inserted Rows
| | | Skip Deleted Rows
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | | 1: ?
| | | Data Prefetch: None
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| Nested Loop Join
| | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
| | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
| | | | Regular Index (Clustered)
| | | | Index Columns:
| | | | | 1: GB2_TID_TABLE_ID (Ascending)
| | | | | 2: GB2_CDE_CODE (Ascending)
| | | #Columns = 1
| | | Single Record
| | | Fully Qualified Unique Key
| | | Skip Inserted Rows
| | | Skip Deleted Rows
| | | #Key Columns = 2
| | | | Start Key: Inclusive Value
| | | | | | 1: 'CPU'
| | | | | | 2:
| | | | Stop Key: Inclusive Value
| | | | | | 1: 'CPU'
| | | | | | 2:
| | | Data Prefetch: None
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | #Predicates = 1
| Insert Into Sorted Temp Table ID = t3
| | #Columns = 4
| | #Sort Key Columns = 2
| | | Key 1: (Ascending)
| | | Key 2: CUS_CID_CUST_ID (Ascending)
| | Sortheap Allocation Parameters:
| | | #Rows = 1
| | | Row Width = 56
| | Piped
| | Duplicate Elimination
| Access Temp Table ID = t3
| | #Columns = 4
| | Relation Scan
| | | Prefetch: Eligible
)
Return Data to Application
| #Columns = 3
End of section
| 
