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
 

 

Which one to Choose | Left Outer Join or NOT in | DB2

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

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Mon Apr 30, 2012 8:34 pm    Post subject: Which one to Choose | Left Outer Join or NOT in | DB2
Reply with quote

Hi,

My DB2 version doesn't support the Except function, hence I would like to have suggestions on optimized way to construct a Query of

SQL 1
Minus
SQL 2


OPTION 1: USE NOT IN

SELECT A.EMP_NO FROM A
WHERE A.EMP_NO NOT IN
(SELECT B.EMP_NO FROM B)
WITH UR;

OPTION 2: USE OF LEFT OUTER JOIN

SELECT DISTINCT A.EMP_NO
FROM A LEFT OUTER JOIN B ON
A.EMP_NO = B.EMP_NO
WHERE B.EMP_NO IS NULL
WITH UR
;


I have compressed the 1st select, originally it is combination of 5 tables all with Inner Join.
Approx data to scan: 1million records.
Mode: Batch

Please suggest.

Thanks.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Apr 30, 2012 8:55 pm    Post subject:
Reply with quote

Hi Anshul,

You could do something like this also,
Code:
select a.emp_no from a
where not exists (select 1 from b where a.emp_no = b.emp_no)
with ur

Thanks,
Sushanth
Back to top
View user's profile Send private message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Mon Apr 30, 2012 9:24 pm    Post subject: Reply to: Which one to Choose | Left Outer Join or NOT in |
Reply with quote

Thanks Sushant.

Thatls more of correlated join, but still want to learn on Performance impact and the best one.

Cheers
Anshul
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Apr 30, 2012 9:56 pm    Post subject:
Reply with quote

well the only thing we can do is analyze the output to an EXPLAIN for both thses queries.

as of vsn 8 or so, there is a second table associated with the population of columns by EXPLAIN
that you can include.

after you have resolved all the normal 'bottle-necks',
you should understand what works best with the tables with stated attributes.
Back to top
View user's profile Send private message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Tue May 01, 2012 9:17 am    Post subject:
Reply with quote

Thanks Dick.

In that case, I would have to wait for the design to reach DBA as he is permiited to run EXPLAIN here, sadly.

Thanks!!
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue May 01, 2012 8:13 pm    Post subject:
Reply with quote

anshuljain26 wrote:
Thanks Dick.

In that case, I would have to wait for the design to reach DBA as he is permiited to run EXPLAIN here, sadly.

Thanks!!


well, often rookies say that,
not knowing that they could use QMF/SPUFI/Batch invocation of DSNUTIL
to populate their own explain tables.

mainly, dba's don't want you cluttering their explain tables.
as part of the bind process,
normally, explain yes is a parm,
which means, go read the explain generated from the bind.

as part of every sql reference manual, there is a discussion about
creating/populating/reading your own explain tables.

why do dba's get the design documents after the code is laid?
sounds a little back-asswards to me.

actually, i'll bet you don't know how to select the explain,
and are handing us a bunch of bat-shit about how your dba's
inhibit your development process.
Back to top
View user's profile Send private message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Tue May 01, 2012 9:12 pm    Post subject:
Reply with quote

Dick, we do not have authroity to process BIND in the environment.

A bad message from you, highly.
Back to top
View user's profile Send private message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Tue May 01, 2012 9:38 pm    Post subject:
Reply with quote

Just to add further, it's all together a different environement. I cannot name the client for which this is being used, where we have to only send them the code. No compilcation, No testing and all!!

and just to add I know the several ways here to check the EXPLAIN and even through other tools.

Just FYI: QMF has been decomissioned here due to cost factors.

and I do not understand why did you get sad for DBA's..I never meant anything there..
Guess you have had something .... for the day.


Cheers!!
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue May 01, 2012 10:11 pm    Post subject:
Reply with quote

no, i have had a good day.

as far as this thread is concerned,
1. the first thing anyone does when attempting to tune sql,
is get an EXPLAIN output and do some thinking.

2. as you have only provided excuses as to why you are right and we (i) are(am) wrong,
instead of anything that could be used to provide you an answer,
i bid you good luck and goodbye.
Back to top
View user's profile Send private message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Tue May 01, 2012 10:14 pm    Post subject:
Reply with quote

Dick, you being 65, I can't fool you. But this is how Change Technical Design is drafted here..
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 How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Coverting PD to FS and also left just... Atul Banke DFSORT/ICETOOL 6 Wed Aug 24, 2016 4:31 pm
No new posts Join key - Populate Zeros when Unpair... rexx77 DFSORT/ICETOOL 6 Thu May 12, 2016 12:22 am
No new posts Join Keys and DB2 Query jackare SYNCSORT 2 Sat Feb 13, 2016 5:57 am
No new posts Join Keys to compare two files senthamizh DFSORT/ICETOOL 2 Fri Feb 05, 2016 8:28 am


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