How to Call Jobs on Condition based in the Sequence

divine · Post by **divine** » Thu May 03, 2007 2:04 am

HI,
Good day to all.
I am having a problem while creating a new job. I run the entire job in a sequence and runs in a loop. The jobs are extracting the data from the database. Now my job will run if any data is present in the database. So if data is there then it will run. So to achieve this I am creating another job which will check whether any record is present or not. If present then it will run as usual.Then

So how to achieve this? How in the main sequence I can give condition to run the main job?

Any body can give some clue

Regards

Biswajit

nick.bond · Post by **nick.bond** » Thu May 03, 2007 2:14 am

A very useful routine for you to write is one that allows you to call sql from it. Then you can simply use this routine as derivation to a user variable in sequence running sql like

Code: Select all

select count(*) from myTable where mycondition

and use the result from this in the sequence to decide which path to take.

The writing of this routine depends on the database you are using and the platform you are on. I know how to write one for Oracle on Unix but wouldn't know for Windows off the top of my head.

ray.wurlod · Post by **ray.wurlod** » Thu May 03, 2007 3:09 am

Why bother? The job will work fine even if there are no rows in the table. It will process zero rows.

divine · Post by **divine** » Thu May 03, 2007 3:21 am

Dear Nick,
It is a vary good suggestion. I am also thinking of that, but I have no idea how to write this kind of routine in windows environment. So I am looking for some kind of tips for the experts who can help me.

Dear Ray,
What ever you have suggested, actually it is working like this. But the main problem is that our database is a shared one and ETL taking maximum resources which impacts other projects. So to avoid this I need to do the new approach. As here I'll have to run one query and on that basis I can execute my entire job.

Thanks and Regards
Biswajit

DSguru2B · Post by **DSguru2B** » Thu May 03, 2007 7:09 am

Create a job that does count(*) and loads to a file. This will be your first job activity, the second icon will be of execute command stage where you will read this file using type. Now depending on the count, branch your stream accordingly.

chulett · Post by **chulett** » Thu May 03, 2007 7:23 am

So what is your actual problem that you need help with? How to write the 'check the database' job? How to communicate what it found to the downstream jobs? How to build conditional triggers in a Sequence?

If you can be more specific as to what parts you have covered and which parts you need help with, you can get more specific answers.

ray.wurlod · Post by **ray.wurlod** » Thu May 03, 2007 4:26 pm

It takes the same connection resources to check whether there are any rows as to process zero rows. There is no gain.

divine · Post by **divine** » Thu May 03, 2007 11:07 pm

Hi All,
I need to write a routine which can call an oracle function. On the basis of the values get from the routine it will call different set of jobs.

Routine --------------Sequence1
|
|
|
|
Sequence2

So on the basis of the out come of the routine I can call either Seq1 or Seq2. But I am not comfortable how to call oracle function in a DataStage routine. If any one can guide me then it will be great help to me.

Thanks and Regards
Biswajit

ray.wurlod · Post by **ray.wurlod** » Thu May 03, 2007 11:44 pm

Search for "BCI". But you don't really need a routine. A job could fetch a calue from Oracle, probably easier to manage and to maintain. Load the retrieved value into the job's user status area and use that value to drive the triggers from the Job activity.

chulett · Post by **chulett** » Fri May 04, 2007 12:17 am

divine wrote:I need to write a routine which can call an oracle function.

No, you don't - nor would you really want to if you knew all that would be involved. Two fairly easy ways to consider:

1) Write a script to connect to sqlplus and run whatever query you think you need to check for data. Spool the output to a file. Then echo the contents to standard out so DataStage can pick it up easily. Run the script using the Execute Command stage and then check $CommandOutput in your triggers.

2) Write a simple DataStage job to do the check and store the results in USERSTATUS. You'll need a custom routine that is a simple interlude to the DSSetUserStatus function but it has been posted here a number of times. Then check $UserStatus in your triggers.

I'm sure there's others. Both of these approaches have been discussed before and should turn up in searches.

divine · Post by **divine** » Fri May 04, 2007 1:48 am

Dear Ray,
Thanks a lot for your suggestion. Can you please explain me how I need to use the value ? But the requirement is that first I need to chek the value in one table and if the value is set then I run my usual jobs esle I need to run another job. For this I have gibven my approach where I can create a routine to check for that and on that basis I can execute my job.

I do not have any idea how to connect to to oracle database and call the function in the routine. I am not familiar with wrting the routines.
Can any one help me.

Regards

ray.wurlod · Post by **ray.wurlod** » Fri May 04, 2007 2:29 am

All the more reason not to use a routine.

Use a job to retrieve the value from Oracle.
That job puts the value into its user status area (search the forum for techniques).
The triggers from the Job activity are custom triggers that test the value of that user status area.

chulett · Post by **chulett** » Fri May 04, 2007 8:14 am

Again, you do not want nor need to do this in a routine. That would require some low-level programming using the BCI functions Ray mentioned, the purchase of drivers to leverage and some serious tutoring. Put it out of your mind.

Use the tool as it was meant to be used. Write a job to do this, the problem would have been solved by now if you had.

ray.wurlod · Post by **ray.wurlod** » Fri May 04, 2007 3:35 pm

Because the OP is on Windows, the Data Direct driver issues are more readily worked around by using different drivers. But everything I posted earlier remains apposite.

chulett · Post by **chulett** » Fri May 04, 2007 5:49 pm

Ah... I always seem to forget about the Windows angle on the Data Direct drivers. Still... don't use a routine.