November 30, 2011 at 4:42 pm
I have an app that executes Update and Select statements on DB2. If I run them seperately, it works fine. But if I run them together, it errors out.
What I really want to do is lock the DB2 table first, read the maxID column of that table and then release the lock. All in one statement. Is this doable?
UPDATE TableA SET Col = Col + 1 WHERE Col IN (SELECT MAX(Col ) FROM Table
SELECT MAX(Col) AS NextSeqNo FROM Table
Running independently it works. But how can i run them together? Also, I need a command for Lock the table and release the table. I checked th IBM website and they talk about Lock Table IN Exclusive Mode and Release(Commit)....Any body has any idea?
December 1, 2011 at 2:10 am
Are you using a linked server? Which OLEDB provider are you using?
Some things to check:
1) Isolation level
2) Two phase commit
3) Table journaling
What error are you getting?
Looks like you're simulating a sequence. You should update in the first place and select last, or set an higher isolation level.
-- Gianluca Sartori
December 19, 2011 at 9:53 am
You can do this...
BEGIN TRANSACTION
LOCK TABLE...
SELECT...
UPDATE...
COMMIT
This is a VERY bad way to use a database (SQL or DB2). Your planned approach does not make best use of DB2 facilities, and will slow performance for other users.
I suggest you find a forum that specialises in DB2 and ask what is the best way to auto-increment an Id number for inserted records.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply