Results 1 to 11 of 11
  1. #1
    Join Date
    May 2011
    Posts
    5

    Unanswered: Query performance needs to be increased

    Hi All,

    I am new to DB2, I am running a query in DB2 on mainframes under production environment. But it is running for long time approx 2+ hrs. I am using 3 tables in the query and using simple join on the tables.

    Can some one help me to show the methods to reduce the running time for query...

    Thanks in advance,
    Sravni.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    One of the most effective methods is to add a WHERE clause to your query.

  3. #3
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    Can you share your SQL here to see how the tables are joined? Check your indexes defined on the columns used in join/where.

  4. #4
    Join Date
    May 2011
    Posts
    5
    Quote Originally Posted by schintala View Post
    Can you share your SQL here to see how the tables are joined? Check your indexes defined on the columns used in join/where.
    Sure. Here is my sql:
    SELECT
    CAST(DIGITS(AU.CUS_ID_NO) AS CHAR(12))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))
    ,AU.BUS_NM
    ,CAST('"' AS CHAR(1))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))
    ,PA.CUS_AD_LN_ONE_AD
    ,CAST('"' AS CHAR(1))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))
    ,PA.CUS_AD_MEM_LN_AD
    ,CAST('"' AS CHAR(1))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))
    ,PA.CUS_AD_CTY_NM
    ,CAST('"' AS CHAR(1))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))
    ,PA.CUS_AD_STE_PVN_CD
    ,CAST('"' AS CHAR(1))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))
    ,PA.CUS_AD_ZIP_PTL_CD
    ,CAST('"' AS CHAR(1))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))
    ,CAST(DIGITS(PA.CUS_PHN_ARE_CD) AS CHAR(4))
    ,CAST('"' AS CHAR(1))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))
    ,CAST(DIGITS(PA.CUS_PHN_NO) AS CHAR(8))
    ,CAST('"' AS CHAR(1))
    ,CAST(',' AS CHAR(1))
    ,CAST(AU.CLS AS CHAR(3))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))
    ,AU.PRY_SRS_CNT_PRS
    ,CAST('"' AS CHAR(1))
    FROM DB2NAD8.NADXTNM NM,
    DB2NAD8.NADXTPA PA,
    DB2NAD8.NADXTAU AU
    WHERE NM.AD_ID_NO = AU.AD_ID_NO
    AND NM.CUS_ID_NO = AU.CUS_ID_NO
    AND AU.AD_ID_NO = PA.AD_ID_NO
    AND (PA.CUS_PHN_AD_CHG_DT >= CURRENT DATE - 7 DAY
    OR NM.CUS_NM_CHG_DT >= CURRENT DATE - 7 DAY )

    WITH UR;

    Indexes are also defined for the tables.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What indexs did you defined?

    I thought the following indexes might be worth to try.

    DB2NAD8.NADXTNM NM
    (CUS_NM_CHG_DT DESC , AD_ID_NO , CUS_ID_NO)

    DB2NAD8.NADXTPA PA
    (CUS_PHN_AD_CHG_DT DESC , AD_ID_NO)

    DB2NAD8.NADXTAU AU
    (AD_ID_NO , AU.CUS_ID_NO)

  6. #6
    Join Date
    May 2011
    Posts
    5
    Quote Originally Posted by tonkuma View Post
    What indexs did you defined?

    I thought the following indexes might be worth to try.

    DB2NAD8.NADXTNM NM
    (CUS_NM_CHG_DT DESC , AD_ID_NO , CUS_ID_NO)

    DB2NAD8.NADXTPA PA
    (CUS_PHN_AD_CHG_DT DESC , AD_ID_NO)

    DB2NAD8.NADXTAU AU
    (AD_ID_NO , AU.CUS_ID_NO)
    Thanks alot. I l try with this index and let you know.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm not sure that DB2 on mainframe implies addtional predicates.
    I mean the following additional predicate.

    If not, it may be worth.
    Code:
     FROM  DB2NAD8.NADXTNM NM
         , DB2NAD8.NADXTPA PA
         , DB2NAD8.NADXTAU AU
     WHERE AU.AD_ID_NO  = NM.AD_ID_NO
       AND AU.CUS_ID_NO = NM.CUS_ID_NO
       AND PA.AD_ID_NO  = NM.AD_ID_NO  -- Add predicate.
       AND AU.AD_ID_NO  = PA.AD_ID_NO
       AND
       (   PA.CUS_PHN_AD_CHG_DT >= CURRENT DATE - 7 DAY
        OR NM.CUS_NM_CHG_DT     >= CURRENT DATE - 7 DAY
       )
    Last edited by tonkuma; 05-24-11 at 06:35. Reason: Remove a redundant predicate.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why did you separated columns of constants values, like ...
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))
    or
    ,CAST('"' AS CHAR(1))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))

    Why not like...
    ,CAST(',"' AS CHAR(2))
    or
    ,CAST('","' AS CHAR(3))

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If (AD_ID_NO , CUS_ID_NO) on DB2NAD8.NADXTNM was unique,
    following rewritings might be worth to try.
    (Because, no column of DB2NAD8.NADXTNM was in SELECT list.)

    Example 1:
    Code:
     FROM
           DB2NAD8.NADXTPA PA
         , DB2NAD8.NADXTAU AU
     WHERE AU.AD_ID_NO  = PA.AD_ID_NO
       AND EXISTS
           (SELECT 0
             FROM  DB2NAD8.NADXTNM NM
             WHERE NM.AD_ID_NO  = AU.AD_ID_NO
               AND NM.CUS_ID_NO = AU.CUS_ID_NO
               AND
               (   PA.CUS_PHN_AD_CHG_DT >= CURRENT DATE - 7 DAY
                OR NM.CUS_NM_CHG_DT     >= CURRENT DATE - 7 DAY
               )
           )
    or

    Example 2:
    Code:
    /*
     The result of the proposed query
       may be slightly different from original query.
    
     The row with conditions:
            AU.AD_ID_NO          =  PA.AD_ID_NO
        AND PA.CUS_PHN_AD_CHG_DT >= CURRENT DATE - 7 DAY
        AND NOT EXISTS
            (    NM.AD_ID_NO  = AU.AD_ID_NO
             AND NM.CUS_ID_NO = AU.CUS_ID_NO
             -- Don't care value of NM.CUS_NM_CHG_DT --
            )
     Original query: Not included in result.
     Proposed query: Included in result.
    */
     FROM
           DB2NAD8.NADXTPA PA
         , DB2NAD8.NADXTAU AU
     WHERE AU.AD_ID_NO  = PA.AD_ID_NO
       AND
       (   PA.CUS_PHN_AD_CHG_DT >= CURRENT DATE - 7 DAY
        OR EXISTS
           (SELECT 0
             FROM  DB2NAD8.NADXTNM NM
             WHERE NM.AD_ID_NO      =  AU.AD_ID_NO
               AND NM.CUS_ID_NO     =  AU.CUS_ID_NO
               AND NM.CUS_NM_CHG_DT >= CURRENT DATE - 7 DAY
           )
       )
    with the index
    (same as before)
    DB2NAD8.NADXTNM NM
    (CUS_NM_CHG_DT DESC , AD_ID_NO , CUS_ID_NO)
    or
    DB2NAD8.NADXTNM NM
    (AD_ID_NO , CUS_ID_NO , CUS_NM_CHG_DT DESC)

  10. #10
    Join Date
    May 2011
    Posts
    5
    Thanks alot for all ur replies Tonkuma. Now it is running for 40 minutes around. Thanks for ur guidence.

  11. #11
    Join Date
    May 2011
    Posts
    5
    I am sending the file in CSV format. So there is address field which may contain comma in between that is why i am enclosing my fields in quotes. that is the reason for writing the query in this way.
    ,CAST('"' AS CHAR(1))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))
    ,PA.CUS_AD_LN_ONE_AD
    ,CAST('"' AS CHAR(1))
    ,CAST(',' AS CHAR(1))
    ,CAST('"' AS CHAR(1))

Posting Permissions

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