Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    26

    Unanswered: Help in fine tunning the query

    Hi,
    My application uses the below query
    SELECT TXN_DT, INTEGER (CLNT_SRF_NO) AS CLNT_SRF_NO,(TXN_AMT * EXCH_RT) AS TXN_AMT FROM PQ5.SRVC_PLATF_CLNT_TXN_DTL
    UNION ALL
    SELECT TXN_DT, INTEGER (CLNT_SRF_NO) AS CLNT_SRF_NO,(TXN_AMT * EXCH_RT) AS TXN_AMT FROM PQ5.ABM_DEP_TXN_DTL
    UNION ALL
    SELECT TXN_DT, INTEGER (CLNT_SRF_NO) AS CLNT_SRF_NO , (TXN_AMT * EXCH_RT) AS TXN_AMT FROM PQ5.HSCP_DEP_TXN_DTL

    It takes very much time, please provide soem input to fine tune the query.

    Thanks
    Surjya

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Since there is no WHERE clause in the queries, it is having to read and return every row in each table. How many rows are there in each table?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    As Marcus mentioned, no where clause not much to improve. The only thing that may help is to create an index on the four columns you are after. Depending on the version you are running you may want to create and index on expressions, meaning the column definition would be:
    TXN_DT, INTEGER (CLNT_SRF_NO) AS CLNT_SRF_NO , (TXN_AMT * EXCH_RT) AS TXN_AMT
    Dave

Posting Permissions

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