SHOW LOCKS UTILITY

Share via:

SHOW LOCKS UTILITY

Dear Readers,

In this article , we will see show locks utility in Teradata.

What is SHOWLOCKS ?

When a table or database is being backed up, it places a HUT lock on the object, which is released when it finishes backup successfully. But sometimes when a backup fails or is aborted, HUT lock is still present on the objects involved and query on those objects goes into blocking.  So, one way of find and release lock on those objects is to login into one of the nodes and use show lock utility.

  • The Show Locks utility, showlocks, provides information about Host Utility (HUT) locks placed on databases and tables by the Archive/Recovery (ARC) utility during database operations.
  • Show Locks also displays information about locks placed during a Teradata Database system failure.
  • Lock information can also be monitored and displayed using the Lock Viewer portlet in Viewpoint.

 

What information is displayed by SHOW LOCKS ?

The Show Locks utility display provides :

  • Summary of the showlocks function
  • Name of the databases, tables, and hash IDs on which locks are placed
  • Username that placed each lock
  • Lock mode: read, write, exclusive, or access
  • Identification numbers of the AMPs on which the locked database or table resides
  • Job identifier (only for DSA archive/recovery operations)
  • Proxy table locks, which enable sequential locking on all-AMP table locks

Note : Show Locks reports All AMPs rather than individual AMP numbers when the locked database or table resides on all AMPs. Information is provided for only the most restrictive lock a user has placed on an object.

  • When no locks are found, Showlocks shows the below message:

There are currently no host utility locks in the DBS.

  • If a host utility lock that conflicts with Show Locks is in place when showlocks is executed, the Teradata Database system displays this message:

’Unable to proceed due to xxxx lock on yyyy’

Where

xxxx : refers to either a Write or Exclusive lock

yyyy : refers to DBC, DBC.TVM, or DBC.DBase.

After reporting a conflict, Show Locks terminates.

From where to run the utility ?

  • Database Window or comparable interface to the Teradata Database console subsystem, such as cnsterm
  • Teradata Viewpoint Remote Console portlet
  • Host Utility Console

Let us see how to start show locks utility from cnsterm.

Step 1 : Start cnsterm by typing “cnsterm 6” on PDN node

Step 2 : Go to the respective window number as seen from above.

Case 1 : If there are no locks present

# cnsterm 1

Attempting to connect to CNS…Completed.

Hello

This program queries all AMPs and reports all

Host Utility locks which currently exist at both

the data base level and the table level.

For each lock which is found, an entry will

appear on your console which includes the following

information:

–Data Base Name

–Table Name (if applicable)

–HashId (if applicable)

–User Name of user who placed lock

–Lock Mode

–AMP Number

–JobId of DSA Job (if applicable)

–PROXY LOCK (if applicable)

There are currently no host utility locks in the DBC

–ShowLocks Processing Complete—

Case 2 : If locks are present

–ShowLocks Processing Complete—

Now, once we get the list of objects having HUT lock, we need to issue release lock command on those objects by the user who placed the HUT lock or a user who has DROP DATABASE privilege on the database.(e.g. DBC)

Step 3: Release lock

 

*** Command accepted.

*** Total elapsed time was 1 second.

*** Command accepted.

*** Total elapsed time was 1 second.

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

 

 

 

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment