After I run a simple stored procedure, the data is left in a "changed" mode so that when I try to update the data, I get the error "record not found or changed by another user". What am I missing in my stored procedure to "finalize" the data so I don't get the error message later.

The only thing in the stored procedure is a MERGE command.

Please post the stored procedure syntax and the exact error number/message you get from DB2.

I am still waiting to get the SQL error code/message from the vendor since all I see is the "record not found or changed by another user" error.

Here's the body of the stored procedure:

------------------------------------------------------------------------
-- CWH 07.18.07 Part of SSIS package to interface ADP data to TruckMate.
-- Uses data in CTS_PR_EMPLOYEE and merges it (INSERT/UPDATE)
-- into PR_EMPLOYEE table.
------------------------------------------------------------------------

P1: BEGIN

MERGE INTO TMWIN.PR_EMPLOYEE AS p

USING ( SELECT CODE
,LAST_NAME
,FIRST_NAME
,MIDDLE_INITIAL
,ADDRESS_1
,ADDRESS_2
,CITY
,PROVINCE
,POSTAL_CODE
,PHONE
,EMERGENCY_CONTACT
,EMERGENCY_PHONE
,BIRTH_DATE
,DEPARTMENT
,TERMINATED
FROM TMWIN.CTS_PR_EMPLOYEE ) AS cts

ON p.CODE = cts.CODE

WHEN MATCHED THEN
UPDATE SET
LAST_NAME = cts.LAST_NAME
,FIRST_NAME = cts.FIRST_NAME
,MIDDLE_INITIAL = cts.MIDDLE_INITIAL
,ADDRESS_1 = cts.ADDRESS_1
,ADDRESS_2 = cts.ADDRESS_2
,CITY = cts.CITY
,PROVINCE = cts.PROVINCE
,POSTAL_CODE = cts.POSTAL_CODE
,PHONE = cts.PHONE
,EMERGENCY_CONTACT = cts.EMERGENCY_CONTACT
,EMERGENCY_PHONE = cts.EMERGENCY_PHONE
,BIRTH_DATE = cts.BIRTH_DATE
,DEPARTMENT = cts.DEPARTMENT
,TERMINATED = cts.TERMINATED

WHEN NOT MATCHED THEN
INSERT ( CODE
,LAST_NAME
,FIRST_NAME
,MIDDLE_INITIAL
,ADDRESS_1
,ADDRESS_2
,CITY
,PROVINCE
,POSTAL_CODE
,PHONE
,EMERGENCY_CONTACT
,EMERGENCY_PHONE
,BIRTH_DATE
,DEPARTMENT
,TERMINATED)
VALUES
( cts.CODE
,cts.LAST_NAME
,cts.FIRST_NAME
,cts.MIDDLE_INITIAL
,cts.ADDRESS_1
,cts.ADDRESS_2
,cts.CITY
,cts.PROVINCE
,cts.POSTAL_CODE
,cts.PHONE
,cts.EMERGENCY_CONTACT
,cts.EMERGENCY_PHONE
,cts.BIRTH_DATE
,cts.DEPARTMENT
,cts.TERMINATED) ;

--------------------------------------------------
-- commit the changes to the database
COMMIT;
--------------------------------------------------

END P1