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 sql and 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


Please look at the the explain plan output below, in my next post. It is posted below because the text was too long and didn't fit in this window.

Please see below... Thanks!

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

Is your hardware appropriate for intra-partition parallelism? Try setting it to 1. Also, remove DISTINCT and use UNION instead of UNION ALL; that will require one sort instead of three. Is the cardinality estimate correct (that is, do you expect the query to return very few rows)? If not, statistics may not be up to date.

It might be also a good idea to indicate your platform and the DB2 version.

you might, also, want to think about your DB design. wouldn't a pid table that contaied your pid nbr and the type of pid make things a lot simpler?