Show Hut lock and release lock in Teradata

Teradata put a HUT lock on objects when the database or table is being backed up and after successful backup HUT lock released by backup script. Sometime when the backup fails or aborted then HUT lock be there and query on those objects goes into blocking. Using show lock utility, we can see the lock on databases and objects and we can release the lock.

Showing an example how to check lock and release lock.

Check locks using Show lock Utility

# cnsterm 6

Attempting to connect to CNS...Completed.

Hello

Input Supervisor Command:

> start showlocks

start showlocks

Started 'showlocks' in window 1

at Thu Jul 5 15:15:37 2018

Input Supervisor Command:

> ^C

# cnsterm 1

Attempting to connect to CNS...Completed.

Hello

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

--PROXY LOCK (if applicable)

tmp_test_20171121

USER Arcuser MODE Excl AMP All Amps

tmp_test_20171121.FINANCIAL_PLAN

USER Arcuser MODE Excl AMP All Amps

--ShowLocks Processing Complete--

Release locks

# arcmain

logon XXX.XX.XXX.XXX/arcuser,Xxxxxx;

07/05/2018 15:23:35 LOGON XXX.XX.XXX.XXXX/arcuser,;

07/05/2018 15:23:35 LOGGED ON 3 SESSIONS

07/05/2018 15:23:35

07/05/2018 15:23:35 DBS LANGUAGE SUPPORT MODE Standard

07/05/2018 15:23:35 DBS RELEASE 15.10.07.11

07/05/2018 15:23:35 DBS VERSION 15.10.07.11

07/05/2018 15:23:35

07/05/2018 15:23:35 STATEMENT COMPLETED

07/05/2018 15:23:35

To release table level lock

Release lock (tmp_test_20171121.FINANCIAL_PLAN);

07/05/2018 15:24:11 Release lock (tmp_test_20171121.FINANCIAL_PLAN);

07/05/2018 15:24:11 "TMP_TEST_20171121"."FINANCIAL_PLAN" - LOCK RELEASED

07/05/2018 15:24:11 STATEMENT COMPLETED

07/05/2018 15:24:11

To release database level lock

Release lock (tmp_test_20171121);

07/05/2018 15:36:32 Release lock (tmp_test_20171121);

07/05/2018 15:36:32 "TMP_TEST_20171121" - LOCK RELEASED

07/05/2018 15:36:32 STATEMENT COMPLETED

07/05/2018 15:36:32

logoff;

07/05/2018 15:39:56 logoff;

07/05/2018 15:39:56 LOGGED OFF 3 SESSIONS

07/05/2018 15:39:56 STATEMENT COMPLETED

07/05/2018 15:39:56

07/05/2018 15:39:56 ARCMAIN TERMINATED WITH SEVERITY 0

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;

Check locks again using Show lock Utility after release lock

# cnsterm 6

Attempting to connect to CNS...Completed.

Hello

Input Supervisor Command:

> start showlocks

start showlocks

Started 'showlocks' in window 1

at Thu Jul 5 15:26:54 2018

Input Supervisor Command:

> ^C

# 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

--PROXY LOCK (if applicable)

There are currently no host utility locks in the DBC

--ShowLocks Processing Complete—