Updating System Temporary Tables

Go Up to Monitoring with System Temporary Tables

There are cases where, having acquired information about the state of the database, you need to take appropriate action. You might, for example, detect a transaction that had unexpectedly been open for many hours, or one that was consuming resources that were needed by others. By updating the TMP$STATE column of certain temporary tables, you can perform the following updates:

• Roll back an active or limbo transaction
• Commit a limbo transaction
• Cancel an attachment’s executing operation
• Shut down the current attachment
• Ping database attachments to check if they are alive
• Make an executing statement stop running

Making single changes

The following examples operate on a single attachment or transaction.

Action	Statement
To roll back an active transaction	UPDATE TMP$TRANSACTIONS SET TMP$STATE = 'ROLLBACK' WHERE TMP$TRANSACTION_ID=123;
To roll back a limbo transaction	UPDATE TMP$TRANSACTIONS SET TMP$STATE = 'ROLLBACK' WHERE TMP$TRANSACTION_ID=123;
To commit a limbo transaction	UPDATE TMP$TRANSACTIONS SET TMP$STATE = 'COMMIT' WHERE TMP$TRANSACTION_ID=123;
To cancel the attachment’s currently executing operation	UPDATE TMP$ATTACHMENTS SET TMP$STATE = 'CANCEL' WHERE TMP$ATTACHMENT_ID=123;
To shut down the current attachment	UPDATE TMP$ATTACHMENTS SET TMP$STATE = 'SHUTDOWN' WHERE TMP$ATTACHMENT_ID=123;
To ping an attachment by sending a keepalive request. If the connection/attachment is defunct, the server will automatically cleanup the port	UPDATE TMP$ATTACHMENTS SET TMP$STATE = 'KEEPALIVE' WHERE TMP$ATTACHMENT_ID=123;
To make an executing statement stop running	UPDATE TMP$STATEMENTS SET TMP$STATE = 'CANCEL' WHERE TMP$STATEMENT_ID=123;

Note:
Shutting down an attachment detaches the user from the database and terminates the local or network attachment to the server.

Making global changes

You can make more global changes, as listed below.

Action	Statement
To roll back all active transactions	UPDATE TMP$TRANSACTIONS SET TMP$STATE = 'ROLLBACK' WHERE TMP$STATE ='ACTIVE';
To roll back all limbo transactions	UPDATE TMP$TRANSACTIONS SET TMP$STATE = 'ROLLBACK' WHERE TMP$STATE ='LIMBO';
To commit all limbo transactions	UPDATE TMP$TRANSACTIONS SET TMP$STATE = 'COMMIT' WHERE TMP$STATE ='LIMBO';

Advance To:

• Viewing Statistics using IBConsole