Delete data conditionally in the target file/table

[SonShe]

I have job to extract data from the source to a stage1 table. My need is not to delete the data in the target if there is no data in the source to extract from. The job has the following simple design:

SeqFile ------> Transformer------------>Oracle Table

I extract data incrementally from the source, truncate the target oracle table and then load the data. If there is no source data on any day, then I should not truncate the tartget table so that I can preserve the prior data to process again.

If there is any way I can stop the outgoing link execution. I will appreciate any help.

Thanks.

[chulett]

You can't use the 'truncate' option if you don't want it to truncate on every run, regardless of how many records are in your source. From what I recall, it truncates the table when the stage starts up and connects to the target, before it starts to process any rows.

You could write a small 'before job' routine (or script) that checks how many rows are in your source file (using wc -l) and issues the truncate command on the target table if that number is greater than zero. I've done this before and it's pretty easy to setup a generic version that takes parameters for all the vital bits so you can use it in other jobs as well.

Or maybe try the 'clear table' update action? I have vague memories that the transactional delete may not be triggered until the first row is processed, but I could be wrong about that. Shouldn't be hard to rig up a quick test.

[elavenil]

Truncate table cannot be used as it truncates the entire table. Would recommed to have two jobs one for delete and one for insert. Delete jobs should be run before running insert job. This would help you clearing the records if you have records in the source.

HTWH.

Regards
Saravanan

[info_ds]

Hi,

I have tried using Routine and Sequence,it is working fine.

1.Create a Job to load your Data.

Source file ----------->Transformer --------------->Oracle Stage

In Oracle stage in Update action option give clear table then Insert Rows.

2.Create a Routine say "ROWCOUNT" to find the no.of.rows of the source file.

3.Create a sequence

Routine Activity ------------->Job Activity.

In routine activity call "ROWCOUNT" get Return value and set Expression condition > 0.


Is there any other alternative.


Thanks
A.S

[chulett]

Well, I did give an alternative way to handle it as one job with a 'before job' script.

Your approach with the Sequencer is perfectly valid as well, one that skips running the job unless there are rows to process. One thing to keep in mind is that any routine that has a 'non-zero' return will be considered to have failed if you turn on 'Automatically handle activities that fail' in the Sequencer job. Not necessarily a big deal, just something to be aware of.

I prefer to keep stuff like this in one job so that it can scheduled and it can run every time without having to do anything special. It doesn't hurt to run a job that sources from an empty flat file, as a matter of fact they should process it rather quickly without throwing any errors. The only 'problem' in this scenario is conditionally truncating the target table.

[SonShe]

Thank you both for your suggestions. Now I know how to jandle this.