Release MLOAD in Teradata
In the following example the following steps perform to release mload:
· Check which tables have the lock in database
· Release Mload on the marked tables
· Drop the temporary table
· Check again Lock release or not
Check which tables have the lock in database:
In that example we are checking the lock on ITAxCRMBI table.
# cnsterm 6
Attempting to connect to CNS...Completed.
Hello
Input Supervisor Command:
> start checktable
start checktable
Started 'checktable' in window 1
at Tue Feb 5 08:54:20 2019
Input Supervisor Command:
> ^C
# cnsterm 1
Attempting to connect to CNS...Completed.
Hello
_______
| | |
| ___ __ ____ | ____ __|__ ____
| / |/ \ ____| ____| ____| | ____|
| --- | / | / | / | | / |
| \___ | \____| \____| \____| |__ \____|
Release 16.20.24.01 Version 16.20.24.01
CHECK TABLE Utility (April 08)
CHECKTABLE will run in Workload Def: WD-Default
Enter a command, "QUIT;", "HELP;" or F7 for interactive help:
> CHECK ITAxCRMBI AT LEVEL PENDINGOP;
CHECK ITAxCRMBI AT LEVEL PENDINGOP;
Checktable defaults to concurrent mode
on non-quiescent system with logons enabled.
Check beginning at 08:54:39 19/02/05.
CHECKTABLE will run from Host: 0 Session: 78,17662
Table: "ITAXCRMBI"."ET_W_LOY_ACCRUAL_ITEM_FS" checking at 08:54:39 19/02/05.
Map No 1025, Table id 00DBH B2B9H, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
Table: "ITAXCRMBI"."ET_W_LOY_MEMBER_DS" checking at 08:54:39 19/02/05.
Map No 1025, Table id 00DBH B6EEH, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
Table: "ITAXCRMBI"."ET_W_LOY_REDEMPTION_ITEM_FS" checking at 08:54:39 19/02/05.
Map No 1025, Table id 00DBH B2B6H, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
>
Table: "ITAXCRMBI"."UV_W_LOY_ACCRUAL_ITEM_FS" checking at 08:54:41 19/02/05.
Map No 1025, Table id 00DBH B2BAH, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
Table: "ITAXCRMBI"."UV_W_LOY_MEMBER_DS" checking at 08:54:41 19/02/05.
Map No 1025, Table id 00DBH B6EFH, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
Table: "ITAXCRMBI"."UV_W_LOY_REDEMPTION_ITEM_FS" checking at 08:54:41 19/02/05.
Map No 1025, Table id 00DBH B2B7H, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
Table: "ITAXCRMBI"."WT_W_LOY_ACCRUAL_ITEM_FS" checking at 08:54:45 19/02/05.
Map No 1025, Table id 00DBH B2B8H, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
Table: "ITAXCRMBI"."WT_W_LOY_MEMBER_DS" checking at 08:54:46 19/02/05.
Map No 1025, Table id 00DBH B6EDH, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
Table: "ITAXCRMBI"."WT_W_LOY_REDEMPTION_ITEM_FS" checking at 08:54:46 19/02/05.
Map No 1025, Table id 00DBH B2B5H, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
"ITAXCRMBI"."W_AREA_DS" was just processed. Processed 100 tables till now.
"ITAXCRMBI"."W_ETL_RUN_S" was just processed. Processed 200 tables till now.
"ITAXCRMBI"."W_KPI_FX" was just processed. Processed 300 tables till now.
Table: "ITAXCRMBI"."W_LOY_ACCRUAL_ITEM_FS" checking at 08:55:10 19/02/05.
Map No 1025, Table id 0034H B84FH, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
Table: "ITAXCRMBI"."W_LOY_MEMBER_DS" checking at 08:55:11 19/02/05.
Map No 1025, Table id 0034H B858H, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
Table: "ITAXCRMBI"."W_LOY_REDEMPTION_ITEM_FS" checking at 08:55:15 19/02/05.
Map No 1025, Table id 0034H B878H, Database id 0000H 0733H, Fallback.
Table check bypassed due to pending MultiLoad.
Table Result: Skipped
"ITAXCRMBI"."W_MKTG_COST_F" was just processed. Processed 400 tables till now.
"ITAXCRMBI"."W_PERFORMANCE_FX" was just processed. Processed 500 tables till now.
"ITAXCRMBI"."W_REGN_DH" was just processed. Processed 600 tables till now.
"ITAXCRMBI"."W_SSTAGE_DS" was just processed. Processed 700 tables till now.
"ITAXCRMBI"."W_WEEK_D" was just processed. Processed 800 tables till now.
Summary:
810 table(s) checked.
810 fallback table(s) checked.
0 non-fallback table(s) checked.
12 table(s) bypassed due to pending MultiLoad.
0 table(s) failed the check.
Check completed at 08:56:01 19/02/05.
Error file is '/var/opt/teradata/tdtemp/CheckTableErrors20190205085439'.
Enter a command, "QUIT;", "HELP;" or F7 for interactive help:
> ^C
We can see the marked red table lock need to release and rest of the tables are temporary table created for Mload. We need to release the lock on red marked table and drop temporary tables.
Release Mload on the red marked tables:
# bteq
Teradata BTEQ 16.20.00.01 for LINUX. PID: 27276
Copyright 1984-2017, Teradata Corporation. ALL RIGHTS RESERVED.
Enter your logon or BTEQ command:
.logon userx
.logon userx
Password:
*** Logon successfully completed.
*** Teradata Database Release is 16.20.24.01
*** Teradata Database Version is 16.20.24.01
*** Transaction Semantics are BTET.
*** Session Character Set Name is 'ASCII'.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command:
RELEASE MLOAD ITAXCRMBI.W_LOY_ACCRUAL_ITEM_FS;
RELEASE MLOAD ITAXCRMBI.W_LOY_ACCRUAL_ITEM_FS;
*** Mload has been released.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command:
RELEASE MLOAD ITAXCRMBI. W_LOY_MEMBER_DS;
RELEASE MLOAD ITAXCRMBI. W_LOY_MEMBER_DS;
*** Mload has been released.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command:
RELEASE MLOAD ITAXCRMBI.W_LOY_REDEMPTION_ITEM_FS;
RELEASE MLOAD ITAXCRMBI.W_LOY_REDEMPTION_ITEM_FS;
*** Mload has been released.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command:
Drop the temporary table:
*** Mload has been released.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command:
SELECT
databasename
,tablename
,'DROP TABLE ' || TRIM(Databasename) || '.' || TRIM(Tablename) || ';' AS ExecutionSQL
FROM
DBC.tablesv
WHERE
databasename='ITAXCRMBI' AND
tablename like '%W_LOY_ACCRUAL_ITEM_FS' AND
tablename <> 'W_LOY_ACCRUAL_ITEM_FS' ;
Execute the DROP Statement:
DROP TABLE ITAxCRMBI.ET_W_LOY_ACCRUAL_ITEM_FS;
DROP TABLE ITAxCRMBI.ML_W_LOY_ACCRUAL_ITEM_FS;
DROP TABLE ITAxCRMBI.UV_W_LOY_ACCRUAL_ITEM_FS;
DROP TABLE ITAxCRMBI.WT_W_LOY_ACCRUAL_ITEM_FS;
SELECT
databasename
,tablename
,'DROP TABLE ' || TRIM(Databasename) || '.' || TRIM(Tablename) || ';' AS ExecutionSQL
FROM
DBC.tablesv
WHERE
databasename='ITAXCRMBI' AND
tablename like '%W_LOY_REDEMPTION_ITEM_FS' AND
tablename <> 'W_LOY_REDEMPTION_ITEM_FS ';
Execute the DROP Statement:
DROP TABLE ITAxCRMBI.UV_W_LOY_REDEMPTION_ITEM_FS;
DROP TABLE ITAxCRMBI.WT_W_LOY_REDEMPTION_ITEM_FS;
DROP TABLE ITAxCRMBI.ET_W_LOY_REDEMPTION_ITEM_FS;
DROP TABLE ITAxCRMBI.ML_W_LOY_REDEMPTION_ITEM_FS;
SELECT
databasename
,tablename
,'DROP TABLE ' || TRIM(Databasename) || '.' || TRIM(Tablename) || ';' AS ExecutionSQL
FROM
DBC.tablesv
WHERE
databasename='ITAXCRMBI' AND
tablename like '%W_LOY_MEMBER_DS' AND
tablename <> 'W_LOY_MEMBER_DS' ;
Execute the DROP Statement:
DROP TABLE ITAxCRMBI.WT_W_LOY_MEMBER_DS;
DROP TABLE ITAxCRMBI.ET_W_LOY_MEMBER_DS;
DROP TABLE ITAxCRMBI.ML_W_LOY_MEMBER_DS;
DROP TABLE ITAxCRMBI.UV_W_LOY_MEMBER_DS;
Check again Lock release or not:
# cnsterm 1
Attempting to connect to CNS...Completed.
Hello
_______
| | |
| ___ __ ____ | ____ __|__ ____
| / |/ \ ____| ____| ____| | ____|
| --- | / | / | / | | / |
| \___ | \____| \____| \____| |__ \____|
Release 16.20.24.01 Version 16.20.24.01
CHECK TABLE Utility (April 08)
CHECKTABLE will run in Workload Def: WD-Default
Enter a command, "QUIT;", "HELP;" or F7 for interactive help:
> CHECK AGAIN /var/opt/teradata/tdtemp/CheckTableErrors20190205085439 AT LEVEL PENDINGOP;
CHECK AGAIN /var/opt/teradata/tdtemp/CheckTableErrors20190205085439 AT LEVEL PENDINGOP;
Checktable defaults to concurrent mode
on non-quiescent system with logons enabled.
Check beginning at 09:40:26 19/02/05.
CHECKTABLE will run from Host: 0 Session: 80,13684
Summary:
3 table(s) checked.
3 fallback table(s) checked.
0 non-fallback table(s) checked.
0 table(s) failed the check.
Check completed at 09:40:26 19/02/05.
Enter a command, "QUIT;", "HELP;" or F7 for interactive help:
>
Additional Note:
Use IN APPLY in the following condition to release MLOAD
IN APPLY
The Upload operator task that was in the application phase when it was interrupted.
Note: The IN APPLY option can not be used to release a lock placed on the update operator's target table during the capture phase. In this case, use the RELEASE MLOAD statement without specifying the IN APPLY option.
Example:
RELEASE MLOAD ITAXCRMBI.W_LOY_REDEMPTION_ITEM_FS;