Wednesday, November 20, 2013

Release Hut-Lock in Teradata database

Hut-Lock is placed on table or database by Teradata where there is archive/restore operation is being performed on table/database, ideally the archive/resotre/copy script should include “release lock” command at the end of script to release lock once the operation is complete. But in case if backup script did not mention “release lock” clause or backup job failed with abnormal termination the lock is not release automatically.
In this case we need to release lock manually; we can release Hut- lock in two ways 1. By using arcmain  or by using SQL Assistant/bteq.

Using arcmain to release lock.

Using command prompt or Linux prompt, enter below command.

      > arcmain
         Logon serverip/arcuser1,arcuserpassword;
         Release lock (databasename.tablename);       à to release table level lock.


         Release lock (databasename);         à to release database level lock.

Note: In both the cases Teradata expect to use same user (arcuser1) which has put Hut-Lock, if not you can use super                    user with “override” option.
              i.e. Release lock (databasename.tablename), override;

Using Sql Assistant/bteq to release lock.

        1. Logon to SQL Assistant using same arcuser1, which accuire lock.
        2.   release lock(databasename.tablename);


Host Utility (HUT) Lock is a lock that Teradata ARC utility places when ARC command like ARCHIVE/RESTORE etc. are executed on database or objects with below condition apply.

     1. HUT locks are associated with the currently logged-on user who entered the statement,
         not with a job or transaction.
     2. HUT locks are placed only on the AMPs that are participating in a Teradata ARC
     3. A HUT lock that is placed for a user on an object at one level never conflicts with another
         level of lock on the same object for the same user.

We can check the Hut-lock in Teradata database using the ShowLocks Console utility.

Friday, November 15, 2013

Teradata constraints type

Teradata has various types of explicit or implicit constraints to support object relationship and Teradata specific data validation constraints.

The type of table-level check constraint or partitioning constraint (formerly referred to as indexes).

constraints  types:
 C = Explicit table-level constraint check
 P = Non-partitioned Primary Index
 Q = Partitioning constraint
 S = Hash-Ordered Secondary Index without ALL
 K = Primary Key
 U = Unique constraint
 R = References constraint
 V = Value-Ordered Secondary Index without ALL
 H = Hash-Ordered Secondary Index with ALL

 O = Value-Ordered Secondary with ALL