In View Point, a session is in ABORTING state for more than 8 days now.
Whats the reason the session is taking so long to abort?
Is there any way to get rid of this aborting session, to clean up such sessions quickly?
Go to recovery manager and check the status of rollback session
You can cancel the rollback if exists ...
If no sessions in roolback state .. it is bug.
You can use commands like LIST STATUS,LIST ROLLBACK TABLES.If that session is listed in output of above command then rollback is still in progress. If CPU is not an issue then increase the rollback priority
If the aborting session is not reported in the output of above mentioned commands then check with GSC. TPA reset with dump will clear those and GSC can investigate why they were in aborting state for so long .
~ Abhishek Jadhav
Above post you mention 2 commands RIght ? like LIST STATUS,LIST ROLLBACK TABLES
Actually where i have to run this commands ? like SQLA or bteq ?
When i am trying to ran this commands on SQLA ,we are getting error ?
Like thsi ?
Query is invalid ?
Please let me know the procedure ?
And my question is how to check abort sessions for any user ? in case we dont have VP ? if i have TD Manager where i have to find this ?
Or is their any query to find out
THanks in advance
RCVManager is CNS subsystem utility. You cannot invoke them via queries. Rather you can use Viewpoint Remote Console Utility OR use Cnsterm 6.
> Cnsterm 6
rcmanager started in cnsterm 1
Go to cnsterm 1, then just type the command "LIST ROLLBACK TABLES;"
It will return the table name as well as the time estimated to complete the rollback.
In other case, the session appears to be in 'Aborting state', but it wont be listed by LIST ROLLBACK TABLES command, that does not mean that the table is NOT in rollback state. Remember that RCVMANGER return the table name if you execute that command 'provided' the transcation in rollback state has more than 10000 rows on at least one AMP. So 2 things we should remember is:
1) The transaction should be in 'aborting state' in Viewpoint
2) Table that is rolling back does not have more than 10000 rows in TJ (Transient Jrnl) on at least one amp, it will not be listed by the command.
Coming to LIST STATUS, correct syntax for this command as per 13.10 version is: LIST STATUS VprocID;
For ex: LIST STATUS 0;
The requesting AMP is not executing Dow AMP recovery.
That means this vproc0 is not participating in the recovery of this rollback. From PUMA -c command, you can see the whether the MSGWORKABORT AWTs are high inuse or not. As MSGWORKABORT is meant for internal aborts, you can find out which particular vproc returning high number and check LIST STATUS for that vproc. This will give you clear picture on finding out whether its a real rollback OR not and how costly it is.