DB2 LUW v9.5 fp7;

Consider the following statement:

MERGE INTO ADA.T_VMSTAT_CONV AS I USING
(
...
)
as INDATA (MACHINE_ID, COLLECT_TIME, other_fields, ...)

ON (I.MACHINE_ID = INDATA.MACHINE_ID AND I.COLLECT_TIME =
INDATA.COLLECT_TIME)
when matched then UPDATE ...
when not matched then INSERT ...

CREATE UNIQUE INDEX db2inst1.IXVMSTAT_CONV ON ADA .T_VMSTAT_CONV
(MACHINE_ID ASC, COLLECT_TIME ASC) ALLOW REVERSE SCANS;

I just reorged the table and updated statistics.

The INDATA fullselect returns no more than 100 rows. Targe table
T_VMSTAT_CONV has 2M rows.

Even though there is an index on (MACHINE_ID, COLLECT_TIME), the MERGE
is performing a tablescan on T_VMSTAT_CONV.

Here is part of the plan:

6543.15
HSJOIN
( 11)
269600
62323
/----------------------------------
+----------------------------------\
1.06898e
+07
6543.15

TBSCAN
FILTER

( 12)
( 13)

244968
23503.9

61143
1180.04

+07
19629.5
TABLE:
ADA
GRPBY

T_VMSTAT_CONV
( 14)

Q20
23499.7

1180.04



Operator 13 is the result of the fullselect / INDATA.
Somehow, DB2 chooses not to use the created indexes even with the
MERGE ON clause matching the index definition.

I have other MERGES on different target tables running IXSCAN, but
this query plan always show me TBSCAN on the target.

Any ideas why?

Thanks