I am trying to execute below query to keep last 3 months of data in the table and delete rest of the data.
DELETE FROM REPORTER_STATUS WHERE FIRSTOCCURRENCE < current timestamp - 3 MONTHS;
However when I run the above query, I am getting deadlock or timeout error as below.
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 "2". SQLSTATE=40001 SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". Explanation: The current unit of work was involved in an unresolved contention for use of an object and had to be rolled back. The reason codes are as follows: 2 The transaction was rolled back due to a deadlock. 68 The transaction was rolled back due to a lock timeout. 72 The transaction was rolled back due to a DB2 Data Links Manager error during the transaction. 73 The transaction was rolled back because a queuing threshold such as the CONCURRENTDBCOORDACTIVITIES threshold caused two or more activities to reach a deadlock state. For more information, see "CONCURRENTDBCOORDACTIVITIES threshold" in the DB2 Information Center. The application was rolled back to the previous COMMIT. User response: The changes associated with the unit of work must be entered again. To help avoid deadlock or lock timeout, issue frequent COMMIT operations, if possible, for a long-running application, or for an application likely to encounter a deadlock. Federated system users: the deadlock can occur at the federated server or at the data source. There is no mechanism to detect deadlocks that span data sources and potentially the federated system. It is possible to identify the data source failing the request (refer to the problem determination guide to determine which data source is failing to process the SQL statement). Deadlocks are often normal or expected while processing certain combinations of SQL statements. It is recommended that you design applications to avoid deadlocks to the extent possible. For more detailed information about preventing deadlocks or lock timeouts search the DB2 Information Center (http: //publib.boulder.ibm.com/infocenter/db2luw/v9) using phrases such as "deadlock prevention", and terms such as "deadlocks" and "lock timeouts". If a deadlock state was reached because of a queuing threshold such as the CONCURRENTDBCOORDACTIVITIES threshod, increase the value of the queuing threshold. sqlcode: -911 sqlstate: 40001
I have restarted DB2 and the server to release the locks, but it did not help me in fixing the issue. Even issued 'COMMIT' staement to release locks, but in vain. :(
OS : RHEL 5.5 64-bit
Database : DB2 V9.7
Please help me out in fixing this issue.
G Ravi Chandran.