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.
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.
It would be graet if you explain the point mentioned above...( "internal inconsistencies between the primary and its non-unique secondary indexes")
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.
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.