We are running into a problem when we restore a backup that causes the job to fail due to join indexes we have. The root cause has been determined, there's a patch that exists. But until we can get management to pay to get it installed, we have the situation in which tables need to have "RELEASE LOCK" run.
This morning I wrote code to perform a select top 10 * from each table in a restored database. I then collected all the errors (tables reporting a select couldn't be performed becasue the table was being restored), built a script with a "Build Data Table (<tablename>), release lock;" for each table that errored, and then ran the resulting code.
Question: Is there a way to globally apply the release lock to an entire database, or other method to release all the tables in a database in a more automated fashion?
You can test the "Forced" option for Release Lock and see it works for you.
Forced = Instructs Teradata ARC to try and release any placed locks if the archive fails. However there are some exceptions,you can find those in Archive/Recovery documentation.
I am assuming this is only about Host Utility (HUT) Locks
The "RELEASE LOCK FORCED" seems to be more of a before the fact option and as the manual says isn't a guarantee
depending on the situation it may nicer to figure out what objects have locks by doing a CNSTERM 6 > START SHOWLOCKS > then flick over to the appropriate "window"
then with the same account that created the lock using either (using a different account requires higher privelages and use of the OVERRIDE option)
RELEASE LOCK (database_name)
RELEASE LOCK database_name
then check via CNSTERM again to confirm there are no outstanding locks
Both support the ALL option to hit a whole heirachy but I'd be a cautious/anxious about doing this against a whole system ie. (DBC) ALL - caveat emptor eg. doing this while a real backup/restore is occuring would be bad.
nb. when doing BUILDs in a seperate job I usually send the output to a file since it is easier to look at later, I don't tend to do this so much now since RDBMS 13.10 the BUILD process doesn't tend to be worth splitting off for most standard restores.