Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Examples to demonstrate the work of independent and correlat

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Examples to demonstrate the work of independent and correlat
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    Post subject: Re: Examples to demonstrate the work of independent and corr
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    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 2 commands instream work. In ... descann Compuware & Other Tools 2 Tue May 16, 2017 3:31 pm
No new posts INDEPENDENT CICS TS 4.1 MRO REGION UP... Kyle Carroll CICS 0 Wed Dec 14, 2016 6:55 pm
No new posts What is the maximum number of sort wo... Pravina M SYNCSORT 2 Mon Mar 14, 2016 4:51 pm
No new posts JCL -> Rexx -> XMITIP : Doesnt ... enrico-sorichetti CLIST & REXX 2 Thu Aug 06, 2015 11:09 pm
No new posts Select Variable=string Rexx does not ... Csongor CLIST & REXX 11 Thu May 07, 2015 9:33 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us