Topic
1 reply Latest Post - ‏2013-07-04T00:01:30Z by MatthiasNicola
C976_NILANJAN_SIL
C976_NILANJAN_SIL
1 Post
ACCEPTED ANSWER

Pinned topic DB2 Select Query

‏2013-07-02T07:47:06Z |

Guys,

I have a problem in solving an DB2 SQL problem:

The following query is a part of a stored procedure:

SELECT t.transaction_id
        INTO v_second_leg_trans_id
        FROM DBPAT001.T0080TRANSACTION t        
        INNER JOIN DBPAT001.T0080TRANSACTION p
            ON t.parent_transaction_id = p.transaction_id
        INNER JOIN DBPAT001.T0080TRANSACTION m
            ON p.parent_transaction_id = m.transaction_id
        WHERE m.transaction_internal_reference = INTEGER(v_master_ref)
        AND p.transaction_internal_reference = INTEGER(v_order_ref)
        AND t.transaction_internal_reference = INTEGER(SECOND_LEG_REF);

Where v_master_ref & v_order_ref are internally declared variables and SECOND_LEG_REF is a constant all of INTEGER datatype. This query takes min 30 seconds to execute. But if I run the same query by replacing the variables with valid integer inputs, it takes less than a second, but we can't use the query in that way as a apart of stored procedure.

Please help.

Rgds,

Nil

  • MatthiasNicola
    MatthiasNicola
    45 Posts
    ACCEPTED ANSWER

    Re: DB2 Select Query

    ‏2013-07-04T00:01:30Z  in response to C976_NILANJAN_SIL

    Nil,

    my suggestion would be to use the explain facility (such as db2exfmt, or the Visua Explain in Data Studio) to compare the query execution plans using the variables vs. using actual integer values.

    When you use actual integer values then the DB2 optimzier can do much more precise cardinality and selectivity estimates for these predicates, which may lead to a better execution plan.

    - Matthias