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!!

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.

What option do you have in v9.7?

Soft invalidation. The DROP statement completes, but the object is not actually dropped until all applications that are using it complete.

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!

This doesn't seem to work for DROP TABLE - got a lock timeout.

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?

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

Not for tables, looks like...

Soft invalidation of database objects - IBM DB2 9.7 for Linux, UNIX, and Windows

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!!

If your application connects as the instance owner, there's no use of quiesceing the database - the application will override the quiesce.

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!

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!