constraint voilation

[sumeet]

I am trying to insert based on primary key lookup.

those keys are formed this way: in table2
[b]origin key[/b]
01|A4BZ ----->12345
01|A222 ------>67890


now getting back to the job

when i lookup the table2.origin with table1.origin that key and the corresponding row gets inserted. but the problem is coming when
table1.origin repeats again and same key is passed onto the insert .

this causes the constraint voilation and the second record is not inserted.

how do i insert the second record without constraint voilation

[chulett]

You can't or you would violate the constraint, as you've seen.

But you can do a couple of different things with the second occurance of the primary key - 1) Update rather than insert or 2) throw the record away. That's something you would have to decide.

I am assuming that you are doing a hashed lookup to determine if a primary key value should be inserted or not. When the lookup fails, you insert. When the lookup succeeds, you need to decide what is appropriate then. However, in order for that second lookup to succeed you need to be writing any failed lookup surrogate keys to the lookup hashed file so next time it won't fail. Do this by dropping a second hashed file stage on the canvas with the same hashed file name and metadata you are using for the lookup. When the lookup fails, write a row there.

This will also mean you either need to disable the 'Preload to memory' option on your lookup hashed stage, either that or use the 'Enabled, locked for updates' option.

[ray.wurlod]

You are trying to update the hashed file in real time. That's OK, but it means that you can't use cache - the hashed file is cached for reading before the job starts processing rows, and isn't updated thereafter.

It also means you need two separate Hashed File stages, though they refer to the same hashed file. This is because a passive stage can not open its outputs until its inputs have closed.

Your design must look like this:

Code: Select all

         HFstage1       HFstage2
           |               ^
           |               |
           v               |
      ---> TransformerStage0 --->

Add an Annotation to the job design warning future maintainers that both Hashed File stages refer to the same hashed file.

HFstage1 must set pre-load to memory to "Disabled, lock for updates", while HFstage2 must set write cache to disabled (clear the check box).