Same Table as Source and Target

[ReachKumar]

Hi,

Can we use same table as source and target.

Job : Db2stage and lookup file -> Transformer -> DB2stage

Thanks in advance.

[srinivas.g]

Yes ........you can use same table as a source and target.

[grimm336]

Yes you can do that.....But you should use intermediate files or tables for streaming stages

[ray.wurlod]

Such blocking operations are not permitted in parallel jobs. Grimm's solution would require two jobs.

You can use the same table as source and target but you need to make sure that self-deadlocking does not occur, typically by using a transaction size of 1 and an array size of 1 and partitioning on the table key (so that the same key can not arrive into the table from more than one node, which might also cause a locking issue).

[ReachKumar]

So its better to avoid to have the same table as source and target.

If my requirement is truncate and load, then i think its not possible to use same table as source and target.

Am i correct?

[chulett]

If you stopped and thought about it for a moment, you'd realize the answer is that "it is not possible" in that scenario.

[ShaneMuir]

What if the source DB2 stage was infact the lookup? Depending on the job requirements, if you were to read the source data into memory first and used the sequential stage as the primary link. Or does it not actually load the lookup data into memory first?

[ray.wurlod]

Good thinking, and it may work but it may not. Certainly with a normal reference link the reference data set is completely loaded into memory and an index built before the first lookup is attempted, which is the situation where it may work, provided the database does not continue to hold any lock until the connection is closed.

[ReachKumar]

Informative.. Thanks