Unanswered: On-line reorg - how to make deletes and updates
I have tried to use the on-line reorg in db2 8.1 fp 2 on Windows 2000.
I have used the following command:
REORG TABLE table_name INDEX index_name INPLACE ALLOW WRITE ACCESS START
During execution of above command data can be inserted into "reorg" table, but can't be updated or deleted. Is this true or am I doing something wrong? As the name says "ON-LINE", does this mean I can do SELECT, INSERT, UPDATE and DELETE on "reorg table"? What is the command to enable me to do select, insert, update and delete on "reorg" table?
I have forgotten to post the error message I received when I execute delete during on-line reorg.
Message: "SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001"
Why this happens? How to enable on-line reorg table and have privilege to do delete, update, insert and select statements?
Possibly lock escalation. If that is the problem, then you can try to increase the locklist size and/or increase maxlocks (the percentage of locks in the locklist that any one application can have before an escalation takes place).
First do a:
DB2 GET DATABASE CONFIGURATION FOR database-name SHOW DETAIL
You must attach to the database first.
Look for the following lines to determine existing locklist size and maxlocks percent:
Max storage for lock list (4KB) (LOCKLIST) = 100
Percent. of lock lists per application (MAXLOCKS) = 10
Then change the value using the following syntax:
db2 update db cfg using locklist 1000 immediate
db2 update db cfg using maxlocks 50 immediate
(These are example values only).
Please note that I am only guessing that your problem may have been caused by lock escalation. That may have not been the problem. But the default values are a bit low, so it may not hurt to change them. Note that the locklist storage will use real memory like a bufferpool does, so take that into consideration.