Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

COALESCE stage 1 or stage 2?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
revdpoel

New User


Joined: 01 Nov 2006
Posts: 56

PostPosted: Wed Jun 18, 2014 1:18 am    Post subject: COALESCE stage 1 or stage 2?
Reply with quote

Hello

This is a part of a query.
Using COALESCE, is that stage 1 or 2?
T400.rek-nr and t401a.bbo-key are part of an index

LEFT JOIN GWTB400 T400
ON T400.REK_NR = COALESCE(T812.REK_NR1,T812.REK_NR2)
AND T400.MSRT_KEY = :W-MSRT-KEY
AND T400.VRWYD_DAT IS NULL

INNER JOIN GWTB400 D400
ON D400.REK_NR = -1

LEFT JOIN GWTB401 T401A
ON T401A.BBO_KEY = COALESCE(T400.BBO_KEY,D400.BBO_KEY)
AND T401A.VA_DAT <= :W-T403-DAT
AND T401A.TM_DAT >= :W-T403-DAT

thnx
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1649
Location: NY,USA

PostPosted: Wed Jun 18, 2014 2:32 am    Post subject:
Reply with quote

Code:
DB2 manuals provide a complete description of when a predicate can be stage 2 versus stage 1. Generally, stage 2 occurs after data accesses and performs such actions as sorting and evaluation of functions and expressions. Stage 2 predicates cannot take advantage of indexes to limit the data access and are more expensive than stage 1 predicates because they are evaluated later in the processing.
 
Stage 2 predicates generally contain column expressions, correlated subqueries, CASE expressions, and so on. A predicate can also appear to be stage 1, but it can be processed as stage 2. For example, any predicate that is processed after a join operation is stage 2. Although DB2 promotes mismatched data types to stage 1 through casting (as of version DB2 V8), some predicates with mismatched data types are stage 2. One example is a range predicate comparing a character column to a character value that exceeds the length of the column. The following are examples of stage 2 predicates (EMPNO is a character column of fixed length 6):
 SELECT LASTNAME, FIRSTNME
FROM   EMP
WHERE  EMPNO > '00000010'
 
SELECT LASTNAME, FIRSTNME
FROM   EMP
WHERE  SUBSTR(LASTNAME,1,1) = 'B'


Why do use COALESCE in joins? There are other much better ways to do it?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Not able to stage components from dev... spoorni IBM Tools 4 Tue Mar 11, 2014 1:08 am
No new posts DB2 coalesce function for replacing n... Sanath sekhar Reddy N DB2 1 Thu Jul 11, 2013 6:11 pm
No new posts Changeman :- What is difference betwe... udaysnimje All Other Mainframe Topics 3 Tue Apr 03, 2012 8:04 pm
No new posts issues with COALESCE function tecnokrat DB2 6 Wed Jul 28, 2010 9:12 am
No new posts Unloading data along with coalesce fu... gansin DB2 4 Thu Oct 01, 2009 2:52 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us