Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    23

    Unanswered: Inserts in Table as Deletes occur

    The situation is we have to do massive Deletes on a PRINT****NTIME table. A simple "delete" causes an Exclusive X lock on the table. While we're doing the delete, there are programs that need to insert rows to the end of this table. I say "end of the table" because the index is sequential and we're deleting lower index rows in the table at the same time that we're adding to the end (new, higher sequential indexes).

    Currently we are getting deal this error: SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68".

    Is there a way around this problem? We have to do deletes on most of the table rows (at the beginning, not at the end) but there will always be something inserting at the same time.

    Do you have any suggestions or has anyone encountered this issue?
    Thank you.

    CC

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Here are a few items to think of.
    how are you doing the deletes, a single row, multiple rows? delete where time <= host-var? delete where time = host-var?
    are the deletes using an index or tablespace scan?
    do they have to be done right away or can you perform the deletes at end of day/week/month when there is a period of time when you are not as busy? and can you issue commits between deletes?

    Dave

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So, what is timing out: inserts or deletes? Inserts should not be affected, unless there is lock escalation during the delete, for which there are only two solutions: either increase the lock list size (and MAXLOCKS) to accommodate large transactions, or make your transactions smaller. You should also consider table partitioning, if it is available in your version of DB2.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You didn't mention what version/OS you are on. Z/OS you can use reorg with discard option, I don't remember a similar command on LUW. If on LUW or Z/OS there are scripts/programs that you can google and copy that turn a single delete statement into a delete x number of rows and issue commits process.

    Dave

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try setting these environment variables:

    db2set DB2_SKIPDELETED=ON
    db2set DB2_SKIPINSERTED=ON
    db2set DB2_EVALUNCOMMITTED=ON

    then stop and start instance.

    But you should also use a cursor to do the deletes and commit after every 100 or 1000, or so deletes). Define the cursor WITH HOLD to keep it open even after the program does a commit. This would have to be done in an application program or Stored Procedure.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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