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;

Comments