All Forums Tools
AmarnathG 16 posts Joined 10/12
21 Nov 2012
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

 

AmarnathG
feinholz 1234 posts Joined 05/08
21 Nov 2012

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

AmarnathG 16 posts Joined 10/12
21 Nov 2012

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

 

AmarnathG

katakoti 21 posts Joined 11/12
23 Nov 2012

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

feinholz 1234 posts Joined 05/08
26 Nov 2012

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

Fred 1096 posts Joined 08/04
01 Dec 2012

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. 

You must sign in to leave a comment.