I am a bit confused on how Mload works in acquisition and application phase( how data moves,matching sequence numbers. One of my friend guided me that all the inserts, updates,deletes are done on worktables (acquisition phase) and finally they are just applied to the target table (application phase). Can you please elucidate on this.
Will the DML and data rows are concatenated or they are just compared during processing based on some conditions? Also, how does the same row that is being affected by multiple DML's handled in Mload.
I have been dwindling to get some categorical information on all these, but not to picture perfect. Thank you for your time on this.
in the Aqcuisition phase the rows are loaded (similar to a FastLoad) into the worktable, one row for each APPLY WHERE.
At the end of this phase the worktable is sorted by the so-called "match tag" columns (automatically added) which ensures that all modifications are done in the correct order based on input sequence number.
Both target and work table have the same PI and during the application phase all those modifications are merged into the target using a sequential scan on both tables.
So your friend was not really right, the data for the inserts/updates/deletes are recorded in the worktable, but they're actually done on the target rows.
If you read the MLoad manual there shoud be more detailed info.