Menu Request Demo

Custom Job Control

Date:
18 October 2007
Author:
Aaron Parvin

In some situations you may choose to write custom code to control the activation of a job through the WhereScape RED Scheduler. Situations that can cause the need to consider this requirement are:• Activate a job based on a trigger event. Such as working day # of a calendar month.

• Maintain one version of the job metadata but name its scheduled run iteration dependent on the date of processing or the source system that provides the data

.• Ability to control the scheduled iteration of the job a given number of times, perhaps for a table rebuild.

• Have a job run as a task with a master job but using an alternate scheduler type. The windows scheduler and not the unix scheduler for example.

When writing this custom code it becomes necessary to communicate with some WhereScape Red scheduler metadata objects. The table below lists and describes some key metadata objects and how they can be used when writing custom code.

Object Namews_wrk_schedular

 

ws_wrk_job_run

 

 

 

 

 ws_job_restart

 

ws_job_create

 

 

ws_admin_v_sched

DescriptionA table listing schedulers and their active status.

 

A WhereScape RED audit meta data table that maintains a row for jobs that are running or failed.

 

 

 

A WhereScape RED audit meta data table that maintains a row for jobs that are running or failed

A WhereScape RED callable procedure that allows the creation of a new job from another job that must be in either a hold or waiting state.

 

A WhereScape RED meta data view. Provides a view across multiple RED audit metadata providing access to the current status of a job

Useful forThis table can be used to confirm that a particular scheduler or scheduler type is available.

If a job has failed a row will exist in this table. This table can then be used to establish if a restart or a job create is required.A restart is very useful because a job may fail having completed 90% of its tasks. When the issue is resolved the job can then pick up from the point that it failed.To restart a failed job

 

 To create a new job based on another job

 

 

This view can be monitored to establish the status of the job

 

The configuration of the custom procedure will depend heavily on your specific requirements. However in general you will need to communicate with the above objects in almost all cases. The following sql is provided as an example of how it might be done.

– ==============================================================================
– DBMS Name : Oracle
– Script Name : custom_task_job_control
– Description :
– Generated by :
– Generated for :
– Author :
– ==============================================================================
– Notes / History

WhereScape Limited Custom Job control.doc 28 July 2006 Page 1 of 5
– This is designed to be executed as a task within a job.
– It runs another job using Ws_Job_Create and then looks at the audit tables
– for successful completion of that job before completing successfully itself.
– This is needed because RED executes a task job within a job using only one schedular type.
– Unix OR Windows. Not a combination of the two.
– This procedure will execute a task job from within a job and use a different schedular type.
– The only draw back is that the task job audit meta data will be held seperatley to the
– calling job audit meta data.

CREATE OR REPLACE procedure custom_task_job_control
(
p_sequence IN number,
p_job_name IN varchar2,
p_task_name IN varchar2,
p_job_id IN number,
p_task_id IN number,
p_return_msg OUT varchar2,
p_status OUT number
)
AS
–===============================================================
– Control variables used in most programs
–===============================================================
v_MsgText varchar2(255); — Text for audit_trail
v_sql varchar2(4000);– sql statements
v_step integer := 0; — return code
v_update_count integer := 0; — no of records updated
v_insert_count integer := 0; — no of records inserted
v_count integer := 0; — General counter;
v_iteration integer := 0; — iteration counter;
v_fact_inv_start varchar2(30); — date;
v_fact_inv_end varchar2(30); — date;
v_fact_inv_current varchar2(30); — date;
v_status integer := 0; — Status holder
v_return_code varchar2(1);
v_return_msg varchar2(256);
v_wait_limit integer := 0;
v_wait_total integer := 0;
v_sleep integer := 0;
v_template_job_name varchar2(256);
v_task_job_name varchar2(256);
v_gpm_current_cal_month integer := 0; — date (yyyymm);
v_task_sequence integer := 0; — the sequence number of the created task job
v_task_status varchar2(30); — the sequence number of the created task job
BEGIN
v_step := 100;
–=======================================================
– Establish if a Windows schedular is available to
– run the load job.
– If not fail and halt processing
–=======================================================
SELECT count(*)
INTO v_count
FROM ws_wrk_scheduler
WHERE ws_type_ind = ‘W’
AND ws_stop_date is null;
IF v_count = 0 THEN
p_return_msg := ‘Exception no windows schedular active’||
‘ Step ‘ ||v_step;
p_status := WsWrkAudit(’F',p_job_name, p_task_name, p_sequence,
p_return_msg,SQLCODE,SQLERRM,p_task_id, p_job_id);
p_status := -3;
RETURN;
END IF;
–=======================================================
– Set Some variables
–=======================================================
– Name of job to copy
v_template_job_name := ‘Populate dw fact’;
– Month of data to process
v_gpm_current_cal_month := WsParameterRead(’CURRENT_CAL_MONTH’);
– Combine the month of data to process and the template job name to
– create a task job name.
v_task_job_name := v_gpm_current_cal_month||’ ‘||v_template_job_name;
–=======================================================
WhereScape Limited Custom Job control.doc 28 July 2006 Page 2 of 5
– Descision time create or restart
– If a task job is in a failed state then initiate a restart
– (because the majority of work may already be done)
– else use ws_job_create
–=======================================================
SELECT count(*)
INTO v_count
FROM ws_wrk_job_run
WHERE ws_wrk_job_run.wjr_name = v_task_job_name;
IF v_count != 0 THEN
–=======================================================
– This procedure allows the automated restarting of a
– failed job.
–=======================================================
v_status := Ws_Job_Restart( p_sequence
, p_job_name
, p_task_name
, p_job_id
, p_task_id
, v_task_job_name
, v_return_code
, v_return_msg);
ELSE
–=======================================================
– Run the job using Ws_Job_Create
– This procedure allows the automated creating of a new job
– from an existing job that is in either a holding or waiting
– state.
–=======================================================
v_step := 300;
v_status := Ws_Job_Create( p_sequence
, p_job_name
, p_task_name
, p_job_id
, p_task_id
, v_template_job_name
, v_task_job_name
, NULL
, ‘ONCE’
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, v_return_code
, v_return_msg);
END IF;
p_return_msg := v_task_job_name||’ ‘||v_return_code||’ ‘||v_return_msg;
p_status := WsWrkAudit(’I',p_job_name, p_task_name, p_sequence,
p_return_msg,NULL,NULL,p_task_id, p_job_id);
COMMIT;
–=======================================================
– Get the sequence number of the task job
–=======================================================
SELECT MAX(”Sequence”)
INTO v_task_sequence
FROM WS_ADMIN_V_SCHED
WHERE “Job Name” = v_task_job_name;
–=======================================================
– Check for completion of the job
–=======================================================

– Set wait limit for job to MORE than the expected completion time
– of the job called.
– Wait limit and sleep require SECONDS
WhereScape Limited Custom Job control.doc 28 July 2006 Page 3 of 5

v_sleep := 120; — 2 minutes
v_count := 0;
v_wait_total := 0;
v_wait_limit := 28800; — 8 hours

– Sleep for a few minutes before beginning the task job status loop

DBMS_LOCK.SLEEP(180);
WHILE v_wait_total < v_wait_limit LOOP

– Sleep for value in v_sleep

DBMS_LOCK.SLEEP(v_sleep);
v_wait_total := v_wait_total + v_sleep;
v_step := 400;
SELECT “Status”
INTO v_task_status
FROM WS_ADMIN_V_SCHED
WHERE “Sequence” = v_task_sequence;

– Look for ‘On Hold’,'Running’,'Pending’,'Waiting’,'Completed’,'Blocked’,'Failed’,'Failed - Aborted’,'Error Completion’,'Unknown’

IF v_task_status = ‘Completed’ THEN
– exit the loop as the job has completed successfully
EXIT;
ELSIF v_task_status IN (’Pending’,'Waiting’,'Running’) THEN
– keep looping through looking for completion status
NULL;
ELSIF v_task_status IN (’Failed’,'Failed - Aborted’,'Error Completion’,'Unknown’) THEN
– raise error and fail
p_return_msg := ‘Exception in custom_task_job_control. Called job ‘||v_task_job_name||’ has failed. Check audit trail.’;
p_status := WsWrkAudit(’F',p_job_name, p_task_name, p_sequence,
p_return_msg,NULL,NULL,p_task_id, p_job_id);
p_status := -3;
RETURN;
ELSE
– ‘Blocked’,'On Hold’ or any other status not catered for
– should not occur raise error and fail
p_return_msg := ‘Exception in custom_task_job_control. Called job ‘||v_task_job_name||’ status is ‘||v_task_status;
p_status := WsWrkAudit(’F',p_job_name, p_task_name, p_sequence,
p_return_msg,NULL,NULL,p_task_id, p_job_id);
p_status := -3;
RETURN;
END IF;
END LOOP;
–=======================================================
– If we have exceeded the wait limit it is likely that
– the task job has experienced a problem.
– This calling task will fail.
–=======================================================
v_step := 500;
IF v_wait_total >= v_wait_limit THEN
p_return_msg := ‘Exception in custom_task_job_control. Wait limit exceeded. Step ‘ ||v_step;
p_status := WsWrkAudit(’F',p_job_name, p_task_name, p_sequence,
p_return_msg,NULL,NULL,p_task_id, p_job_id);
p_status := -3;
COMMIT;
WhereScape Limited Custom Job control.doc 28 July 2006 Page 4 of 5
RETURN;
END IF;
–===============================================================
–All Done report the results and return.
–p_status is the return code. Valid values are:
– 1 successfull completion
– -2 failed with error
– -3 failed with unhandled error
–p_return_msg is a 256 character message which should
– provide a summary of the result of this procedure.
–===============================================================
COMMIT;
v_step := 700;
p_status := 1;
p_return_msg := ‘custom_task_job_control completed. ‘;
RETURN;
EXCEPTION
–=================================================================
– Unhandle Exception. Return with Fatal Error
–=================================================================
WHEN OTHERS THEN
p_return_msg := ‘Unhandled Exception in custom_task_job_control. ‘||
‘ Step ‘ ||v_step || ‘ ‘||SQLERRM;
p_status := WsWrkAudit(’F',p_job_name, p_task_name, p_sequence,
p_return_msg,SQLCODE,SQLERRM,p_task_id, p_job_id);
p_status := -3;
RETURN;
END custom_task_job_control;

 

CATEGORIES:

Comments 0

Leave a Comment

No comments.