DB2 timestamp query - Query optimization

arjunma

New Member

Join Date: Feb 2014
Posts: 1

DB2 timestamp query - Query optimization

Feb 13 '14, 09:15 AM

Hi All,

My requirement is to fetch the previous day records.Iam joining three tables hereby.I used the below queries,but the DB2 cost seems to be high.Is there any simpler way to reduce the db2 cost and optimize the query further?

Query:
-----
Iam selecting around ten fields and below is my where condition,

Query:1
--------

Code:

FROM     ARJ00T.ARJAUTH A, ARJ00T.ARJCUST B, ARJ00T.ARJCMNT C 
WHERE    DATE(C.CRTN_TS) = DATE(CURRENT TIMESTAMP - 1 DAYS)      
  AND    A.AUTH_RFRNC_NO = B.AUTH_RFRNC_NO                       
  AND    A.AUTH_RFRNC_NO = C.RFRNC_NO                            
  AND    A.LOB_C = 'CCDO'                                        
  AND    A.PROD_CODE = 'PPC'                                     
  AND    B.CUST_TP_C = 'IND'                                     
WITH     UR

Query:2
-------

Code:

WHERE C.CRTN_TS BETWEEN TIMESTAMP(CURRENT_DATE - 1 DAY) 
AND   TIMESTAMP(CURRENT_DATE - 1 DAY) + 86399 SECONDS

Last edited by Rabbit; Feb 14 '14, 02:50 AM. Reason: Please use [code] and [/code] tags when posting code or formatted data.

Tags: None

Rabbit

Recognized Expert MVP

Join Date: Jan 2007

Posts: 12517
#2

Feb 14 '14, 02:52 AM

Please use code tags when posting code or formatted data.

That's a pretty simple query unless you're doing something complicated in the SELECT clause that you're not showing us. The only optimizations you can do is putting on the proper indexes.
Comment

DB2 timestamp query - Query optimization

DB2 timestamp query - Query optimization

Comment