Hi,

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?

Thanks,
Grofaty

Hi,

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?

Grofaty

Hi,

I have a table of 300000 records. If I try to delete or update 30000 records (10% of table) I receive the above message, but if I try to delete only few records (100 records) it works fine.

Why does transaction roll back happen when trying to delete 10% of table?

Grofaty

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).

Hi,

Can you please write command how to do above thing.

Thanks,
Grofaty

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.

Hi,

Thank you Marcus_A that has solved my problem.

Grofaty

I need to find a job that pays money.

Grofaty
I won't be surprised if your deadlock problem occurs again as the data in the table increases ...

So, you may want to do deletes by issuing commits every 'n' number of rows ... You can use a stored procedure for this ....

HTH

Sathyaram