Custom scheduling without “.BAT” file or any third party schedulers

[venkateshgolla]

Custom scheduling  without “.BAT” file or any third party schedulers:

Introduction:

This Article provides solutions for custom scheduling BODS Batch Job conditionally. As you are aware that BODS does not contain an inbuilt mechanism to customize the schedule of a job. However, we cannot execute workflow on its own and need a Job to execute it and in various scenarios there is a need to sequence the Jobs and conditionally run the Jobs. The approaches provided below can be used where we can customize our scheduling as per the requirement and without using any .BAT files or any third party schedulers.

• With this approach we do not need any third party softwares or any scheduling tools.
• With this approach we do not need any BAT files or SH scripts or any LINUX environment.
• We can avoid scheduling the entire job and wasting the time and impact.
• Avoids loading a Job with too many Workflows.
• At any point, Child Jobs can be run independently too in Production Environment, this will not be possible if the entire Job logic is put into a Workflow.

We can schedule a job with .BAT file by using below procedure:

• Create a script in designer, inside script use below code:

Exec( 'cmd.exe’ , 'E:\\SAP_SCHEDULE\\JOB1.BAT',  😎 ;   

Print(  ' Job_1 Completed....') ;

Our aim is to schedule a job without .BAT file:

Let us assume the requirement is to schedule a job on the third Friday of each quarter of the year.

Generally, to approach this requirement, we create a .BAT file and schedule it with EXEC command as shown above or we have to manually execute this job on particular date.

How to automate this kind of scenario?

In the following I will explain the process to achieve without .BAT file

I have created a Project: PRJ_CUSTOM_SCHEDULE

And Job: JOB_CUSTOM_SCHEDULE.

And then, I have created a script: SCR_THIRD_FRI

The script contains below logic:

          $G_SYSDATE = to_date( sysdate(), 'YYYY.MM.DD');  à

          #$G_SYSDATE = to_date( '2015.07.11', 'YYYY.MM.DD');

          print( '  $G_SYSDATE '||$G_SYSDATE); --> printing the date

          $G_THIRD_FRI = to_date (sql( 'DS_NAME' ,'select b.CALENDAR_DATE from ( select row_number() over (partition by YEAR_NUM, QUARTER_NUM order by                YEAR_NUM, QUARTER_NUM desc) as QTR_WEEK_NUM,  a.* from (  select CALENDAR_DATE, YEAR_NUM, QUARTER_NUM from SCHEMA1.DATE_DIM                where DAY_OF_WEEK_NUM = 6 and YEAR_NUM = YEAR(CURRENT_TIMESTAMP) and  QUARTER_NUM = DatePart(QUARTER, {$G_SYSDATE})  ) a) b                where b.QTR_WEEK_NUM = 3 '),'YYYY.MM.DD');

          #$G_THIRD_FRI = $G_SYSDATE; 

          Print (‘$G_THIRD_FRI: The job execution date is:   ' || $G_THIRD_FRI );

The query written in the script is calculating third Friday of each quarter. This query is written on Date dim table. Schema of Date dim might be different from project to project. You need to modify the query (eg: column names) as per the schema of your Date dim.

I joined this Script with a conditional object as follows:

Inside the conditional object:

The actual business logic(WF_BusinessLogic) works if the fetched/calculated date is equal to current system date or else the script(SCR_NOT_3FRI) will be executed which says today is not 3^rd Friday of this quarter.

The condition in the script make sure that the workflow will execute only on 3^rd Friday of each quarter.

Once this is done, Schedule the job JOB_CUSTOM_SCHEDULE

Login to Data services Management Console:

1. Go to Administrator tab -->
2. 

Select Batch under Administrator

Then -->

Schedule the job :

Advantages of this approach:

• Calculating job scheduling day is pushed down to database server.
• Maintaining the code is easy compared to .bat / .sh method. Can be reused as reusable component