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 # |