Automating release lock after database copy failure


Automating release lock after database copy failure

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? 


Tags (1)
Junior Supporter

Re: Automating release lock after database copy failure

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.

Abhishek Jadhav
Teradata Employee

Re: Automating release lock after database copy failure


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)

- arc

RELEASE LOCK (database_name)

- bteq

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.