Condition based triggering of batch

[Puneet Kalra]

Hi

We're focused on minimizing database queries to reduce costs. Our daily schedule comprises two batches: one at 5 AM for loading staging files from multiple countries, and another at 6 AM for processing data into reporting tables. However, we aim to optimize the 6 AM batch by running it conditionally—specifically, only when there's new data in the staging tables from the 5 AM batch.

Essentially, we're seeking a way to trigger the 6 AM batch based on certain conditions. If these conditions aren't met, we want to exit the batch without executing it to avoid unnecessary costs.

How can we achieve this?

[Dieter Stubler]

Hi,

As always there are multiple ways to achieve what you are up to.

I'll try to lineout one possible solution.

Create an Exit State Definition NO_NEW DATA.
Create an Exit State Mapping CHECK_NEW_DATA which maps exit code 0 to SUCCESS and exit code 1 to NO_NEW_DATA, everything else to FAILURE.

Create an Exit State Profile CHECK_NEW_DATA (does not have to be the same name as the mapping) which contains SUCCESS and NO_NEW_DATA (both FINAL), FAILURE (RESTARTABLE) and SKIPPED (FINAL set to the unreachable state) and set the default Exit State Mapping to CHECK_NEW_DATA .

Create a new top level batch like 6AM_MAIN which has your 6AM batch as child and schedule this one instead of your 6AM batch.

Create  a job CHECK_NEW_DATA with the Exit State Profile and a run program which will exit 0 if there is new data or 1 if there is no new data.
Add this new job as child to the 6AM_MAIN batch.
Create a dependency to the CHECK_NEW_DATA job to your 6AM batch with state selection FINAL and required state SUCCESS.

If you use the STANDARD Exit State Profile for the 6AM batch it will contain a state SKIPPED which is set to be the unreachable state. (I recommend to do that in most your Exit State Profiles).
Now if the CHECK_NEW_DATA job exits with 1 it will have a final exit state NO_NEW_DATA which does not fulfill the required state SUCCESS of the dependency of your 6AM batch.
The 6AM batch and all of its children (assuming that all have an exit state profile containing an unreachable state like SKIPPED) will not be executed and will show the FINAL state SKIPPED.

As an alternative you could also use a synchronizing resource with a resource state model  which will be set by a job in your 5AM batch to reflect whether there are new data (State NEW_DATA) or not  (State NO_NEW_DATA) and configure the CHECK_NEW_DATA job of your 6AM batch to require this resource in state NEW_DATA with timeout 0 and an timeout state SKIPPED.
You can now just enter a 0 as run program which used as exit code without executing anything.This will also result in the 6AM batch going to be skipped if the CHECK_NEW_DATA job results in skipped because it takes the timeout state SKIPPED if the resource is not in state NEW_DATA.

Hope that helps. Let me know whether you could solve your problem and dont hesitate to ask if any questions arise.

Regards

Dieter 

[Ronald Jeninga]

Hi,

first of all, I think it is a good idea to start the 6 AM batch nevertheless.

This way it is obvious that everything that was scheduled to run was started indeed.

If it is easy to check if the 5 AM batch added new data to the staging tables, maybe with a small script that issues a fast query, the easiest way to implement your requirement would be to run that script as a predecessor of the 6AM batch.

Hence, instead of

    BATCH_6AM

(as a batch with any number of children), you create

   BATCH_6AM_WRAPPER

            |

   +--------+---------+

   |                  |

CHECK_STAGING ----> BATCH_6AM

where CHECK_STAGING exits with some exit code that maps to some Exit State, e.g. STAGING_EMPTY if no new rows are present.

It simply exits with SUCCESS if it finds rows.

BATCH_6AM requires the SUCCESS state from its predecessor and it will skip execution if its predecessor reports STAGING_EMPTY.

There are a number of other ways to schieve the same, but why make it complicated.

I hope this helps.

Best regards,

Ronald

puneet....@gmail.com schrieb am Freitag, 26. April 2024 um 10:23:18 UTC+2: