On Feb 21, 2:07 am, "Sam Durai" <reachsamdu...@gmail.com> wrote:
> Hello, I need to merge a small table (of rows less than 100,sometimes
> even 0 rows) to a big table (of rows around 4 billion). I used the PK
> of the big table as merge key but merge does a table scan so it runs
> for ever.
>
> I checked the table and PK statistics of the big table and it looks
> good. Please let me know if I need to check for something else.
>
> Here are more details
> Small table - Non Partitioned ( Node 0)
> Big table - Partitioned across 7 logical nodes (Node 1 - 8)
>
> Query:
> MERGE INTO CARD.VIN_VEH_OPTNS AS A USING (
> SELECT VEH_IDENT_NBR, OPTN_CD, ERR_FLAG
> FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
> WHERE ERR_FLAG IN ('N', 'Y'))AS B ON (A.VEH_IDENT_NBR =
> B.VEH_IDENT_NBR AND A.OPTN_CD =B.OPTN_CD AND
> A.VEH_OPTN_CD_CSI =13)
> WHEN NOT MATCHED
> THEN
> INSERT (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI, OPTN_INSTLT_TYP_CD,
> PART_SERIAL_NBR, BROADCAST_CD, SOURCE_SYSTEM_CD, SOURCE_DT,
> DWH_EFCTV_TIMSTM, DWH_UPD_TIMSTM)VALUES (B.VEH_IDENT_NBR,
> B.OPTN_CD, 13, '', '', '', 34, Current Timestamp, '
> 2007-02-20 10:39:53', '2007-02-20 10:39:53')
> ELSE IGNORE
>

I thought that it is worth to try following index.
Because, to check MATCHED or NOT, data of columns A.VEH_IDENT_NBR,
A.OPTN_CD, A.VEH_OPTN_CD_CSI from Table "CARD.VIN_VEH_OPTNS AS A" are
necessary. If there is no appropriate index, DB2 need to see table
itself to acquire these values. Consequentry, tablespace scan may be
inevitable.
CREATE INDEX CARD_IDNBR_CD_CSI ON CARD.VIN_VEH_OPTNS
(VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI)