Usage Notes of RELEASE MLOAD| Teradata Parallel Transporter User Guide - Usage Notes - Parallel Transporter

Teradata® Parallel Transporter User Guide - 17.20
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Parallel Transporter
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-08-25
dita:mapPath
uzp1645128359760.ditamap
dita:ditavalPath
tvt1507315030722.ditaval
dita:id
B035-2445
Product Category
Teradata Tools and Utilities

The following table describes the things to consider when using the RELEASE MLOAD statement.

RELEASE MLOAD Usage Notes 
Topic Usage Notes
Locks To release the target tables, the release function must first obtain an exclusive lock on each specified table. This is not possible, and the RELEASE MLOAD statement will fail if either a database lock or an Update operator write lock has been placed on any of the specified tables.

An Update operator normally places write locks on the target tables:

  • Near the end of the acquisition phase of an import task, when data acquisition is complete and the data sort operation is about to begin
  • During the preliminary phase of a delete task when the DELETE statement is sent to the database

If the RELEASE MLOAD statement is executed before the Update operator task reaches these critical points, the release function completes before the utility can place write locks on the target tables.
Messages Returned A release completed message indicates that all of the specified tables were releasable and the release function completed without error.

If the release function encounters a table that cannot be released, it terminates with an error message identifying the table and the reason it could not be released.

Acquisition phase error conditions include:
  • Table does not exist
  • Table has some other lock
  • Table is not an Update Operator target table
  • The requesting user does not have the required privilege
    Application phase error conditions include all of the acquisition phase error conditions plus:
    • Table has fallback
    • Table has a NUSI
    • Table has permanent journals
    • Table is not in the application phase
    In this case, either:
    • Execute another RELEASE MLOAD statement, specifying only the tables that were releasable
    • Take corrective action, based on the error condition, then execute another RELEASE MLOAD statement
Releasing Application Locks The IN APPLY option of the RELEASE MLOAD statement releases the application locks on the target tables that satisfy one of the following conditions:
  • No changes have been made to the table
  • The table is empty and has no permanent journals
  • The table has no fallback, no NUSIs, and no permanent journals

If none of these conditions is true, and the table has no permanent journals, then the Update operator changes the application lock to a restoration lock that prevents the Update operator from restarting and allows the following table accesses:

  • DELETE ALL
  • DROP FALLBACK
  • DROP INDEX
  • DROP TABLE
  • SELECT with access lock
If permanent journals are defined on the table, the only option is to drop the target table. If the target table belongs to a replication group and change data capture is active (for example, the replication group status is not Defined nor Terminated), then the following steps must be completed to drop the table:
  1. Put the replication group in Suspended status.
  2. Use the ALTER REPLICATION GROUP statement to remove the table from the replication group.
  3. Drop the table.
Releasing Application Locks when the target table has fallback Follow these steps to release MLOAD locks entirely.
  1. Use the RELEASE MLOAD <table_name > IN APPLY statement to change the application locks to the restoration lock.
    • This query returns: Failure 7745 Mload table table_name cannot be released because fallback exists.
    • However, the application lock does get changed to restoration lock.
  2. Use DELETE <table_name> ALL to delete all of the rows in the table.
  3. Use RELEASE MLOAD < table_name > IN APPLY to free the table.

    This query removes the MLOAD lock entirely. The table is freed from this point.

  4. Fix the Update operator script, if needed.
  5. Run the Update operator job from the beginning.
Required Privilege Only the owner of the database or a user with one of the following privileges on the specified tables can use the RELEASE MLOAD statement:
  • Insert
  • Update
  • Delete
Table Requirements All of the tables that specified must have been involved in an Update operator task. If a specified table is not involved in an Update operator task, the database rejects the RELEASE MLOAD statement.