This problem is not only for an upsert statement with index.  It is obviously a limit that Trafodion can also run into with a delete or an update (without any index).  Unlike insert, which has an alternative of using upsert to avoid transactions, delete and update do not similar alternatives.  Any attempt to address this problem should also take insert/delete/update into consideration.  As shown here, the script below tries to delete 5000 rows from a table.  The delete statement ran for 4 hours.  After that, it returned a TMF error, a conflicting message saying ‘5000 row(s) deleted’, and the rows were not really deleted.  5000 rows seem to be the limit for this particular table.  A delete of 4000 rows ran OK.

------------------------------------------------

Here is the script:

drop schema mytest cascade;
create schema mytest;
set schema mytest;
create table abase like g_wisc32.abase with constraints with partitions;
upsert using load into abase select * from g_wisc32.abase;
select count(*) from abase;
update statistics for table abase on every column sample random 5 percent;
prepare x1 from select count(*) from abase where unique2 < 5000;
prepare x2 from delete from abase where unique2 < 5000;
explain options 'f' x1;
explain options 'f' x2;
sh date;
execute x1;
sh date;
execute x2;
sh date;
select count(*) from abase;

------------------------------------------------

Here is the execution output:

>>drop schema mytest cascade;

--- SQL operation complete.
>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>create table abase like g_wisc32.abase with constraints with partitions;

--- SQL operation complete.
>>upsert using load into abase select * from g_wisc32.abase;

--- 32000000 row(s) inserted.
>>select count(*) from abase;

(EXPR)
--------------------

            32000000

--- 1 row(s) selected.
>>update statistics for table abase on every column sample random 5 percent;

--- SQL operation complete.
>>prepare x1 from select count(*) from abase where unique2 < 5000;

--- SQL command prepared.
>>prepare x2 from delete from abase where unique2 < 5000;

--- SQL command prepared.
>>explain options 'f' x1;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

2    .    3    root                                                  1.00E+000
1    .    2    sort_scalar_aggr                                      1.00E+000
.    .    1    trafodion_scan                  ABASE                 3.29E+001

--- SQL operation complete.
>>explain options 'f' x2;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

1    .    2    root                  o         x                     3.29E+001
.    .    1    trafodion_delete                ABASE                 3.29E+001

--- SQL operation complete.
>>sh date;
Sun Feb  8 23:09:52 UTC 2015
>>execute x1;

(EXPR)
--------------------

                5000

--- 1 row(s) selected.
>>sh date;
Mon Feb  9 00:14:43 UTC 2015
>>execute x2;

*** ERROR[8606] Transaction subsystem TMF returned error 97 on a commit transaction.

--- 5000 row(s) deleted.
>>sh date;
Mon Feb  9 04:37:46 UTC 2015
>>select count(*) from abase;

(EXPR)
--------------------

            32000000

--- 1 row(s) selected.