Re: Correlated SubQueries in MVS DB2

[Victor Loghin <vgl_ca@HOTMAIL.COM>]

Chris,

In my experience DB2 performs well with correlated subselects if it can take
advantage of an index.

What version of PeopleSoft are you on? If you are on version 7.5, the
correlated subselect does not specify the first column (SETID) of the
primary key (SETID, LOCATION and EFFDT). DB2 will not use the index and will
perform a tablespace scan.

Also, it will select all job rows that were ever active and not just those
active on $Proc-dt. If this is what is required then you should use
distinct.

Are you using multiple setids? If you are then you will need to use the
business unit to get the setid. The following query will get active
employees on $Proc-dt located in MA when using multiple setids. If you use a
single setid then hard code it and eliminate the join to PS_SET_CNTRL_REC.

SELECT J.EMPLID
FROM   PS_JOB            J
,      PS_SET_CNTRL_REC  S
,      PS_LOCATION_TBL   L
WHERE  J.COMPANY           = 'CCB'
AND    J.EMPL_STATUS       = 'A'
AND    J.EFFDT             =
        ( SELECT MAX(EFFDT)
          FROM   PS_JOB
          WHERE  EMPLID    = J.EMPLID
          AND    EMPL_RCD# = J.EMPL_RCD#
          AND    EFFDT    <= $Proc-dt )
AND    J.EFFSEQ            =
        ( SELECT MAX(EFFSEQ)
          FROM   PS_JOB
          WHERE  EMPLID    = J.EMPLID
          AND    EMPL_RCD# = J.EMPL_RCD#
          AND    EFFDT     = J.EFFDT )
AND    S.SETCNTRLVALUE     = J.BUSINESS_UNIT
AND    S.RECNAME           = 'LOCATION_TBL'
AND    L.SETID             = S.SETID
AND    L.LOCATION          = J.LOCATION
AND    L.EFFDT             =
        ( SELECT MAX(EFFDT)
          FROM   PS_LOCATION_TBL
          WHERE  SETID     = L.SETID
          AND    LOCATION  = L.LOCATION
          AND    EFFDT    <= $Proc-dt )
AND    L.STATE             = 'MA'

HTH,
Victor Loghin
VGL Consulting Inc.
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.