Results 1 to 2 of 2

Thread: Query Tuning

  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: Query Tuning

    I have a query which needs to be tuned. Please have a look at the query

    select c1 as c1,
    c2 as c2,
    c3 as c3,
    c4 as c4,
    c5 as c5,
    c6 as c6,
    c7 as c7,
    c8 as c8,
    c9 as c9,
    c10 as c10,
    c11 as c11,
    c12 as c12,
    c13 as c13,
    c14 as c14,
    c15 as c15,
    c16 as c16,
    c17 as c17,
    c18 as c18,
    c19 as c19,
    c20 as c20,
    c21 as c21,
    c22 as c22,
    RSUM(c25 for c1) as c23
    from
    (select c1 as c1,
    c2 as c2,
    c3 as c3,
    c4 as c4,
    c5 as c5,
    c6 as c6,
    c7 as c7,
    c8 as c8,
    c9 as c9,
    c10 as c10,
    c11 as c11,
    c12 as c12,
    c13 as c13,
    c14 as c14,
    c15 as c15,
    c16 as c16,
    c17 as c17,
    c18 as c18,
    c19 as c19,
    c20 as c20,
    c21 as c21,
    XCOUNT(c24) as c22,
    CASE WHEN (c21 = c21) THEN (1) ELSE null END as c25
    from
    (select c1 as c1,
    c2 as c2,
    c3 as c3,
    c4 as c4,
    c5 as c5,
    c6 as c6,
    c7 as c7,
    c8 as c8,
    c9 as c9,
    c10 as c10,
    c11 as c11,
    c12 as c12,
    c13 as c13,
    c14 as c14,
    c15 as c15,
    c16 as c16,
    c17 as c17,
    c18 as c18,
    c19 as c19,
    c20 as c20,
    c21 as c21,
    RSUM(CASE WHEN (c21 = c21) THEN (1) ELSE null END) as c24
    from
    (select T1."LOCATION" as c1,
    T1."LEGAL_ENTITY_CODE" as c2,
    T1."SUB_AREA" as c3,
    T1."TRADE_DATE" as c4,
    T1."TRADER_BOOK" as c5,
    T1."TRADER_BOOK_NAME" as c6,
    T1."DELIVERY_DATE" as c7,
    T1."INS_KEY" as c8,
    T1."INS_FULL_TITLE" as c9,
    T1."CLOSE_PRICE" as c10,
    T1."INS_MARKET" as c11,
    T1."LI_BUY_VOL" as c12,
    T1."LI_AVG_B_PRI" as c13,
    T1."LI_BUY_TGC" / 1000000 as c14,
    T1."LI_SELL_VOL" as c15,
    T1."LI_AVG_S_PRI" as c16,
    T1."LI_SELL_TGC" / 1000000 as c17,
    T1."LI_RT" * 100 as c18,
    T1."LI_PL" as c19,
    T1."ST_STAT_CHECK1" as c20,
    'Detail' as c21
    from "DB"."MC624V" T1,
    "DB"."MC350T" T2
    where ((((T1."TRADE_DATE" = T2."CHK_DATE") and (T2."DATA_CREATOR" = 'MC624B')) and (T2."ST_REPORT1" = 'P')) and (T1."ST_STAT_CHECK1" = 'E'))
    order by c1 asc,c19 desc,c3 asc,c11 asc,c5 asc,c6 asc,c7 asc,c2 asc,c4 asc
    ) D3
    ) D1
    ) D2

    Both the tables have loads of data. Is there anyway that i can tune this query?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It would help if you supplied the table DDL and index DDL for all tables. Also, have you done an Explain on the query? I would include that also.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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