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

Teradata® MultiLoad Reference - 20.00

Release Number
October 2023
English (United States)
Last Update
Product Category
Teradata Tools and Utilities

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

Topic Usage Notes
Locks To release the target tables, the Teradata MultiLoad 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 a Teradata MultiLoad write lock has been placed on any of the specified tables.

Teradata MultiLoad 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 Teradata MultiLoad 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 a Teradata MultiLoad 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 Teradata MultiLoad changes the application lock to a restoration lock that prevents Teradata MultiLoad from restarting and allows the following table accesses:

  • 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 MultiLoad 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 MultiLoad script, if needed.
  5. Run the MultiLoad 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 a Teradata MultiLoad task. If a specified table is not involved in a Teradata MultiLoad task, the database rejects the RELEASE MLOAD statement.