Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: On-line reorg - how to make deletes and updates

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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
    Last edited by grofaty; 10-20-03 at 03:09.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Can you please write command how to do above thing.

    Thanks,
    Grofaty

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thank you Marcus_A that has solved my problem.

    Grofaty

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I need to find a job that pays money.

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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



    Originally posted by grofaty
    Hi,

    Thank you Marcus_A that has solved my problem.

    Grofaty
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •