Is there any rule of thumb to optimize sql queries like this this is my first question here. Although I've been helped out from this forum over a hundred times.
I was having difficulties in optimizing sql query. It takes hours to execute. Record set is also large enough. The query was not written by me. So just to find the bottle neck I tried removing conditional clauses but that doesn't make any difference. Indexing the ID's done.

Can any sql guru here could throw some light on it. Is there any fine tuning room left in the query below? The server hosting the database in DB2. I'm not too pro in sql.
Thanks as always.
Regards, Nuh

This is the query:

SELECT
HEALTH_INSURANCE.RISK_DETAIL_ID ,
POLICY_RISK_COVER.RISK_COVER_ID ,
HEALTH_INSURANCE.RD_POLICY_SYSTEM_NO ,
RD_POLICY_END_NO_IDX ,
HEALTH_INSURANCE.RD_POLICY_ID ,
HEALTH_INSURANCE.RD_LEVEL1_ID ,
HEALTH_INSURANCE.RD_SUM_INSURED_AMT_LC ,
HEALTH_INSURANCE.RD_PREMIUM_AMT_LC ,
POLICY_RISK_COVER.PREMIUM_AMOUNT_FC ,
POLICY_RISK_COVER.SUM_INSURED_AMT_FC ,
HEALTH_INSURANCE.RD_REC_TYPE ,
HEALTH_INSURANCE.RD_EFFECT_FROM_DT ,
HEALTH_INSURANCE.RD_EFFECT_TO_DT ,
HEALTH_INSURANCE.RD_END_EFFECT_FROM_DT ,
HEALTH_INSURANCE.SEX_MAS_CD ,
HEALTH_INSURANCE.MARITAL_STATUS_CD ,
HEALTH_INSURANCE.EMP_CATG ,
HEALTH_INSURANCE.NO_OF_DEPENDENTS ,
CAST((
CASE 
    WHEN HEALTH_INSURANCE.AUTHORITY_LETTER_NO IS NULL 
    THEN HEALTH_INSURANCE.EMP_AL_NO 
    ELSE HEALTH_INSURANCE.AUTHORITY_LETTER_NO 
END) AS INT) AS EMP_AL_NO ,
HEALTH_INSURANCE.DOB ,
HEALTH_INSURANCE.EFF_DATE ,
HEALTH_INSURANCE.EFF_DATE2 ,
HEALTH_INSURANCE.NAME ,
CAST((SUBSTR(HEALTH_INSURANCE.RELATIONSHIP_CD, 5,2)) AS INT) AS 
RELATIONSHIP_CD_S ,
HEALTH_INSURANCE.RELATIONSHIP_CD ,
HEALTH_INSURANCE.DESIGNATION ,
HEALTH_INSURANCE.BRANCH ,
HEALTH_INSURANCE.BANK_ACCOUNT ,
HEALTH_INSURANCE.BANK_BRANCH_NAME ,
HEALTH_INSURANCE.PRE_EXISTING_AILMENT ,
HEALTH_INSURANCE.AUTHORITY_LETTER_NO ,
HEALTH_INSURANCE.AGE ,
HEALTH_INSURANCE.REGION ,
HEALTH_INSURANCE.CNIC ,
HEALTH_INSURANCE.CO_CODE ,
HEALTH_INSURANCE.EMP_LOCATION ,
HEALTH_INSURANCE.SUB_LOCATION ,
CATEGORY_LIMIT_HEADER.CLH_SYSTEM_NO ,
CATEGORY_LIMIT_HEADER.CTH_SYS_ID ,
CATEGORY_LIMIT_HEADER.CTH_POL_SYS_ID ,
CATEGORY_LIMIT_HEADER.CTH_END_NO_IDX ,
CATEGORY_LIMIT_HEADER.CTH_END_SR_NO ,
CATEGORY_LIMIT_HEADER.CTH_CATEGORY ,
CATEGORY_LIMIT_DETAIL.CLD_SYS_ID ,
CATEGORY_LIMIT_DETAIL.CLDH_SYS_ID ,
CATEGORY_LIMIT_DETAIL.CLD_COVER_CD ,
CATEGORY_LIMIT_DETAIL.CLD_END_IDX ,
CATEGORY_LIMIT_DETAIL.CLD_COVER_DESC ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_TYPE_LIMIT ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_REL ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_FROM ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_TO ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_RB_LIMIT ,
CATEGORY_LIMIT_DETAIL.CLD_CATEGORY_LIMIT_FC ,
CATEGORY_LIMIT_DETAIL.CLD_CATEGORY_PREM_FC 
FROM
DB2ADMIN.HEALTH_INSURANCE AS HEALTH_INSURANCE 
    INNER JOIN DB2ADMIN.POLICY_RISK_COVER AS POLICY_RISK_COVER 
    ON HEALTH_INSURANCE.RD_POLICY_SYSTEM_NO = POLICY_RISK_COVER.
    RC_POLICY_SYSTEM_NO AND
    TRIM(RD_LEVEL1_ID) = TRIM(RC_LEVEL1_ID) 
        INNER JOIN DB2ADMIN.CATEGORY_LIMIT_HEADER AS CATEGORY_LIMIT_HEADER 
        ON HEALTH_INSURANCE.RD_POLICY_ID = CATEGORY_LIMIT_HEADER.
        CTH_POL_SYS_ID AND
        HEALTH_INSURANCE.EMP_CATG = CATEGORY_LIMIT_HEADER.CTH_CATEGORY 
            INNER JOIN DB2ADMIN.CATEGORY_LIMIT_DETAIL AS 
            CATEGORY_LIMIT_DETAIL 
            ON CATEGORY_LIMIT_HEADER.CTH_SYS_ID = CATEGORY_LIMIT_DETAIL.
            CLDH_SYS_ID AND
            POLICY_RISK_COVER.RISK_COVER_CD = CATEGORY_LIMIT_DETAIL.
            CLD_COVER_CD AND
            HEALTH_INSURANCE.RELATIONSHIP_CD = CATEGORY_LIMIT_DETAIL.
            CLD_CLM_REL 
WHERE
COALESCE(HEALTH_INSURANCE.AGE, 1) BETWEEN CATEGORY_LIMIT_DETAIL.
CLD_CLM_AGE_FROM AND
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_TO
Some rules of thumb that might be relevant to you . . .


Never try to tune a query without a representative EXPLAIN plan. Representative means to get an EXPLAIN plan either from the production database or from a dev/test database that have nearly the same number of rows and nearly the same distribution of values, ideally running on similar hardware. Different numbers of rows and different distribution of values often means different execution plans.
Make sure statistics are up to date.
Index (or verify indexes) on every column used in a JOIN clause and every column used in a WHERE clause. I think every dbms implements primary key constraints and foreign key constraints with indexes. There used to be some that wouldn't let you drop such indexes, and one or two that would let you. Since I deal with a lot of different dbms, I don't bother trying to remember which is which. I just look it up when I need to know. So just look it up.
One multi-column index might perform better than three single-column indexes. Think about consolidating indexes.
Write sargable WHERE clauses. Your expression COALESCE(HEALTH_INSURANCE.AGE, 1) BETWEEN one_column AND another_column has to evaluate the COALESCE() function for every row--it can't use an index. If it's possible, replace the NULLs with the literal integer 1, and make that column non-nullable. (This isn't always practical, but non-sargable WHERE clauses are often a performance killer, especially if other considerations provoke a full table scan.)