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.

Answer 1

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.