Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Unanswered: Long Running SQL in DB2 Database - Attached Access Plan

    HI,

    I have a SQL which is running for an hour which i am trying to tune.but i am not able to find which part of the sql is taking more time.Any help is Appreciate.Please find attached access plan (VISUAL EXPLAIN) for the same.


    select
    A.COMPANY,
    C.DEPTID
    FROM PS_LEAVE_ACCRUAL A,
    PS_PERSON_NAME B,
    PS_PRIMARY_JOBS PJ,
    PS_PER_ORG_ASGN E,
    PS_JOB C,
    PS_LEAVE_PLAN D
    WHERE A.EMPLID = B.EMPLID
    AND A.EMPL_RCD = C.EMPL_RCD
    AND C.EMPLID = A.EMPLID
    AND A.COMPANY = C.COMPANY
    AND A.ACCRUAL_PROC_DT =
    (SELECT MAX(AX.ACCRUAL_PROC_DT)
    FROM PS_LEAVE_ACCRUAL AX
    WHERE AX.EMPLID = A.EMPLID
    AND AX.EMPL_RCD = A.EMPL_RCD
    AND AX.COMPANY = A.COMPANY
    AND AX.PLAN_TYPE = A.PLAN_TYPE)
    AND E.EMPLID = A.EMPLID
    AND E.EMPL_RCD = C.EMPL_RCD
    AND PJ.EMPLID = A.EMPLID
    AND PJ.EMPL_RCD = C.EMPL_RCD
    AND PJ.EMPL_RCD = E.EMPL_RCD
    AND PJ.PRIMARY_JOB_APP = 'BN'
    AND PJ.PRIMARY_JOB_IND = 'Y'
    AND PJ.EFFDT = (SELECT MAX(PJ1.EFFDT)
    FROM PS_PRIMARY_JOBS PJ1
    WHERE PJ1.EMPLID = PJ.EMPLID
    AND PJ1.EMPL_RCD = PJ.EMPL_RCD
    AND PJ1.PRIMARY_JOB_APP = 'BN'
    AND PJ1.EFFDT <= A.ACCRUAL_PROC_DT)
    AND C.EMPLID = A.EMPLID
    AND C.EFFDT =
    (SELECT MAX(EFFDT)
    FROM PS_JOB
    WHERE EMPLID = C.EMPLID
    AND EMPL_RCD = C.EMPL_RCD
    AND EFFDT <= A.ACCRUAL_PROC_DT)
    AND C.EFFSEQ =
    (SELECT MAX(EFFSEQ)
    FROM PS_JOB
    WHERE EMPLID = C.EMPLID
    AND EMPL_RCD = C.EMPL_RCD
    AND EFFDT = C.EFFDT)
    AND ((C.EMPL_STATUS in ('A','L','P','U'))
    OR ((C.EMPL_STATUS in ('R','T','D','Q'))
    AND (A.Hrs_Carryover + A.Hrs_earned_ytd - A.Hrs_taken_ytd - A.Hrs_taken_unproc +
    A.Hrs_adjust_unproc + A.Hrs_adjust_ytd - A.Hrs_sold_ytd - A.Hrs_sold_unproc +
    A.Hrs_bought_unproc + A.Hrs_bought_ytd <> 0)))
    AND D.EMPLID = A.EMPLID
    AND D.EMPL_RCD = A.EMPL_RCD
    AND D.PLAN_TYPE = A.PLAN_TYPE
    AND D.EFFDT =
    (SELECT MAX(DD.EFFDT)
    FROM PS_LEAVE_PLAN DD
    WHERE DD.EMPLID = D.EMPLID
    AND DD.EMPL_RCD = D.EMPL_RCD
    AND DD.PLAN_TYPE = D.PLAN_TYPE
    AND DD.BENEFIT_NBR = D.BENEFIT_NBR
    AND DD.EFFDT <= A.ACCRUAL_PROC_DT)
    ORDER BY A.COMPANY,
    C.DEPTID,
    A.EMPLID,
    A.PLAN_TYPE
    Attached Thumbnails Attached Thumbnails Tune.JPG  

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though you selected only two columns A.COMPANY, C.DEPTID,
    why did you used ORDER BY A.COMPANY, C.DEPTID, A.EMPLID, A.PLAN_TYPE?

  3. #3
    Join Date
    Jul 2012
    Posts
    4
    I am selecting couple of more columns in the select class.I have just give two columns as sample..

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It's no good idea to omit some selected columns.

    Because, your sample select list didn't include columns of B, PJ, E, D.
    If it's the case, those tables(B, PJ, E, D) could be moved to EXISTS(or IN) predicates in WHERE clause.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are your DB2 version/release/fixpack and platform OS?

  6. #6
    Join Date
    Jul 2012
    Posts
    4
    This is my Full SQL including all the fields.MY DB2 Version is DB2 v9.5.200.315

    SELECT
    A.COMPANY ,
    C.DEPTID ,
    A.EMPLID,
    A.EMPL_RCD,
    B.NAME,
    A.PLAN_TYPE,
    D.BENEFIT_PLAN,
    C.HOURLY_RT,
    C.BUSINESS_UNIT,
    A.ACCRUAL_PROC_DT,
    A.HRS_CARRYOVER,
    A.HRS_EARNED_YTD,
    A.HRS_TAKEN_YTD,
    A.HRS_TAKEN_UNPROC,
    A.HRS_ADJUST_UNPROC,
    A.HRS_BOUGHT_UNPROC,
    A.HRS_SOLD_UNPROC,
    A.HRS_ADJUST_YTD,
    A.HRS_BOUGHT_YTD,
    A.HRS_SOLD_YTD
    FROM PS_LEAVE_ACCRUAL A,
    PS_PERSON_NAME B,
    PS_PRIMARY_JOBS PJ,
    PS_PER_ORG_ASGN E,
    PS_JOB C,
    PS_LEAVE_PLAN D
    WHERE A.EMPLID = B.EMPLID
    AND A.EMPL_RCD = C.EMPL_RCD
    AND C.EMPLID = A.EMPLID
    AND A.COMPANY = C.COMPANY
    AND A.ACCRUAL_PROC_DT =
    (SELECT MAX(AX.ACCRUAL_PROC_DT)
    FROM PS_LEAVE_ACCRUAL AX
    WHERE AX.EMPLID = A.EMPLID
    AND AX.EMPL_RCD = A.EMPL_RCD
    AND AX.COMPANY = A.COMPANY
    AND AX.PLAN_TYPE = A.PLAN_TYPE)
    AND E.EMPLID = A.EMPLID
    AND E.EMPL_RCD = C.EMPL_RCD
    AND PJ.EMPLID = A.EMPLID
    AND PJ.EMPL_RCD = C.EMPL_RCD
    AND PJ.EMPL_RCD = E.EMPL_RCD
    AND PJ.PRIMARY_JOB_APP = 'BN'
    AND PJ.PRIMARY_JOB_IND = 'Y'
    AND PJ.EFFDT = (SELECT MAX(PJ1.EFFDT)
    FROM PS_PRIMARY_JOBS PJ1
    WHERE PJ1.EMPLID = PJ.EMPLID
    AND PJ1.EMPL_RCD = PJ.EMPL_RCD
    AND PJ1.PRIMARY_JOB_APP = 'BN'
    AND PJ1.EFFDT <= A.ACCRUAL_PROC_DT)
    AND C.EMPLID = A.EMPLID
    AND C.EFFDT =
    (SELECT MAX(EFFDT)
    FROM PS_JOB
    WHERE EMPLID = C.EMPLID
    AND EMPL_RCD = C.EMPL_RCD
    AND EFFDT <= A.ACCRUAL_PROC_DT)
    AND C.EFFSEQ =
    (SELECT MAX(EFFSEQ)
    FROM PS_JOB
    WHERE EMPLID = C.EMPLID
    AND EMPL_RCD = C.EMPL_RCD
    AND EFFDT = C.EFFDT)
    AND ((C.EMPL_STATUS in ('A','L','P','U'))
    OR ((C.EMPL_STATUS in ('R','T','D','Q'))
    AND (A.Hrs_Carryover + A.Hrs_earned_ytd - A.Hrs_taken_ytd - A.Hrs_taken_unproc +
    A.Hrs_adjust_unproc + A.Hrs_adjust_ytd - A.Hrs_sold_ytd - A.Hrs_sold_unproc +
    A.Hrs_bought_unproc + A.Hrs_bought_ytd <> 0)))
    AND D.EMPLID = A.EMPLID
    AND D.EMPL_RCD = A.EMPL_RCD
    AND D.PLAN_TYPE = A.PLAN_TYPE
    AND D.EFFDT =
    (SELECT MAX(DD.EFFDT)
    FROM PS_LEAVE_PLAN DD
    WHERE DD.EMPLID = D.EMPLID
    AND DD.EMPL_RCD = D.EMPL_RCD
    AND DD.PLAN_TYPE = D.PLAN_TYPE
    AND DD.BENEFIT_NBR = D.BENEFIT_NBR
    AND DD.EFFDT <= A.ACCRUAL_PROC_DT)
    ORDER BY A.COMPANY,
    C.DEPTID,
    A.EMPLID,
    A.PLAN_TYPE

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a formatted of your partial code.
    Isn't " AND PJ.PRIMARY_JOB_IND = 'Y' " neccesary in a subquery?

    Code:
       AND PJ.EMPLID          = A.EMPLID
       AND PJ.EMPL_RCD        = C.EMPL_RCD
       AND PJ.EMPL_RCD        = E.EMPL_RCD
       AND PJ.PRIMARY_JOB_APP = 'BN'
       AND PJ.PRIMARY_JOB_IND = 'Y'
       AND PJ.EFFDT
           = (SELECT MAX(PJ1.EFFDT)
               FROM  PS_PRIMARY_JOBS PJ1
               WHERE PJ1.EMPLID          =  PJ.EMPLID
                 AND PJ1.EMPL_RCD        =  PJ.EMPL_RCD
                 AND PJ1.PRIMARY_JOB_APP =  'BN'
                 AND PJ1.EFFDT           <= A.ACCRUAL_PROC_DT
             )

  8. #8
    Join Date
    Jul 2012
    Posts
    4
    I think i don't have to consider PRIMARY_JOB_IND = 'Y' in Subquery as per business logic.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    That means, I think, if there was a larger EFFDT with EFFDT <= A.ACCRUAL_PROC_DT
    and PRIMARY_JOB_IND <> 'Y' than largest EFFDT with PRIMARY_JOB_IND = 'Y',
    within same EMPLID and EMPL_RCD,
    the combinaion of (EMPLID, EMPL_RCD) would be excluded from result regardless any other conditions(including for other tables).

    Is that true?

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a trial example.

    If the result of the query was different from your desired result,
    please publish test data and expected result from the data and result of the sample query.

    Code:
    SELECT
           A.COMPANY
         , C.DEPTID
         , A.EMPLID
         , A.EMPL_RCD
         , B.NAME
         , A.PLAN_TYPE
         , D.BENEFIT_PLAN
         , C.HOURLY_RT
         , C.BUSINESS_UNIT
         , A.ACCRUAL_PROC_DT
         , A.HRS_CARRYOVER
         , A.HRS_EARNED_YTD
         , A.HRS_TAKEN_YTD
         , A.HRS_TAKEN_UNPROC
         , A.HRS_ADJUST_UNPROC
         , A.HRS_BOUGHT_UNPROC
         , A.HRS_SOLD_UNPROC
         , A.HRS_ADJUST_YTD
         , A.HRS_BOUGHT_YTD
         , A.HRS_SOLD_YTD
     FROM  (SELECT AX.*
                 , ROW_NUMBER()
                      OVER( PARITION BY EMPLID
                                      , EMPL_RCD
                                      , COMPANY
                                      , PLAN_TYPE
                               ORDER BY ACCRUAL_PROC_DT DESC
                          ) AS row_num
             FROM  PS_LEAVE_ACCRUAL AX
           ) A
     INNER JOIN
           PS_PERSON_NAME   B
      ON   B.EMPLID   = A.EMPLID
     INNER JOIN
           LATERAL
           (SELECT C.*
                 , ROW_NUMBER()
                      OVER( ORDER BY EFFDT  DESC
                                   , EFFSEQ DESC
                          ) AS row_num
             FROM  PS_JOB C
             WHERE C.EMPLID   =  A.EMPLID
               AND C.EMPL_RCD =  A.EMPL_RCD
               AND C.COMPANY  =  A.COMPANY
               AND C.EFFDT    <= A.ACCRUAL_PROC_DT
           ) C
      ON   C.row_num = 1
       AND
      (    C.EMPL_STATUS in ('A','L','P','U')
       OR
           C.EMPL_STATUS in ('R','T','D','Q')
       AND (  A.Hrs_Carryover     + A.Hrs_earned_ytd - A.Hrs_taken_ytd - A.Hrs_taken_unproc
            + A.Hrs_adjust_unproc + A.Hrs_adjust_ytd - A.Hrs_sold_ytd  - A.Hrs_sold_unproc
            + A.Hrs_bought_unproc + A.Hrs_bought_ytd <> 0
           )
      )
     INNER JOIN
           LATERAL
           (SELECT PJ.*
                 , ROW_NUMBER()
                      OVER( ORDER BY EFFDT DESC
                          ) AS row_num
             FROM  PS_PRIMARY_JOBS PJ
             WHERE PJ.EMPLID          =  A.EMPLID
               AND PJ.EMPL_RCD        =  A.EMPL_RCD
               AND PJ.PRIMARY_JOB_APP =  'BN'
               AND PJ.EFFDT           <= A.ACCRUAL_PROC_DT
           ) PJ
      ON   PJ.row_num = 1
       AND PJ.PRIMARY_JOB_IND = 'Y'
     INNER JOIN
           LATERAL
           (SELECT D.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY BENEFIT_NBR
                                ORDER BY EFFDT DESC
                          ) AS row_num
             FROM  PS_LEAVE_PLAN    D
             WHERE D.EMPLID    =  A.EMPLID
               AND D.EMPL_RCD  =  A.EMPL_RCD
               AND D.PLAN_TYPE =  A.PLAN_TYPE
               AND D.EFFDT     <= A.ACCRUAL_PROC_DT
           ) D
      ON   D.row_num = 1
     WHERE A.row_num = 1
       AND EXISTS
           (SELECT 0
             FROM  PS_PER_ORG_ASGN  E
             WHERE E.EMPLID    = A.EMPLID
               AND E.EMPL_RCD  = A.EMPL_RCD
           )
     ORDER BY
           A.COMPANY
         , C.DEPTID
         , A.EMPLID
         , A.PLAN_TYPE
    Last edited by tonkuma; 07-30-12 at 14:56. Reason: Remove some PARTITION BY columns.

  11. #11
    Join Date
    Nov 2011
    Posts
    334
    What is the explain tool you used?
    I think the access plan of DB2 has no operation called QBx and wfscan。

    And just showing a graph is not enough for tuning a query ,
    plz give the full plan ( post the output of db2exfmt ) 。

    at first glance for the graph ,Maybe there are too much nljoins in the plan。

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    bhaskar.target,

    Did you tried my query?

    It might be not so difficult.
    Just copy and paste the query on your client tool and execute it(it might be not neccesary to understand completely what was the query doing...),
    and report the results(got error message(s), got incorrect output, performance was not improved, performance was improved but not enough, so on...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •