db2 stored procedure error when update statement follows rollback

Question

I have a stored procedure where at the end I check for errors and if there are errors I perform a rollback and then update the status on the batch table to 'FAILED'. When I run the stored procedure I regularly get an SQLCODE 818 error saying there is 'a timestamp conflict occurred'.

When I remove the update statement that changes the status on the batch table, I do not get the error.

What is the best practice to perform these actions so I avoid getting the error?

The section of code looks like this:

IF v_error_count > 0 THEN                                           
    -- Batch failed      
    ROLLBACK;       

    UPDATE batch_table bt
    SET bt.batch_status = 'FAILED'
    WHERE batch_id = input_batch_id;        

END IF;

Thanks for any help.

What is the isolation level used? probably another process is modifying at the same time. — AngocA, Jan 22 '14 at 16:52

score 0 · Answer 1 · answered Jan 22 '14 at 17:05

SQL Code -818 indicates that the internal timestamp DB2 uses to ensure consistency between the running module matches the DBRM version created when the SQL Statements were precompiled.

You might check with your DBA (or someone else at your site), because the specific steps you have to perform may vary. For a general overview, you can see this article on IBM Knowledge Center.

db2 stored procedure error when update statement follows rollback

1 Answers1