Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2013
    Posts
    80

    Unanswered: Very Large Table Find MAX TIME Records

    Hi All,
    DB2 Version 9.7.6 In AIX
    I have another question on very large table (717 Mil ) to find out the MAX(time) records.

    Code:
    SELECT AR_X_LMT.AR_ID,AR_X_LMT.LMT_TP_ID,AR_X_LMT.EFF_DT,AR_X_LMT.LMT_AMT,AR_X_LMT.END_DT,
    AR_X_LMT.OD_RED_IND,AR_X_LMT.OD_RED_CD, AR_X_LMT.SCHED_RED_AMT,AR_X_LMT.OD_RED_DT,AR_X_LMT.BG_PRICE,
    AR_X_LMT.OD_LMT_IND,AR_X_LMT.SRC_STM_ID,AR_X_LMT.BK_CODE,AR_X_LMT.PPN_TM,current date, EXP_DT, AR_X_LMT.FAC_STATUS_TP_ID, AR_X_LMT.CPM_DT
    FROM DSSBDW.AR_X_LMT AR_X_LMT inner join 
    ( select max(PPN_TM) as PPN_TM, AR_ID, LMT_TP_ID from DSSBDW.AR_X_LMT where AR_ID in 
    (select AR_ID from DSSSTG.MNM_AR) group by AR_ID, LMT_TP_ID ) B on AR_X_LMT.AR_ID = B.AR_ID 
    									and AR_X_LMT.LMT_TP_ID = B.LMT_TP_ID 
    									and AR_X_LMT.PPN_TM = B.PPN_TM
    
    P/s: DSSSTG.MNM_AR contains 17mil records.
    
    For your infromation: DSSBDW.AR_X_LMT MIGHT have 3 Identical values for AR_ID,LMT_TP_ID and PPN_TM (Even after Max).

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    have you tried to create refresh deferred MQT with
    Code:
    select max(PPN_TM) as PPN_TM, AR_ID, LMT_TP_ID 
    from DSSBDW.AR_X_LMT 
    where AR_ID in (select AR_ID from DSSSTG.MNM_AR) 
    group by AR_ID, LMT_TP_ID
    ?
    Regards,
    Mark.

Posting Permissions

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