DSXchange

Hi !

I have a load job that should be triggered when a row appears on the control Table.

I am trying to write a control job which will call the load job.

I am stuck at this place and I donot know how to go about this. The control job should monitor the control table (for the presence of row) every 15 minutes (for a maximum of 10 hrs). If the row is present then the LoadJob should be called.. If the row is not present.. The job should sleep for 15 minutes and then repeat the process again.

I am getting stuck at

1.How to find out if the row is present or not and inform the job sequence to proceed with the load job.

2. How to end the looping activity once the load job is triggered.


Any thoughts?

- Nagesh

How comfortable are you with writing your own Job Control? This would be pretty straight-forward if you are but you may be able to do something in the GUI with the new Start/End Loop constructs as well.

You could create a simple job that sources that row from the table in question. It doesn't really need to do much with it, perhaps even just write it to a sequential file somewhere. Then a custom routine that either does a DSGetLinkInfo to check the row count in the job (0? 1?) or one that checks the size of the file (0? >0?) would tell you if there was something on the end of your line or if you needed to go back and fish some more.

Sleep 900 seconds (15 minutes) via another Routine inside the loop. Exit the loop if the total runtime exceeds 10 hours or you found the row. When you do exit, you can then either run your load job or trigger an exception email (if you like) depending on which exit you took.

There are examples of the Loop stages in the docs. I haven't played with them much yet, preferring to hand code stuff like that, but I'm sure you would be able to do something like the above. There can be more to it, like building a way to exit early if you want the job to stop, but this should get you started. I hope.

Thanx Criag,

I was developing almost on the same lines.

Quick Q! How do I get the DSLInkInfo for a link in the job.. and make sure of that in the Sequence ?

You would write a 'generic' routine that takes the three required bits of information as arguments. Check the syntax of the command - you'll need a handle to the job to check and the name of the link and the transformer it comes from.

Your routine would then take the jobname you passed in and try to 'attach' to it. If successful, take the resulting job handle and call the DSGetLinkInfo function. Pass back the resulting count or any error encountered during the process. Don't forget to 'detach' before you exit the routine.

Keep in mind the fact that, if the Sequence job is automatically handling 'activities that fail', a routine that returns a non-zero result is considered to have failed. Handle that with the appropriate error triggers as documented in the online help so that it thinks *you* are handling any errors from the routine so it doesn't have to.

HI Craig,

Many thanx for the tip.

I have designed the job in a sligthly different way..

1. Start with the Start Loop Activity.

2. Call the Job Which will Read from the source table and create a file called "triggerfile" in the predefined directory, if the source table has a record with Null extract date.

3. Call the Wait for the Job Activity and check for the presence of the file.

3.1.1 If the file is not present then call a routine to sleep
3.1.2 End the loop and go to Step 1

3.2.1 If the file is present then Delete that file
3.2.2 Call the load job


It works..

Thanx again

Nagesh

Hi Srinagesh,

Nice way of explaining the process. Keep it up!

Many Thanks,
Naveen.

srinagesh wrote:2. Call the Job Which will Read from the source table and create a file called "triggerfile" in the predefined directory, if the source table has a record with Null extract date.

Out of curiousity, how are you keeping it from creating the file regardless of the outcome of the source query? Just having the Sequential File stage in a job will create an empty file when it runs...

chulett wrote:

srinagesh wrote:2. Call the Job Which will Read from the source table and create a file called "triggerfile" in the predefined directory, if the source table has a record with Null extract date.

Out of curiousity, how are you keeping it from creating the file regardless of the outcome of the source query? Just having the Sequential File stage in a job will create an empty file when it runs...

One way of acheiving it, to process out of the job. May be based on the status of the previous job, a Execute command activity can be called to touch a file. (The previous job should read the file and should changed its state either aborted or Finished ok). Or with After job subroutine to call a shell which inturns check the outcome of the job and touch a file.

Out of curiousity, how are you keeping it from creating the file regardless of the outcome of the source query? Just having the Sequential File stage in a job will create an empty file when it runs...

Source -----> Transformation -----> Folder Stage

Source is Oracle. The Source will have only one column called "Count"
The SQL query for the source is

• Select count(*) as count from table_name where <<condition>>

The Folderstage requires atleast 2 columns (One key column, One non Key column). I have dervied the Keycolumn as "If srcLink.Count > 0 Then "TriggerFile" Else "NoTriggerFile". and the NonKeyColumn as srcLink.count

So the output of this job will either be a "TriggerFile" or a "NoTriggerFile" (Depending on whether there are any rows in source with the matching condition or not)


My Job sequence has a FileWait on the TriggerFile.


-Nagesh