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
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.
| 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.
| 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
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.
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 |
- SAP Managed Tags:
1 Comment
