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
 

 

Single select to fetch from two tables

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

New User


Joined: 23 Dec 2008
Posts: 69
Location: India

PostPosted: Wed Aug 26, 2009 12:23 pm    Post subject: Single select to fetch from two tables
Reply with quote

There are two tables. Table 1 and table 2. With identical column called "name"
In a single select i must get names from both the tables.
one way is

Select name from table1
Where condition
union all
select name from table2
Where conditon

But i dont want to do in union.

I tried with

Select case when t1.name = '' then t2.name
when t1.name is null then t2.name
else t1.name
end as name
from table1 t1, table2 t2
Where conditions.

but this gave me results only from table1.. its skipping table2 since null and '' conditions are never met...
So i need to include some condition to check end of rows in table 1. I need to know how to find the end of rows in table1.
Back to top
View user's profile Send private message

bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Wed Aug 26, 2009 12:30 pm    Post subject: Reply to: Pls solve this
Reply with quote

why you dont want to use union .....please clarify
Back to top
View user's profile Send private message
sijayapal

New User


Joined: 23 Dec 2008
Posts: 69
Location: India

PostPosted: Wed Aug 26, 2009 12:35 pm    Post subject:
Reply with quote

Already Union is being used.
My work is to tune long running jobs.
This job takes around 300minutes of elapsed time and just 2 mins of cpu time. Most of the elapsed time is in the query which used union.
So i am trying other methods to replace the query.
Back to top
View user's profile Send private message
vinodgampa

New User


Joined: 20 Apr 2005
Posts: 1
Location: Hyderabad

PostPosted: Wed Aug 26, 2009 1:25 pm    Post subject:
Reply with quote

use outer joins for this one
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10275
Location: italy

PostPosted: Wed Aug 26, 2009 2:02 pm    Post subject: Reply to: Pls solve this
Reply with quote

just to find out if the time ( elapsed/cpu ) is reasonable

run a query on table 1, again on table 2

and if the sum of the two times is comparable ( within a reasonable range ) to the query with the union...

then it' s not a UNION issue... simply there is a lot of work to do icon_biggrin.gif
Back to top
View user's profile Send private message
sijayapal

New User


Joined: 23 Dec 2008
Posts: 69
Location: India

PostPosted: Wed Aug 26, 2009 2:32 pm    Post subject: Reply to: Pls solve this
Reply with quote

Let me give the actual query.
Is there any possiblity of removing the union?
In the mean time let me try with splitting the query

EXEC SQL
DECLARE CUSTPRD-CRS CURSOR FOR
SELECT ACTG_ID
,ACTG_VAL_TXT
FROM WALT_ACTG_VAL_REL
WHERE NTRL_ACCT_ID = :WAL-NTRL-ACCT-ID
UNION ALL
SELECT A.ACTG_ID
,A.ACTG_ITM_CDE ||' '||
B.ACTG_ITM_TXT
||' '
AS ACTG_VAL_TXT
FROM WAKT_ACTG_ITM_REL A
,WAHT_ACTG_ITM B
WHERE A.NTRL_ACCT_ID = :WAK-NTRL-ACCT-ID
AND A.ACTG_ID = B.ACTG_ID
AND A.ACTG_ITM_CDE = B.ACTG_ITM_CDE
FOR FETCH ONLY
END-EXEC.
Back to top
View user's profile Send private message
apandey

New User


Joined: 31 Aug 2009
Posts: 73
Location: Mumbai

PostPosted: Mon Aug 31, 2009 1:53 pm    Post subject:
Reply with quote

The better way is to use joins. Joins are more efficient if you want data from both the tables. Outer Join will be give u records from both the table.
Using union, DB2 internally created resultset of 2 tables merges it to single and them DISPLAY the results, It also checks for duplicate fields,if duplicate field found then it is skipped. While in Outer Join,the records will
be Displayed as all without removing duplicates and creating single resultset of both the tables
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Aug 31, 2009 9:06 pm    Post subject:
Reply with quote

apandey wrote:
The better way is to use joins. Joins are more efficient if you want data from both the tables. Outer Join will be give u records from both the table.
Using union, DB2 internally created resultset of 2 tables merges it to single and them DISPLAY the results, It also checks for duplicate fields,if duplicate field found then it is skipped. While in Outer Join,the records will
be Displayed as all without removing duplicates and creating single resultset of both the tables

Actually
When using UNION ALL
DB2 will NOT check for duplicates
DB2 will NOT skip duplicates
and if no sort is required for the accesspath
DB2 will NOT create an internal resultset

An outer join is definitely not always better.
A simple union all will access the two table each in its best way.

An outer join will have to access both tables on the join condition.
If no index is available a join will probably result in one or more sorts and a merge scan join.
Back to top
View user's profile Send private message
apandey

New User


Joined: 31 Aug 2009
Posts: 73
Location: Mumbai

PostPosted: Tue Sep 01, 2009 10:22 am    Post subject: Reply to: Single select to fetch from two tables
Reply with quote

Dude,read my statements clearly, I have spoken abt UNION not abt
UNION ALL, the points which u have mentioned abt Union all, i am aware of that points. Regarding Outer join, i have tested the difference between
JOINS and UNION ALL, i found JOINS are much more effective in terms
of performance wise.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Sep 01, 2009 1:48 pm    Post subject:
Reply with quote

dude, read the sql it says 'union all' so your "advice" was ,although interesting, besides the point.
I'm glad your joins are faster then union alls, but performance has nothing to do with union all vs outer joins, but everything with available indexes.

If you really want to test something, test this :
- Table A PK&ix on column A1 clustering ix on column A2
- Table B PK&&clustering ix on column B1,B2 ; ix on column A1

Code:
select A.col3, b.col3
from A full outer join B on a.A1 = B.A1
and A.A2 between ? and ?
and B.B1 = ?

Actually the code above would give you wrong results.
Code:
select A.Col3, b.Col3
from (select a.A1,a.col3 from A where A.A2 between ? and ?) a
full outer join
(select B.A1,B.col3 from B where B.B1 = ?) b
on a.A1 = B.A1
will give you results , but very bad performance
And then in your program you will have to start testing which columns are filled in a.col3 or b.col3 or both
Code:

select col3 from A where A.A2 between ? and ?
union all
select col3 from B where B.B1 = ?
will give you result in a reasonable time
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 Read two inputs and write into output... murali.andaluri DFSORT/ICETOOL 4 Wed Jul 26, 2017 7:35 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
This topic is locked: you cannot edit posts or make replies. SDSF multiple spool datasets extracte... PJAlarcon CLIST & REXX 1 Fri Apr 21, 2017 10:50 pm


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