Archives of the TeradataForum

Message Posted: Tue, 04 Apr 2006 @ 15:11:22 GMT

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

Hi Nazy,

Rows being inserted by TPump that share the same primary index value (the table has a NUPI defined) will cause a row hash lock of the same hash-ID. If the input rows are spread across multiple buffers which happen to be executing at the same time, this could cause deadlocks.

If SERIALIZE ON is specified, then all input records will be partitioned across all sessions defined. This guarantees both input record order and that all the records with the same primary index value will be handled in the same session, possibly the same buffer. The way SERIALIZE guarantees input order is to partition on the columns you have specified in your TPump script as KEY fields. Usually this will be the primary index of the table being updated, but it may be a different column or set of columns.

There are several impacts of using serialization, but the advantages usually outweigh these factors:

1. Some additional work is done by the client when partitioning the input

2. Buffer replenishment is spread out, making stale data a greater possibility

3. Performance may be impacted by sending a frequent number of partial buffers to the database when a checkpoint is taken.

Another consideration with serialize is that you can only serialize on one set of key fields. If you have a join index on the table being loaded, and you serialize on the base table NUPI, there is still a possibility of blocking on the join index NUPI.

Thanks, -Carrie

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