Archives of the TeradataForum

Message Posted: Wed, 23 Jun 2011 @ 00:56:58 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

The work table stores the rows that are sent during the acquisition phase to the AMPs. The worktable will have the same primary index as the target table, so by loading the rows there, Multiload is preparing itself to go into the application phase where it starts applying the updates from the work table to the target table. As they are applied, they are deleted from the work table.

When you refer to an update and delete, you could either be referring to updates and deletes that are done as part of the "UPDATE" task or you could be referring to the "UPDATE" task in Multiload vs. the "DELETE" task in Multiload.

When doing an UPDATE task in Multiload, you feed data into Multiload at the primary index level or below. Each row in your file must contain the primary index value (it may contain something that makes it more unique than the primary index). So, the worktable will contain the records that you load and then use those rows to do the updates or deletes against the target table during the application phase.

When doing a DELETE task in Multiload, there is no need for a worktable. In a DELETE task, you can bring in one record that may contain field values that you feed to your DELETE statement (the record is not required). The purpose of having a DELETE in Multiload is so that Multiload does not write the deleted rows to the transient journal. This saves a significant amount of time. So, in a Multiload DELETE task, the DELETE just starts deleting rows from the table. If something interrupts the DELETE (like a restart), when you restart your Multiload, it will just start deleting the rows again based on the criteria specified. It can do this because it knows it has a write lock on the table that it won't release until the application phase is complete. This is unlike a regular SQL delete, which would have to rollback the deletes if a restart occurred during the delete or the transaction was cancelled.

Hope that helps.

Thanks,

Barry

	<Prev	Next>		<<First	<Prev	Next>	Last>>