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
 

 

Help need to understand a DB2 query

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

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Wed Dec 16, 2009 2:57 pm    Post subject: Help need to understand a DB2 query
Reply with quote

Hi,
I have an update query which is currently running in production. I need to understand the logic behind the query.

Code:

UPDATE TABLE_A
SET TAB_A_COL1 =
(
  SELECT DISTINCT TAB_B_COL1 FROM TABLE_B
  WHERE TAB_B_COL2='XYZ'
)
WHERE TAB_A_COL2='ABC'
AND EXISTS
(
  SELECT 'Y' FROM TABLE_B
  WHERE TAB_B_COL2='XYZ'
)



A SELECT query on TABLE_B is performed twice with the same WHERE clause.Why?
What difference is the EXISTS making?

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

Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Dec 16, 2009 3:58 pm    Post subject:
Reply with quote

It will update the table A only if there is a row in the table B with
TAB_B_COL2 = 'XYZ'.
If you remove the EXISTS clause, then it will update table A even if there is no row in table B with TAB_B_COL2 = 'XYZ'
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Wed Dec 16, 2009 4:37 pm    Post subject:
Reply with quote

Got what you meant.
But this inner query
Code:

SELECT 'Y' FROM TABLE_B
 WHERE TAB_B_COL2='XYZ'

is executed twice. Is there a way of optimization where we can make this query to run only once without affecting the existing logic.
Back to top
View user's profile Send private message
Lijo

New User


Joined: 24 Aug 2008
Posts: 41
Location: Cochin

PostPosted: Wed Dec 16, 2009 4:51 pm    Post subject: Reply to: Help need to understand a DB2 query
Reply with quote

Quote:
If you remove the EXISTS clause, then it will update table A even if there is no row in table B with TAB_B_COL2 = 'XYZ'


If there is no row in Table B for 'XYZ', then how will you get the value for updating Table A?

Quote:
What difference is the EXISTS making?


If you want to see the difference, try executing this query with 'Exists' clause and without 'exists' clause.

If there is no row in Table B for XYZ, then with 'Exists' query will return +100. i.e. NO ROWS found for Update

But without 'exists' clause will return a -407 and results in SQL error in production. To avoid this situation your production query is having 'Exists' clause.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Dec 16, 2009 5:32 pm    Post subject:
Reply with quote

r arunmoezhi wrote:
Got what you meant.
But this inner query
Code:

SELECT 'Y' FROM TABLE_B
 WHERE TAB_B_COL2='XYZ'

is executed twice. Is there a way of optimization where we can make this query to run only once without affecting the existing logic.


I guess not. Is there any particular reason you want to tweak this query? I don't think this query might be taking much time to execute.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Dec 16, 2009 6:16 pm    Post subject:
Reply with quote

The exists is just to make sure that your set query does not return null values and it might get updated in TAB_A_COL1. If you are sure that the query

SELECT DISTINCT TAB_B_COL1 FROM TABLE_B
WHERE TAB_B_COL2='XYZ'

is always going to return a value you can remove the EXISTS condition.

It will give -407 only if the column TAB_A_COL1 is defined as not null. If that is the case you can remove the EXISTS clause and handle -407 after the update query.
Back to top
View user's profile Send private message
senthilssg

New User


Joined: 09 Dec 2005
Posts: 64
Location: USA

PostPosted: Tue Dec 29, 2009 4:37 am    Post subject:
Reply with quote

The above query could be split into 2 simple SQL. I It would help to do existence check first. If it exists, then do UPDATE on TABLE_A

SELECT DISTINCT TAB_B_COL1
INTO: WS-TAB-B-COL1
FROM TABLE_B
WHERE TAB_B_COL2='XYZ'

EVALUATE SQLCODE
WHEN 0

UPDATE TABLE_A
SET TAB_A_COL1 =: WS-TAB-B-COL1
WHERE TAB_A_COL2='ABC'

WHEN 100

....
...

WHEN OTHER

....

END-EVALUATE.


One more recommendation could be for first SQL in my response.

If you think that you are going to get only one value (but it contains duplicate rows) for column TAB_B_COL1 from TABLE_B for a given condition, you can use FETCH FIRST ROW ONLY instead of using DISTINCT. It would help to avoid internal SORT for DISTINCT.


SELECT TAB_B_COL1
INTO :WS-TAB-B-COL1
FROM TABLE_B
WHERE TAB_B_COL2='XYZ'
FETCH FIRST ROW ONLY

Please let us know your thoughts

Thanks
Senthil
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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