Re: Correlated SubQueries in MVS DB2

[Peter Clark <PGCLARK@VAC-ACC.GC.CA>]

On second thought, Rod Wright appears to have the answer.

>>> Peter Clark <PGCLARK@VAC-ACC.GC.CA> 2001/05/31 1:12:13 pm >>>
This may be because of DB2 syntax (I am only familiar with Oracle) but why the 
PSOFTCNV. prefix in your subquery when it is not present in your main query? Is 
there is the potential that you are accessing two different Location Tables (or 
accessing the same table via two different schemas) and confusing the heck out 
of DB2? Just guessing.

>>> Chris Campbell <campbell.chris@EXCITE.COM> 2001/05/31 11:54:25 am >>>
Hello all,

We are having some performance problems with a SQR on DB2.  The program has
run as long as 27 hours before we finally cancel it.  The problem seems to
relate to  view materialization as a result of a correlated sub query.  The
SQL is:

select J.emplid
    FROM PS_JOB J, PS_LOCATION_TBL L
    WHERE J.LOCATION = L.LOCATION
          AND J.COMPANY = ?
          AND J.Empl_Status = 'A'
          AND L.STATE = 'MA'
          AND L.EFFDT = (SELECT MAX(L1.EFFDT)
    FROM PSOFTCNV.PS_LOCATION_TBL L1
    WHERE L1.LOCATION = L.LOCATION
          AND L1.EFFDT <= $Proc-dt )

The PS_LOCATION_TBL has about 7500 rows, and the PS_JOB table has about
375000 rows.  I've read some documentation, that suggests that DB2 does not
process correlated subqueries very efficiently.  Has anyone else experienced
this, and if so, I'd be interested in hearing some of your methods to
resolve it.

Thanks in advance, Chris.





_______________________________________________________
Send a cool gift with your E-Card
http://www.bluemountain.com/giftcenter/