Mload table locks cannot be released because NUSI exists

Tools
Enthusiast

Mload table locks cannot be released because NUSI exists

Hi All,

I had  a problem when i executed mload script.

My table has NUPI on some coloumns.

The mload script has got failed  and When i try to release the lock on mload target tabel

--7446 Mload table %TVMID cannot be released because NUSI exists.

then, i drooped NUSI coloums on table then released mload lock on table then reexecuted mload script from begining.it got succeeded.

My Question is--

Why mload lock not released when table having NUPI.Could you please explain in detail.

Thanks,

Amarnath

5 REPLIES
Teradata Employee

Re: Mload table locks cannot be released because NUSI exists

According to the DBS engineer:

Here is the explanation of error 7446. We process the primary data, the fallback data, and the NUSI data separately in the application phase. If there is an abort during this phase, the data between these sub-tables can be inconsistent. Allowing a ‘release mload’ to run can cause data integrity issue. The user should normally choose to restart and let the job complete. If for some reason, that cannot be done, it is required to drop any NUSI and fallback.

Explanation:

The user attempted to release an Mload Apply Phase or Restoration Lock on a table that has non-unique secondary indexes. The table had been the target of an Mload that was aborted in the Apply Phase. There may be internal inconsistencies between the primary and its non-unique secondary indexes because not all inputs may have been applied to the target table.

-- SteveF
Enthusiast

Re: Mload table locks cannot be released because NUSI exists

Hi feinholz,

Thanks a lot for the post!!

Could you please explain what are the possible "internal inconsistencies between the primary and its non-unique secondary indexes". I am a bit confused here.

Thanks&Regards,

AmarnathG

Enthusiast

Re: Mload table locks cannot be released because NUSI exists

It would be graet if you explain the point mentioned above...( "internal inconsistencies between the primary and its non-unique secondary indexes")

Thanks,

Koti

Teradata Employee

Re: Mload table locks cannot be released because NUSI exists

I was just forwarding the information from the DBS engineer. I do not work in the DBS organization and thus do not have that information. You will have to consult with database engineers to obtain that specific explanation.

The explanation may not be important to the user. The bottom line is that the table locks cannot be removed in that particular scenario.

-- SteveF
Teradata Employee

Re: Mload table locks cannot be released because NUSI exists

In normal SQL operations, whenever a change is made to a row in the table, any corresponding update to secondary indexes or join indexes happens immediately. MultiLoad does not work that way.

Instead, as table rows are updated, any necessary updates for NUSIs are logged to the work table. Each AMP applies the NUSI updates only after all table updates are complete. So if you abort in the middle of the Apply phase, the NUSIs will not be consistent with the table.

RELEASE MLOAD IN APPLY is intended to be a recovery of last resort and should not be normal practice. Some changes (but not necessarily all) will have already been applied to the table, and cannot be rolled back. Any NUSIs must be explicitly dropped.