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:
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--
^C
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
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—
^C
#