Initial Loading and subsequent loads in one job

Jamesvanam · Post by **Jamesvanam** » Fri Sep 24, 2004 9:11 am

The is my job:

LKP_For_T14
|
|
|
Src_ORA --------------> XFM---------->T14

I'm loading T14 and I need to do a initial load and loads after that, Like when I run it first time I don't have any records in the table T14. and The Constraint in the Transformer is : T14.Column1 <> Src_ORA.Column3 then load that record. and also I've a foreign key in T14 which is Primary key in T13 and was wondering if these could loaded in the same job.
right now I've two jobs, First one loads T13 then I load the T14.

how can I do this. Please help me.
Regards
James

ray.wurlod · Post by **ray.wurlod** » Fri Sep 24, 2004 5:34 pm

You're already following best practice by having two jobs. Why would you want to change?

It can be done. It relies on the fact that a passive stage type can not open its outputs until all its inputs are closed. The design is below, but I'd still strongly counsel you to maintain two separate jobs; if you only want to run one job, create a Sequence to run the one then the other (in which Sequence you can detect successful completion of the first), and run the Job Sequence.

Code: Select all

     T14  ------>  xfm1  ----->  HashedFile
                                    |
     source  --------------------> xfm2  ----->  T14 (inserts)
                                         ----->  T14 (updates)

Rows are sent to the (inserts) link if the lookup fails, to the (updates) link if the lookup succeeds.

Performance Tip
Load as few rows and columns as you need into the hashed file. If you're only interested in detection (not change) only load the key values. If you need to map natural to surrogate keys, load only these columns. And so on. If there are versioned rows in T14, load only the current versions.

Jamesvanam · Post by **Jamesvanam** » Mon Sep 27, 2004 11:17 am

Thank you Ray. I'm gonna use the Sequencer. but there is one more thing I need help with, I'm trying to dynamically update the HashedFile_T14 from the XFM. but it doesn't let me do it, when I try to compile, it says "Job contains cyclic or linear dependencies and will not run" any idea as to what I can do.
and also (alternate way) I tried to update the HashedFile from the target stage(ORA_T14) by having the Transaction size and array size as one and also having an output link from this Target stage(ORA_T14) to the HashedFile_T14.but my job hangs when I do this, I mean every stage is waiting for other stage to be completed its all yellow color links. any suggestions on how could I resolve this.
Regards
James

Jamesvanam · Post by **Jamesvanam** » Mon Sep 27, 2004 11:55 am

HI Ray,
I just added one more Transformer to the design, I mean a second transformer which updates the HashedFile_T14. It works ok.
Thank
James