Skip to Content

1      INTRODUCTION

1.1   Purpose

The purpose of this document is to define the BODS Job Recovery template for Business Objects Data Services (BODS) projects. This Recovery template will enable the development team to implement module wise recovery logic in case of any failure of the Job.

The Recovery template will enable the BODS job server to run the BODS job from the point where an error was detected in the previous run. As the recovery logic is implemented using BODS job design techniques, this standard job can be run multiple times. In every run, the steps, which were failed /not run in the previous run, will be initiated .This default behaviour can also be overridden (by setting a global variable ) in case the developer wants to run all the steps of his/her job again (ignoring the previous run status information ) .

In addition, this standard template will enable the design and development team to follow specific guidelines for BODS projects.

The job template is designed in a way to:

o    Log the job run parameters in a database for auditing purpose.

o    Log the status and point of failure in case of failed jobs to allow the job to resume from the step of failure when re-run, thus avoiding the redundant reprocessing of already processed data.

o    Allow Initial and Delta runs of the jobs wherever required.

 

2     DESIGN COMPONENTS

/wp-content/uploads/2014/12/1_601040.png

2.1   Try – Catch Blocks:

The Try-Catch Blocks continuously function within their scopes and capture exceptions.

Try-Catch Blocks are implemented at two levels

i)              Job level- All the data processing components are placed within the scope of a main Try- Catch block.

ii)             Work Flow level – Each workflow has its own try – catch block. This is to ensure that an error in one workflow will be logged at that point itself and will not stop the job execution of other workflows placed parallel with that workflow.

2.2   Data Processing Section

      The Data Processing area could be split on the High Level into 3 different steps. You have to replicate the number of Recovery Workflows as required by the job . These steps are:

–         Initialize

–         Recovery Workflows

–         Status Update Workflow

2.2.1     Initialize

The Initialize step performs the task of

o    Initializing all the variables required in the Job.

o    Determining whether the Job failed in the previous run or not.

o    Fetching the Next Job run id.  

o    Logging the Job details like Job name, Job start time, Status etc. into Job_Control table.

2.2.2     Recovery Workflows

In this part the recovery logic is implemented. Based on the job requirements ( number of dataflows/workflows required ) the recovery workflows can be replicated and named based on the functionality. The recovery workflows can be connected in parallel/ serial based on the job functionality.   

Each Recovery workflow has the following components:

1)     Try Block

2)     Initializing Script

3)     Conditional

4)     Catch Block

2.2.3     Status Update Workflow

In this part we are checking that whether any of the workflow failed during the job run. In case any of the workflow had failed, we will update the job status as Failed.

3     DESIGN PROCESS

3.1   Initialize Step

The section is completely contained in an Initialize workflow and contains an Initialize script which performs the process of setting up most of the Global Variables required in the job.

Before start of any of the SAP DS projects, JOB_CONTROL table needs to be created.

This table contains the job run status information for every run of the job. For every new run of the job, a new entry is inserted with the job details.

  1. No.

Field Name

Field Description

Field Type

Field Length

1

JOB_RUNID

Unique ID to store the instance of the job run

INTEGER

2

JOB_NAME

Name of the Job

VARCHAR

250

3

JOB_START_TIME

Job Start Date & Time

DATETIME

4

JOB_END_TIME

Job End Date & Time

DATETIME

5

JOB_STATUS

Process status of a batch

R – Running

C – Completed

F – Failed

VARCHAR

2

The Global variables that are set in this step are

Parameter Name

Type

Description                

Value Assigned

$G_JOB_NAME         

VARCHAR(250)

The name of the job currently executing 

job_name()

$G_RUNID

INT

Unique id for the current job run which is used to populate Job_Runid of Job_control table . In case the previous instance of the job has failed, this variable will contain the previous run’s Job_Runid

via custom function

$G_JOB_STAT

US

VARCHAR(1)

The status of the previous job run

via custom function

$G_RECOVER

Y_FLAG

VARCHAR(1)

The unique id assigned for the current instance of the job run. In case the previous instance of the job fails, this variable will store the previous run id.

via custom function

$G_ERROR_M

SG

VARCHAR(1000)

The error message in case the job fails

$G_LOAD_TYP

E

VARCHAR(10)

This determines whether the job is a Delta load or Full Refresh. Default value is set as ‘DELTA’. Can be overridden during the job run.

‘DELTA’ or ’FULL REFRESH’ 

$G_PARALLEL

_LOAD

VARCHAR(1)

This determines whether the various units in the job need to run in Independent or Dependent mode. If $G_PARALLEL_LOAD = ‘Y’, failure in any workflow will not stop the job execution of the job at that point, i.e other workflows which come after the failed workflow will continue to be initiated .

Default value is set as ‘Y’ . Can be overridden at job run.

‘Y’ or ’N’

$G_RECORDS

_COUNT

INT

The count of the records in the target which is processed in the current run.

$G_CDCLOW

VARCHAR(30)

The min value used to fetch records from source during delta load.

$G_CDCHIGH

VARCHAR(30)

The max value used to fetch records from source during delta load.

$G_DEFAULT_

CDCLOW

VARCHAR(30)

Used to specify the value of $G_CDCLOW during the first run. In ‘DELTA’ load, in the first instance of the job run $G_CDCLOW will be equal to $G_DEFAULT_CDCLOW. From the next run onwards $G_CDCLOW = $G_CDCHIGH value of the previous successful run.

For ‘FULL REFRESH’ load,  $G_CDCLOW will be equal to $G_DEFAULT_CDCLOW for all runs.

Note: All these variables values can be overridden during job execution or in the workflow level scripts.

Custom Functions Used:

Function

Description

Custom_Function

Fetches a new run-id for a new instance of the job run. In case the previous instance of the job failed, this function will fetch the previous run-id. Also logs an entry with the run-id into Job_Control table.

  3.2   Recovery Workflows

In these workflows, the recovery logic is designed. Depending on the job’s complexity and number of target tables loaded, these workflows needs to replicated as many times as required. The thus replicated recovery workflows can be connected either in parallel or in serial.

The salient features of these workflows are as follows:

o    Log each workflow running status in a database.

o    The workflow name is captured automatically during the job run. Even if there is an addition /deletion/ renaming of workflows, the modified details will be automatically captured in the control tables during the next job run

o    Enables recovery from the point of failure .For every run of the job, the workflow/s, which failed/not executed in the previous run will be executed from the point of failure under the same conditions as the previous job run. The workflow/s which were successfully initiated in the previous run will not be initiated (thus avoiding the redundant reprocessing of already processed data) until and unless all the workflows of the job has been successfully executed. 

o    Allow Initial and Delta runs of the workflows wherever required.

o    The various workflows can run in Independent mode or in Dependent mode. For  Independent mode , any failure of the workflow will not stop the job execution of the job at that point, i.e other workflows which come after the failed workflow will continue to be initiated .For Dependent mode, any failure at a workflow will stop the job execution at that very point.

  1. No.

Field Name

Field Description

Field Type

Field Length

1

JOB_RUNID

Unique ID to store the instance of the job run

INTEGER

2

JOB_NAME

Name of the Job

VARCHAR

250

3

WF_NAME

Name of the Workflow

VARCHAR

250

4

WF_START_TIME

Workflow Start Date & Time

DATETIME

5

WF_END_TIME

Workflow End Date & Time

DATETIME

6

WF_STATUS

Process status of a batch

R – Running

C – Completed

F – Failed

VARCHAR

2

7

WF_LOADMINDATE

CDC Low value i.e. min. date range extracted from source

VARCHAR

30

8

WF_LOADMAXDATE

CDC high value i.e.  max. date extracted from source

VARCHAR

30

9

WF_RECORDS_COUNT

Total records loaded in the target

INT

Custom Functions Used:

Function

Description

custom function 2

This function logs entry determines whether the workflow needs to run in the current job run or not.

The recovery workflow consists of the below steps:

      i)              Try- Catch Blocks

ii)             Workflow Initialize Script

iii)            Conditional Flow

/wp-content/uploads/2014/12/2_601054.png

Try- Catch Blocks – These Try-Catch blocks are at workflow level. Any exception in the workflow will be handled in the catch block.

Workflow Initialize Script- In the script SC_START_WF all the local variables used in the workflow is initialized. The script calls the custom function custom function 2 , which logs the entry into wf_control table. The flag $LV_RECOV_FLAG is assigned the value of Y or N .

The logic for this

$LV_RECOV_FLAG-> Y

A)    The Job failed in the previous instance AND this workflow had failed

B)    The Job failed in the previous instance AND this workflow was not executed

C)    The Job successfully executed in the previous run.

$LV_RECOV_FLAG-> N

A)    The Job failed in the previous instance but this workflow had successfully run

Conditional Flow- In this step the value of the variable $LV_RECOV_FLAG is checked.

$LV_RECOV_FLAG-> Y -The dataflow/workflow inside conditional will be initiated.

$LV_RECOV_FLAG-> N -The dataflow/workflow inside conditional will not be initiated.

41.PNG

Custom Functions Used:

Function

Description

custom function 3

This function logs the success/Failure status in WF_CONTROL table

3.3   Status Update Workflow

A custom function is called to check whether any of the workflow failed during the job run.

Custom Functions Used:

Function

Description

custom function 4

This function logs the success/Failure status in JOB_CONTROL table

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply