Highlighted
Five Stars

Run a job based on value in database

Hi ,

 

I want a job that runs only  when there is a value present in one of the tables

 

For example - if the value col1 in  select col1 from table is 'abc' then only will I execute the jobs.

 

Any ideas on this would be of great help

 

Thanks

Rathi

 

 

 

 

13 REPLIES
Eleven Stars TRF
Eleven Stars

Re: Run a job based on value in database

Hi,
You need to start the job periodicaly, query the table and if the value doesn't match the expected value, stop the job using a tDie.

TRF
Eleven Stars

Re: Run a job based on value in database

I may be misunderstanding this question, but are you asking for a way to identify which job to run by the data in your database? If that is the question, you can use the "Use Dynamic Job" functionality of the tRunJob component. Essentially you need to have a parent job which runs a query on your DB. Depending on what it returns the job can select which child job to get the tRunJob to run. 

Rilhia Solutions
Five Stars

Re: Run a job based on value in database

Hi ,

 

I will explain a little in detail. Let's say I have a job j1. This job must run only if the value select col1 from table = 'abc'.

 

If the value does not match then it should not execute, I don't want any error generated.

 

So, if

value matches

then

execute job

else

do nothing

end if;

 

Thanks 

Rathi

Eleven Stars

Re: Run a job based on value in database

You can do this using what @TRF suggested, but don't use the tDie. Just use conditional logic (using RunIf links) based on the success or failure of the query. If the query returns the value you want, go down the RunIF pointing to the tRunJob used to run this job. If it doesn't, do nothing.

Rilhia Solutions
Eleven Stars TRF
Eleven Stars

Re: Run a job based on value in database

OK, so you can go with the proposition I made on top of this topic, it's enough.

You just have to decide the scheduling to start the job when needed.


TRF
Five Stars

Re: Run a job based on value in database

Hi, thanks for the reply. 

 

I am trying to do this.

 

tmssqlinput --> tmap --> tjava --runif --> tsendmail

 

in tmssqlinput I have the query, "select [Values] val from lookupvalues where tablename = 'ETLSource'" which returns the value "devsprint13"

 

in tjava I have the code as context.source= row1.val;

 

In runif the code is "devsprint13".equals(context.source)

 

When I run the job, the runif returns a false.

 

When I try to see the output of tjava using a tlogrow the context.source has no value assigned.

 

I don't understand why the value of the query output is not being assigned to  context.source in tjava ( context.source= row1.val;)

 

Any ideas on this?

 

Thanks 

Rathi

Eleven Stars

Re: Run a job based on value in database

Use a tSetGlobalVar instead of the tJava component. If you are not aware of this component, it allows you to set a hashmap variable from a column in the data row. To use this in the RunIf you would do something like this (assume the name of the variable is "myVar")....

 

globalMap.get("myVar")!=null && ((String)globalMap.get("myVar")).equals("devsprint13")
Rilhia Solutions
Five Stars

Re: Run a job based on value in database

Thanks I will try this out

 

But, may I know why this does not work with tjava?

 

Thanks

Rathi

Eleven Stars

Re: Run a job based on value in database

The tJava is not really an "on row" component *could* be one of the reasons. The context variable has not been properly assigned before testing the RunIf condition, *could* be another reason. I'd have to look at the job. But this way would be a better way of doing this. If this doesn't work you will need to check that your tMap is configured properly.

Rilhia Solutions
Five Stars

Re: Run a job based on value in database

Hi,

When I connect the tjava to a tlogrow then I do get the output as desired. Only When I have connected to RunIf the code always returns a false

 

The job design is as I have explained in my previous reply

 

Thanks

Rathi

Eleven Stars TRF
Eleven Stars

Re: Run a job based on value in database

Can you a capture + the condition?

TRF
Five Stars

Re: Run a job based on value in database

Hi TRF, I am sorry I did not get what you are saying. You want me to give the condition in runif?

 

 

Eleven Stars TRF
Eleven Stars

Re: Run a job based on value in database

Yes, it could be easier if we have a picture of the job and as you said the runIf link allways returned false, we need the condition you've written to try to help you.


TRF