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
 

 

Update option in join

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

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Tue Jul 07, 2009 2:53 pm    Post subject: Update option in join
Reply with quote

Hi all,

Can we use update in inner join??

Actually I want to set the number field of table A to zero.
The key value of the table A and table B should be equal and also name field of the table B should be 'C'

Please tell me whether it is possible and also let me know whether any more information is required.

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

dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jul 07, 2009 3:11 pm    Post subject:
Reply with quote

joins tend to generate a read-only results.

why not use a subquery?

if that is too slow, you might think about unload, modify qsam, load.
Back to top
View user's profile Send private message
banand

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Tue Jul 07, 2009 3:50 pm    Post subject:
Reply with quote

Hi Actually we are given a query to optimize and make it cost efficient

that query contains exists option... can you tell us any other option to optimize the query?

plaese find the below query that was given to us

EXEC SQL UPDATE ORDER01 SET PVTTLCL = 0
WHERE EXISTS
(SELECT FFNDCD FROM PART03
WHERE FFNDFMLY = :PART03.FFNDFMLY
AND PFNDCD = FFNDCD)
END-EXEC

FIELD NAME TABLE NAME

PVTTLCL ORDER01
FFNDCD PART03
FFNDFMLY PART03
PFNDCD ORDER01

Thanks,
Chaitanya.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jul 07, 2009 8:05 pm    Post subject:
Reply with quote

Hello,

Quote:
Can we use update in inner join??
Quote:
Hi Actually we are given a query to optimize and make it cost efficient
that query contains exists option... can you tell us any other option to optimize the query?

Which is it? These don't appear to be related. . . icon_confused.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jul 07, 2009 8:25 pm    Post subject:
Reply with quote

Use EXPLAIN to start with.

number of rows in both tables;
are all columns in the subquery (for both tables) in an index?

you could look at the other threads in this forum about query optimization.

db2 for strobe.
bmc query analyzer

list goes on, but you are the only one who knows about your environment.
we are wasting our time guessing.

you have to analyze the tables to determine what is going on.
talk to coworkers, your dba's.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jul 09, 2009 7:54 pm    Post subject:
Reply with quote

Remove the correlation

Code:


UPDATE ORDER01 SET PVTTLCL = 0
WHERE PFNDCD IN
( SELECT FFNDCD FROM PART03
WHERE FFNDFMLY = :PART03.FFNDFMLY )


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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts DFSORT - Overriding default option TU... Steve Ironmonger DFSORT/ICETOOL 3 Tue Apr 04, 2017 3:54 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am


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