Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Deadlock / timeout on dropping a table

    Hi,

    I have an ever growing table that currently has around 43 million rows and I tried to drop the table but get this error when I run the drop table command --

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL0911N The current transaction has been rolled back because of a deadlock
    or timeout. Reason code "68". SQLSTATE=40001


    We have a program that connects against the db all the time and inserts records into the table every minute. I check the db config parameters and see this for lock timeout --

    Lock timeout (sec) (LOCKTIMEOUT) = 60


    My question is do I just need to increase the lock timeout value and / or can I try running the command again after a db2 force applications all? I didn't issue the force applications all command when I tried it out earlier.

    OR do I really need to kill the program that's connecting to the db so I that can drop the table. Thanks!!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you are not on v9.7, I don't think you can drop a table while it is being accessed. It should suffice if you quiesce the database.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by n_i View Post
    If you are not on v9.7, I don't think you can drop a table while it is being accessed.
    What option do you have in v9.7?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Soft invalidation. The DROP statement completes, but the object is not actually dropped until all applications that are using it complete.

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by n_i View Post
    If you are not on v9.7, I don't think you can drop a table while it is being accessed. It should suffice if you quiesce the database.
    Thanks for the reply! Does it make sense to quiesce the database or instance? If quiescing the database, would I do something like this? --

    db2 quiesce database immediate force connections

    db2 "drop table <tablename>"

    db2 unquiesce db


    Is the only other alternative to stop / start the db? Thanks for your help!

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by n_i View Post
    Soft invalidation. The DROP statement completes, but the object is not actually dropped until all applications that are using it complete.
    This doesn't seem to work for DROP TABLE - got a lock timeout.

  7. #7
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    From a session, try to lock the particular table in exclusive mode and then from the same session run the drop statement. It should work.

    C:\Documents and Settings\Administrator>db2 "LOCK TABLE DUMMY IN EXCLUSIVE MODE"
    DB20000I The SQL command completed successfully.

    C:\Documents and Settings\Administrator>db2 drop table dummy
    DB20000I The SQL command completed successfully.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  8. #8
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by JAYANTA_DATTA View Post
    From a session, try to lock the particular table in exclusive mode and then from the same session run the drop statement. It should work.

    C:\Documents and Settings\Administrator>db2 "LOCK TABLE DUMMY IN EXCLUSIVE MODE"
    DB20000I The SQL command completed successfully.

    C:\Documents and Settings\Administrator>db2 drop table dummy
    DB20000I The SQL command completed successfully.
    Is this version independent? Or will it only work for version 9 and above?

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by JAYANTA_DATTA View Post
    From a session, try to lock the particular table in exclusive mode and then from the same session run the drop statement. It should work.

    This test is not valid... has to be done from two different sessions. I also had to turn autocommit off.

    session #1:

    $ db2 +c "lock table testme in exclusive mode"
    DB20000I The SQL command completed successfully.



    session #2:

    $ db2pd -d testme -locks

    Database Partition 0 -- Database TESTME -- Active -- Up 0 days 02:05:21 -- Date 10/27/2011 14:47:32

    Locks:
    Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
    0x0700000040501B80 2 4141414141504161B4F02AB641 Internal P ..S G 2 1 0 0x00000000 0x40000000 0
    0x0700000040501680 2 000000050000360443A821A043 CatCache ..S G 2 1 0 0x00000000 0x40000000 0
    0x0700000040866B00 2 00000005000000000036000452 Row .NS G 2 1 0 0x00000000 0x40000000 0
    0x0700000040501C80 2 00020004000000000000000054 Table ..X G 2 255 0 0x00003000 0x40000000 0
    0x0700000040866C00 2 00000005000000000000000054 Table .IS G 2 1 0 0x00003000 0x40000000 0


    $ db2 drop table testme
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0911N The current transaction has been rolled back because of a deadlock
    or timeout. Reason code "68". SQLSTATE=40001


    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

  11. #11
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by db2user24 View Post
    Thanks for the reply! Does it make sense to quiesce the database or instance? If quiescing the database, would I do something like this? --

    db2 quiesce database immediate force connections

    db2 "drop table <tablename>"

    db2 unquiesce db


    Is the only other alternative to stop / start the db? Thanks for your help!
    Any thoughts on this? The reason why I ask is because the program that connects to the db is connecting using the instance name. So am wondering if it makes sense to quiesce the instance instead? Thanks!!

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If your application connects as the instance owner, there's no use of quiesceing the database - the application will override the quiesce.

  13. #13
    Join Date
    Nov 2007
    Posts
    265
    thanks, so would the solution be to either quiesce the instance or something else? I think in the past it has worked when I did a force applications all, stop db, start db first and then issued the drop table command. Appreciate your help!

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Just do what you had done in the past. For remote connections, you can unset svcename.

    If nothing works, drop the db.

  15. #15
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by db2girl View Post
    Just do what you had done in the past. For remote connections, you can unset svcename.

    If nothing works, drop the db.


    thanks, although dropping the db is dangerous advice just curious, is there any risk involved in unsetting svcename and then setting it again? thanks!

Posting Permissions

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