DB2 merge upsert gets 'Row not found for MERGE' error

Question

I am trying to do a basic upsert on an iSeries db2 with the MERGE statement, similar to as described in Does DB2 have an "insert or update" statement? and http://db2performance.blogspot.com/2011/12/merge-make-your-upserts-quick.html. When executed, it gives me Row not found for MERGE. SQLSTATE=02000 instead of inserting the row. Since I have when not matched then insert in the statement, why will it return an error instead of inserting? I looked all over SO and didn't see this particular issue.

Here is the statement I'm using:

merge into UFDFTRN as T using (
    select * from UFDFTRN 
    where DFCNO = 354 and DFINV = 1179 and DFLC = 1 and DFDATE = '2017-01-31'
        and DFSPLT = 0 and DFSEQ = 100
) as S on (
    T.DFCNO = S.DFCNO and T.DFINV = S.DFINV and T.DFDATE = S.DFDATE and
    T.DFSPLT = S.DFSPLT and T.DFSEQ = S.DFSEQ
) when matched then 
    update set DFSEQ = 1000, DFTRAN = 0, DFITEM = 'F224', DFRITM = '0', 
        DFDESC = 'DAIRY VTM PREMIX', DFQTY = 3, DFUM = '',DESIQU = 0, DFRTQU = 3,
        DFUPR = 0, DFCTUP = 0, DFUCST = 0, DFOUCST = 0, DFAMT = 0, DFOAMT = 0, DFCODE = '',
        DFURAT = '', DFCGCD = '0', DFCTNO = 0, DFADJITM = '', DFADJPCT = 0, DFMNFITM = '',
        DFMNFRAT = '', DFMNFQTY = '0', DFMNFTQTY = '0'
when not matched then 
    insert (DFCNO, DFINV, DFLC, DFDATE, DFSPLT, DFSEQ, DFTRAN, DFITEM, DFRITM, DFDESC,
        DFQTY, DFUM, DFSIQU, DFRTQU, DFUPR, DFCTUP, DFUCST, DFOUCST, DFAMT, DFOAMT, DFCODE,
        DFURAT, DFCGCD, DFCTNO, DFADJITM, DFADJPCT, DFMNFITM, DFMNFRAT, DFMNFQTY, DFMNFTQTY
    ) values (
        354, 1179, 1, '2017-01-31', 0, 1000, 0, 'F224', '0', 'DAIRY VTM PREMIX', 3, '', 0,
        3, 0, 0, 0, 0, 0, 0, '', '', '0', 0, '', 0, '', '', '0', '0'
    )

Answer 1

It probably should look more like this:

merge into UFDFTRN as T using (
    select 354 DFCNO, 1179 DFINV, 1 DFLC, '2017-01-31' DFDATE, 0 DFSPLT, 100 DFSEQ
           , 'DAIRY VTM PREMIX' f1 -- all other columns you might need
    from sysibm.sysdummy1 
) as S 
on (
    T.DFCNO = S.DFCNO and T.DFINV = S.DFINV and T.DFDATE = S.DFDATE and
    T.DFSPLT = S.DFSPLT and T.DFSEQ = S.DFSEQ
) 
when matched then 
    update set T.DFSEQ = S.DFSEQ, T.DFTRAN = S.DFTRAN, -- etc. etc.
when not matched then 
    insert (DFCNO, DFINV, ... -- etc. etc.
    ) values (
        S.DFSNO, S.DFINV, ..., S.F1, ...-- etc. etc.
    )

PS. Not tested.