Hi,
Have you tried :
SELECT SYSLIB.AbortSessions(1,'your_user',0,'Y','Y');
Cheers,
Raja
Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.
Hi Noel,
If you have found a solution for this, then please kindly share.
The documentation says that it is not allowed.
Regards,
Vlad.
Hi Noel,
Just to add : I am curious if it is hard RI or soft RI too(it can be strange with data volume), can you please share this info for me .
Vlad,
If you dont mind, can you please share the document you refer to my mail id : ktraj1@gmail.com.
Thanks and regards,
Raja
Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.
Hi Raja,
I've seen it here: http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/Utilities/B035_1102_111A/RecoveryMgr.51.23.html
With kind regards,
Vlad.
Hi,
I used to kill jobs, either from Linux, unix prod as a last resort. It's usually fine. Of course, with other DBs, the index becomes unusabe or invalid most of the time.
Thank you very much for the link Vlad.
Cheers,
Raja
Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.
We are using 13.10 and the table does have hard RI ("with check option").
The following will initiate the abort but I'm looking at cancelling the abort:
SELECT SYSLIB.AbortSessions(1,'your_user',0,'Y','Y');
The link talks about the "cancel rollback on table" command and it also mentions the exact issue I'm encountering:
"You cannot cancel rollback on tables that have any referential integrity constraints."
Not sure why a referential integrity constraint would disallow you from cancelling the rollback, I suspect the utility simply does not handle it and should ideally be enhanced to handle the scenario in a future release. Maybe someone from Teradata utility development can comment.
Thanks,
Noel
Raja, you mentioned that you killed jobs, do you mean kill specific Teradata processes at the OS layer? If so, how would you determine which one is performing the rollback? Note that I even tried restarting the database and the rollback simply continued from where it left off.
Thanks,
Noel
You didn't mention how you were doing the loading?
An Insert Select into an empty table should be a fast rollback. A tpump style load should be a short rollback because each PACK is committed.
Re the questions about restart to get rid of the rollback - that will not help because the journal is kept over restart events in order to make sure we maintain database consistency over an event like that. As the system restarts, it scans the journal for uncommitted transactions and starts rollbacks for each one before allowing the system to open for business, including locking the table(s) appropriately. All that a restart accomplishes is to make the rollback take longer due to the rollback and journal scan.
To the question about aborting the Teradata process doing the rollback: Don't go there! Forcibly aborting Teradata system tasks/threads will crash the system and cause a restart at best and can also hang the system requiring services intervention.
Good question and great feedback Todd.
Yes, you are right, it is a bulk insert (insert select) but it has a where not exists clause on the target table to only insert applicable records which is part of the performance and rollback problem (even if the target table is empty).
This works nicely for smaller tables but awful for larger tables so it has been decided to NEVER use this pattern for larger table loads and we have instead implemented other alternitves. Over time, we may accidentally encounter this type of issue again since we do bulk insert loads for many of our tables. I would like to have an action plan if we encounter this type of issue again.
Thanks,
Noel
/insert/ into table a (f1, f2) select b.f1, b.f2 from table b /where/ not /exists/ (select a1.f1 from a as a1 /where/ a1.f1=b.f1 and a1.f2=b.f2)
The select does not affect the insert. It does suggest that the target table is not empty since it is not going to do anything when the table is empty. It will cause the intermediate result to be spooled first but it will not affect the insert to empty table optimization which also optimizes the rollback.
I would be looking for skew either in the data being inserted by the PI of the target or in the column on which the RI is specified.
If the table is indeed being loaded empty, then an alternative is to add the RI constraint after the data has been loaded. Testing this case might also help narrow down why the insert is taking so long.
Note that we are using an ansi connection which probably does not affect on how it does the rollback. The UPI is an integer generated by default which should not cause any skew. There is however an additional large nusi index that may also affect the rollback. The initial load can easily have the FKs and non-PI indexes added after. There are scenarios where we end up loading a couple weeks of data in a single transaction sometime later but cancelling the rollback will most likely corrupt the target table which is undesirable.
Thanks,
Noel
It sounds like the additional couple weeks would be much smaller resulting in a much shorter update time and a much shorter rollback time if the work is aborted.
We attempted a large load on an empty table that was highly ineficient and had to abort the session after hours of running.
Now the rollback has been running for over 24hrs and is still locking the target table.
We tried Recovery Mananager with the "cancel rollback on table" command but gave us the "has referential integrity constraint" error.
We also tried to restart the database but the lock still persits.
Since the table was empty, we are fine in somehow dropping it forcefully but I'm not aware of a way that would help us do that.
Can someone provide me with some options?
Thanks,
Noel