Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Unanswered: record not found or changed by another user

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Please post the stored procedure syntax and the exact error number/message you get from DB2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2007
    Posts
    2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •