IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Examples to demonstrate the work of independent and correlat


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vibhor_thebest
Warnings : 1

New User


Joined: 18 Dec 2006
Posts: 3
Location: noida

PostPosted: Wed Dec 27, 2006 7:57 pm
Reply with quote

can anyone plz provide examples to demonstrate the work of independent and correlated sub queries.

Vibhor Jain
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Dec 27, 2006 10:10 pm
Reply with quote

Independent subquery

Code:

UPDATE DB2.TABLE1        T1
   SET COL1 = 1213
 WHERE T1.COL2 IN
       (SELECT T2.COL3
          FROM DB2.TABLE2     T2
         WHERE T2.COL4 < 4000
           AND
           AND
        )


In an independent subquery, the innermost subquery is executed only once and the result table is unchanged for the duration of the SQL, and is completely independent of and values in TABLE1. PROs to this: the Subquery in ony executed once. CONs: the result table could be very large and must be searched for every row of TABLE1.

Correlated subquery

Code:

UPDATE DB2.TABLE1        T1
   SET COL1 = 1213
 WHERE T1.COL2 IN
       (SELECT T2.COL3
          FROM DB2.TABLE2     T2
         WHERE T2.INDX1 = T1.INDX1
           AND T2.COL4 < T1.COL1
           AND
        )


In a Correlated subquery, the innermost subquery is executed for each row of TABLE1 because ?T2.INDX1 and T2.COL4?in TABLE2 correlates to ?T1.INDX1 and T1.COL1? of TABLE1. PROs to this: The subquery can be fast and produces only values needed to satisfy the ?IN?. CONs: The subquery must be executed for each row. Make sure your subquery in coded as narrowly as possible and use index keys. If the subquery does not use index keys, it will have to scan the entire TABLE2 for each row of TABLE1.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts isfline didnt work in rexx at z/OS ve... CLIST & REXX 7
No new posts Can you give me examples of programs ... CLIST & REXX 22
No new posts Negative value - packed field - Natur... Java & MQSeries 0
No new posts Any examples of calling FTP from REXX? CLIST & REXX 1
No new posts TWS - ETT File triggering does not wo... IBM Tools 4
Search our Forums:

Back to Top