Detecting a Rollback in Progress
Sessions in rollback may appear to have logged off in both DBC.LogOnOffV and DBC.AccessLogV, but this is not always the case. The logoff would depend on the manner in which the job was aborted. If you specify one of the following:
ABORT SESSION hostid.username LOGOFF
ABORT SESSION *.username LOGOFF
ABORT SESSION hostid.* LOGOFF
then the LOGOFF option would terminate the session.
Without it, the session should continue until the abort completes or the Supervisor issues a LOGOFF request. Unless an SQL job is explicitly coded to do otherwise, a session will also appear to have logged off if the system has undergone a restart.
The rollback or abort is independent of the session. It is actually handled by a completely different mechanism with internally allocated AMP worker tasks.
Example
To activate the RCVmanager, go to the Database Window and type “start rcvmanager”. Then issue the “list rollback tables” command. It will show you each table that is being rolled back at that point in time, how many TJ rows have been rolled back and how many rows are remaining.
If you run this command twice, you can then make an estimate how long it will take the rollback to complete, based on the rows processed and rows remaining and the time between the two snapshots.
list rollback tables;
TABLES BEING ROLLED BACK AT 10:01:26 04/09/20
ONLINE USER ROLLBACK TABLE LIST
Host Session User ID Workload Definition AMP W/Count
---- -------- --------- -----------------------------
1 234324 0000:0001 24
TJ Rows Left TJ Rows Done Time Est.
------------- ------------- ---------
53638 1814 00:09:51
Table ID Name
--------- --------------------------
0000:16A6 "FINANCE_T"."Order_Header"
SYSTEM RECOVERY ROLLBACK TABLE LIST
Host Session TJ Row Count
---- -------- -------------
Table ID Name
--------- ------------- ----
Enter command, "QUIT;" or "HELP;" :
list rollback tables;
TABLES BEING ROLLED BACK AT 10:01:37 04/09/20
ONLINE USER ROLLBACK TABLE LIST
Host Session User ID Workload Definition AMP W/Count
---- -------- --------- ------------------- -----------
1 234324 0000:0001 24
TJ Rows Left TJ Rows Done Time Est.
------------- ------------- ---------
52663 2789 00:09:45
Table ID Name
--------- ---------------------------
0000:16A6 "FINANCE_T"."Order_Header"
SYSTEM RECOVERY ROLLBACK TABLE LIST
Host Session TJ Row Count
---- -------- -------------
Table ID Name
--------- ------------------
Enter command, "QUIT;" or "HELP;" :