I have a database with just 4 tables, and 4-5 applications writing dozens of records a seconds to each of them. To prevent the tables getting too big and also to solve the problem of archiving the data, I've written a C++ application to:
1. send a message to every application that writes to the tables to stop for 30 seconds
2. rename the tables every hour and create them again with the same names.
It works, but sometimes I get this error:
RENAME TABLE DBO.TR to TR2011100803
[IBM][CLI Driver][DB2/NT64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001
My logs tell me that all of the applications received the message and stopped writing to the table.
The database is DB2 Express-C, LOCKTIMEOUT is set to 1.
What is wrong?
Is there a command I can send to DB2 to forcefully disconnect all clients and then rename the table?
Please help, this is urgent for me.