Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    5

    Post Unanswered: performace tuning

    Hi

    I am new to DB2..I have a challenging task to tune a query. It is taking 13 min. time. I have to tune this query. Please help me in this regard.
    Thanks in adv.

    SELECT
    stgc_ccr_cc_claim.source_system_code
    , ins_home_claim_drv.id
    , DECODE(stgc_ccr_cc_claim.retired, 0, 'N', 'Y')
    , stgc_ccr_cc_claim.claimnumber
    , stgc_ccr_cc_claim.sc_tempaccommodation
    , stgc_ccr_cc_claim.policyid
    , stgc_ccr_cc_claim.id
    , stgc_ccr_cc_claim.state
    , stgc_ccr_cc_claim.sc_claimdecision
    , stgc_ccr_cc_claim.sc_claimtype
    , stgc_ccr_cc_claim.sc_closedoutcome
    , stgc_ccr_cc_claim.catastropheid
    , stgc_ccr_cc_claim.strategy
    , stgc_ccr_cc_claim.losscause
    , CASE
    WHEN edw_home_claim.lodgement_date IS NOT NULL
    THEN CASE WHEN DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N') = 'N'
    THEN edw_home_claim.lodgement_date
    ELSE NULL
    END
    ELSE
    CASE
    WHEN (edw_home_claim.src_id IS NULL
    AND DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N') = 'N')
    THEN stgc_ccr_cc_claim.reporteddate
    WHEN (edw_home_claim.src_id IS NOT NULL
    AND DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N') = 'N')
    THEN stgc_ccr_cc_claim.updatetime
    ELSE NULL
    END
    END
    , stgc_ccr_cc_policylocation.addressid
    , stgc_ccr_cc_claim.createtime
    , stgc_ccr_cc_claim.updatetime
    , stgc_ccr_cc_claim.closedate
    , DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N')
    , stgc_ccr_cc_incident_latest_v.sc_totaloriginalvalu e
    , stgc_ccr_cc_claim.cdc_action
    , ins_home_claim_drv.cdc_timestamp
    , stgc_ccr_cc_claim.mds_stage_acquisition_sk

    FROM ins_home_claim_drv ins_home_claim_drv
    JOIN stgc_ccr_cc_claim stgc_ccr_cc_claim
    ON ins_home_claim_drv.id = stgc_ccr_cc_claim.id
    AND ins_home_claim_drv.cdc_timestamp = stgc_ccr_cc_claim.cdc_timestamp
    LEFT OUTER JOIN stgc_ccr_cc_policy stgc_ccr_cc_policy
    ON stgc_ccr_cc_claim.policyid = stgc_ccr_cc_policy.id
    AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cc_policy.dss_start_date AND stgc_ccr_cc_policy.dss_end_date
    LEFT OUTER JOIN stgc_ccr_cctl_sc_lineofbusiness stgc_ccr_cctl_sc_lineofbusiness
    ON stgc_ccr_cc_policy.sc_lineofbusiness = stgc_ccr_cctl_sc_lineofbusiness.id
    AND stgc_ccr_cctl_sc_lineofbusiness.dss_current_flag = 'Y'
    LEFT OUTER JOIN stgc_ccr_cctl_sc_yesno stgc_ccr_cctl_sc_yesno
    ON stgc_ccr_cc_claim.sc_incidentreport = stgc_ccr_cctl_sc_yesno.id
    AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cctl_sc_yesno.dss_start_date AND stgc_ccr_cctl_sc_yesno.dss_end_date
    LEFT OUTER JOIN stgc_ccr_cctl_claimstate stgc_ccr_cctl_claimstate
    ON stgc_ccr_cc_claim.state = stgc_ccr_cctl_claimstate.id
    AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cctl_claimstate.dss_start_date AND stgc_ccr_cctl_claimstate.dss_end_date
    LEFT OUTER JOIN stgc_ccr_cctl_losstype stgc_ccr_cctl_losstype
    ON stgc_ccr_cc_claim.losstype = stgc_ccr_cctl_losstype.id
    AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cctl_losstype.dss_start_date AND stgc_ccr_cctl_losstype.dss_end_date
    LEFT OUTER JOIN stgc_ccr_cc_policylocation stgc_ccr_cc_policylocation
    on stgc_ccr_cc_claim.sc_policylocationfk = stgc_ccr_cc_policylocation.id
    AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cc_policylocation.dss_start_date AND stgc_ccr_cc_policylocation.dss_end_date
    AND stgc_ccr_cc_policylocation.retired = 0
    LEFT OUTER JOIN edw_home_claim edw_home_claim
    ON stgc_ccr_cc_claim.source_system_code = edw_home_claim.source_system_code
    AND stgc_ccr_cc_claim.id = edw_home_claim.src_id
    AND edw_home_claim.dss_current_flag = 'Y'
    LEFT OUTER JOIN stgc_ccr_cc_incident_latest_v stgc_ccr_cc_incident_latest_v
    ON stgc_ccr_cc_claim.id = stgc_ccr_cc_incident_latest_v.claimid
    AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cc_incident_latest_v.dss_start_date AND stgc_ccr_cc_incident_latest_v.dss_end_date
    WHERE stgc_ccr_cctl_sc_lineofbusiness.typecode = 'PI'
    AND stgc_ccr_cctl_claimstate.typecode != 'draft'
    AND stgc_ccr_cctl_losstype.typecode = 'PR' ;

    Please help me ..!!!

    Rao

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

  3. #3
    Join Date
    Feb 2012
    Posts
    5

    performance turning

    I didn't get atleast one positive response from anyone since 3 days.
    expecting atleast one response..!!!

    K

  4. #4
    Join Date
    Apr 2004
    Posts
    64
    Quote Originally Posted by Prabhakarrao View Post
    I didn't get atleast one positive response from anyone since 3 days.
    expecting atleast one response..!!!

    K
    Hi Prabhakar,

    As N_I said above, to begin with you should run an EXPLAIN on the query and check access path. The very basic is not to have tablespace scan on any table unless its really small table.

    You should be asking specific questions instead of a general one that requires explaining entire query tuning which is quite a vast subject to get better response.

    Also you didn't write what platform your db2 runs. You may ckeck the Admin Guide and SQL Reference for more details on how to run EXPLAIN on a query and how to understand it's result.

    Regards,
    Amar

  5. #5
    Join Date
    Feb 2012
    Posts
    5

    performance turning

    Hi Xmar

    I am getting explain plan for the query in a pictorial model. Not like oracle(sql) type of explain plan. I am in full confusion. how to understand this kind of explain plan? Is there any way to get explain plan in a normal mode like text.
    I am a pure Oracle guy..I am new to DB2. unfortunately I got this problem fewdays back. I have to finish this task ASAP.

    Please advise...!!

    Rao

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can use db2exfmt tool to get the output in text mode.

    -
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Feb 2012
    Posts
    5

    performance turning

    Hi

    I am working on TOAD for DB2. In toad where can I get this feature
    db2exfmt tool. Please explain me.

    Regards
    Rao

Posting Permissions

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