Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Arrow Unanswered: Improving Query performance

    Hi
    I have a Query to Unload Data From Table which contains JOIN statements on 7 Tables. I guess its getting too heavy for processing due to which my Mainframe Job Times Out and Abends. Can anyone Please suggest me on how to tune my query for obtaining desired results. Here is a snapshot of my Query:

    SELECT
    A.CTLENTYID,
    A.ACKVAL,
    A.VCKVAL,
    A.ICKVAL,
    A.INVCDISTLINENBR,
    A.PMTNBR,
    A.EVENTCD,
    A.SUBEVENTCD,
    A.ACTVYCD,
    A.JEAMT,
    A.ACCTEFFDT,
    B.DESCRIPTION,
    C.VNDRLONGNM,
    D.PCKVAL,
    D.POPOKVAL,
    D.INVCGRPID,
    D.INVCDT,
    D.POSRCNBR,
    E.INVCMTLDESC,
    E.MCKVAL,
    F.CHKNBR,
    F.CHKDT,
    F.CNCLDT,
    SUBSTR(G.DATACOL,70,6)
    FROM CWIGNP.SCE3E00 F INNER JOIN CWIGNP.SCE5100 A
    ON F.CTLENTYID = A.CTLENTYID
    AND F.VCKVAL = A.VCKVAL
    AND F.ICKVAL = A.ICKVAL
    LEFT OUTER JOIN CWIGNP.SCAPDB01 G
    ON F.CHKNBR = SUBSTR(G.KEYCOL,32,11)
    INNER JOIN CWIGNP.SCCA053 B
    ON SUBSTR(A.ACKVAL,6,6) = B.ACCOUNT
    INNER JOIN CWIGNP.SCE0500 C
    ON A.CTLENTYID = C.CTLENTYID
    AND A.VCKVAL = C.VCKVAL
    INNER JOIN CWIGNP.SCE3A00 D
    ON A.CTLENTYID = D.CTLENTYID
    AND A.VCKVAL = D.VCKVAL
    AND A.ICKVAL = D.ICKVAL
    INNER JOIN CWIGNP.SCE3B00 E
    ON D.CTLENTYID = E.CTLENTYID
    AND D.VCKVAL = E.VCKVAL
    AND D.ICKVAL = E.ICKVAL
    WHERE A.ACCTEFFDT >= '2004-04-30'

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Always post your DB2 version and fixpack, and operating-system name.

    It is not much use to post only the query without any context, or knowledge of indexes and cardinalities involved.

    Get the access plan for your query.

    Study that access plan to determine where the time is going (look for table-scans and sorts on tables with large numbers of rows).

    Use the index advisor to suggest appropriate indexes to remove expensive tablescans, or expensive sorts.
    If new indexes are recommended and you have space for them then try them.

    Check if statistics are up to date for all tables/indexes involved in the plan. RUNSTATS where appropriate.

    Check if you need column-group statistics for some of the joins (where the access-plan cardinalities are significantly out of step with reality).

  3. #3
    Join Date
    Dec 2010
    Posts
    123
    hi db2mon,
    i also follow same steps to improve the performance of a query, but i did understand how to improve query performance using db2expln, can you provide me any simple link or explanation...

  4. #4
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Give DB2 version and fixpack, and operating-system name.

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    plz try this

    if you do no have explain table,
    you need create the explain table first.
    db2 -tvf <db2_install_dir>/misc/EXPLAIN.DDL


    then collect the plan:

    db2 set current explain mode explain
    execute your query.
    db2 set current explain mode no

    db2exfmt -d <dbname> -1 > 1.exfmt
    and post the 1.exfmt here....

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    not good:

    ON SUBSTR(A.ACKVAL,6,6) = B.ACCOUNT

    better

    ON B.ACCOUNT = SUBSTR(A.ACKVAL,6,6)

    Perform explain on this query, you'll better understand how to improve the performance.

    Lenny
    Last edited by Lenny77; 04-26-12 at 18:35.

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    what the difference between
    SUBSTR(A.ACKVAL,6,6) = B.ACCOUNT
    and
    B.ACCOUNT = SUBSTR(A.ACKVAL,6,6)

    why do you think
    B.ACCOUNT = SUBSTR(A.ACKVAL,6,6) will be better?

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    There's not much room for performance improvement, at least not by just rewriting the query.
    Since I have no idea of the table sizes and the presence of indexes, I can't be very specific, but the two conditions involving SUBSTR are most likely the main cause of the performance problems.
    If you are on DB2 9 or 10 for z/OS, I would suggest you (let) add the following two indexes on your tables:
    Code:
    CREATE INDEX gsub ON CWIGNP.SCAPDB01(SUBSTR(KEYCOL,32,11))
    and
    Code:
    CREATE INDEX asub ON CWIGNP.SCE5100(SUBSTR(ACKVAL,6,6))
    But first verify whether these two substrings are maybe stored additionally in other columns (and are already indexed).
    Other alternative would be to create a temp table, copy the relevant rows from A into it, create the mentioned index on it, then modify the query to use this table instead of A.
    But that won't take less time in total, I'm afraid.

    So if you really need the output of this query, you'll either have to increase the time limit of your application, or split up the result set in yearly or monthly pieces or so (by setting the final condition to something like
    Code:
    WHERE A.ACCTEFFDT between '2004-04-30' and '2004-12-31'
    and then between 2005-01-01 and 2005-12-31 and so on.
    Last edited by Peter.Vanroose; 04-28-12 at 19:46.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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