Ws_Job_Status

Synopsis
Returns the current status of a job.
Description
Returns the current status of the specified job, as recorded by a WhereScape RED Scheduler. Typically, this routine is used by a third-party scheduler or a user-defined procedure/script to check on a job.
Input

Input	Description
Common Input	Includes all 5 inputs of the Callable Routines Common Input.
Job Sequence	The unique integer identifier of the job to return the status of. This input is optional but when it is specified, the started within and started after inputs should not be specified.
Job Name	The name of the job to return the status of. The specified name must exactly match the job name as displayed by the WhereScape RED Scheduler.
Started Within Last Minutes	The maximum minutes [0-148599] (up to ~103.1 days) in the past to look back for the job to have started. This input is optional but when it is specified, the job name must be specified and the job sequence and started after inputs should not be specified. Note: If multiple instances of the job have started in the specified time frame, then the last job to start is returned (i.e. the job with the highest sequence number).
Started After Time	The date/time after which to look for the job to have started. This input is optional but when it is specified, the job name must be specified and the job sequence and started within inputs should not be specified. Note If multiple instances of the job have started in the specified time frame then the last job to start is returned (i.e. the job with the highest sequence number).

Output

Output	Description
Return Code	Output Return Code: S - Success. N - The job exists but it was NOT started within the specified time frame. E - Error. F - Fatal/Unexpected Error.
Return Message	Output message indicating the action applied or the reason for no action.
Result Number	Output Result Number: 1 - Success. -1 - The job exists but it was NOT started within the specified time frame. -2 - Error. -3 -Fatal/Unexpected Error. 0 - see note below.
Simplified Job Status Code	Simplified Job Status Code: N - Not Running. R - Running. F - Failed. C - Completed. 0 - see note below.
Standard Job Status Code	Standard Job Status Code: H - On Hold. The job is on hold. A held job can be edited and/or started. W - Waiting. The job is waiting to start (it is either waiting for the scheduled time to arrive or is waiting for an available scheduler). B - Blocked. The job is blocked because a previous instance of the same job is still running. P - Pending. This is the initial interim status of an "about to start running" job. The scheduler has identified that the job is ready to start and is preparing to run it. A job should only be pending for a brief period so if it remains pending for a prolonged period then an unexpected error has occurred. R - Running. The job is currently running. F - Failed. The job failed due to an error. C - Completed. The job completed successfully (but it may have warnings). A completed job cannot be restarted. G - Failed - Aborted. The job failed and was subsequently aborted. An aborted job cannot be restarted. E - Error Completion. 0 - see note below.
Enhanced Job Status Number	Enhanced Job Status Number that returns an integer rather than the standard alphabetic code. The running and completed statuses are enhanced to distinguish errors or warnings. On Hold. The job is on hold. A held job can be edited and/or started. Waiting. The job is waiting to start (it is either waiting for the scheduled time to arrive or is waiting for an available scheduler). Blocked. The job is blocked because a previous instance of the same job is still running. Pending. This is the initial interim status of an "about to start running" job. The scheduler has identified that the job is ready to start and is preparing to run it. A job should only be pending for a brief period so if it remains pending for a prolonged period then an unexpected error has occurred. Running. The job is currently running, and no tasks have failed or produced warnings. Running with Errors. The job is currently running but some tasks have failed. The job will ultimately fail when all the tasks that are NOT dependent on the failed tasks have finished. Running with Warnings. The job is currently running, and some tasks have produced warnings. Failed. The job failed due to an error. Completed. The job completed without warnings. A completed job cannot be restarted. Completed with Warnings. The job completed with warnings. A completed job cannot be restarted. Failed - Aborted. The job failed and it was subsequently aborted. An aborted job cannot be restarted. Error Completion. 0 - see note below.

Note

All three returned status values can also return '0' in any of the following situations:

Illegal combination of parameters specified.
Unable to locate specified job sequence.
Unable to locate specified job name.
Job Not Found having started in the last SpecifiedMinutes minutes.
Job Not Found having started after SpecifiedDateTime.

SQL Server

SQL Server Parameters: Ws_Job_Status
Callable Routine Type: PROCEDURE.

Parameter Name	Datatype	Mode
@p_sequence	INTEGER	IN
@p_job_name	VARCHAR(64)	IN
@p_task_name	VARCHAR(64)	IN
@p_job_id	INTEGER	IN
@p_task_id	INTEGER	IN
@p_check_sequence	INTEGER	IN
@p_check_job	VARCHAR(64)	IN
@p_started_in_last_mi	INTEGER	IN
@p_started_after_dt	DATETIME	IN
@p_return_code	VARCHAR(1)	OUT
@p_return_msg	VARCHAR(256)	OUT
@p_result	INTEGER	OUT
@p_job_status_simple	VARCHAR(1)	OUT
@p_job_status_standard	VARCHAR(1)	OUT
@p_job_status_enhanced	VARCHAR(2)	OUT

SQL Server Examples: Ws_Job_Status

– The p_ VARIABLES are normally PARAMETERS in a RED-generated Procedure.
DECLARE @p_sequence integer
DECLARE @p_job_name varchar(256)
DECLARE @p_task_name varchar(256)
DECLARE @p_job_id integer
DECLARE @p_task_id integer
DECLARE @p_return_msg varchar(256)
DECLARE @p_status integer
DECLARE @v_result_num integer
DECLARE @v_return_code varchar(1)
DECLARE @v_return_msg varchar(256)
DECLARE @v_job_status_simple varchar(1)
DECLARE @v_job_status_standard varchar(1)
DECLARE @v_job_status_enhanced varchar(2)
EXEC Ws_Job_Status
@p_sequence, @p_job_name, @p_task_name, @p_job_id, @p_task_id
, NULL, 'Daily Run', 10, NULL
, @v_return_code OUTPUT
, @v_return_msg OUTPUT
, @v_result_num OUTPUT
, @v_job_status_simple OUTPUT
, @v_job_status_standard OUTPUT
, @v_job_status_enhanced OUTPUT

Teradata

Teradata Parameters: Ws_Job_Status
Callable Routine Type: PROCEDURE.

Parameter Name	Datatype	Mode
p_sequence	INTEGER	IN
p_job_name	VARCHAR(64)	IN
p_task_name	VARCHAR(64)	IN
p_job_id	INTEGER	IN
p_task_id	INTEGER	IN
p_check_sequence	INTEGER	IN
p_check_job	VARCHAR(64)	IN
p_started_in_last_mi	INTEGER	IN
p_started_after_dt	TIMESTAMP	IN
p_return_code	VARCHAR(1)	OUT
p_return_msg	VARCHAR(256)	OUT
p_result	INTEGER	OUT
p_job_status_simple	VARCHAR(1)	OUT
p_job_status_standard	VARCHAR(1)	OUT
p_job_status_enhanced	VARCHAR(2)	OUT

Teradata Examples: Ws_Job_Status

– The p_ VARIABLES are normally PARAMETERS in a RED-generated Procedure.
DECLARE p_sequence integer;
DECLARE p_job_name varchar(256);
DECLARE p_task_name varchar(256);
DECLARE p_job_id integer;
DECLARE p_task_id integer;
DECLARE p_return_msg varchar(256);
DECLARE p_status integer;
DECLARE v_result_num integer;
DECLARE v_return_code varchar(1);
DECLARE v_return_msg varchar(256);
DECLARE v_job_status_simple varchar(1);
DECLARE v_job_status_standard varchar(1);
DECLARE v_job_status_enhanced varchar(2);
CALL [METABASE].Ws_Job_Status
( p_sequence, p_job_name, p_task_name, p_job_id, p_task_id
, NULL, 'Daily Run', 10, NULL
, v_return_code
, v_return_msg
, v_result_num
, v_job_status_simple, v_job_status_standard, v_job_status_enhanced
);

Oracle

Oracle Parameters: Ws_Job_Status
Callable Routine Type: FUNCTION.

Parameter Name	Datatype	Mode
p_sequence	NUMBER	IN
p_job_name	VARCHAR2	IN
p_task_name	VARCHAR2	IN
p_job_id	NUMBER	IN
p_task_id	NUMBER	IN
p_check_sequence	NUMBER	IN
p_check_job	VARCHAR2	IN
p_started_in_last_mi	NUMBER	IN
p_started_after_dt	DATE	IN
p_return_code	VARCHAR2	OUT
p_return_msg	VARCHAR2	OUT
p_job_status_simple	VARCHAR2	OUT
p_job_status_standard	VARCHAR2	OUT
p_job_status_enhanced	VARCHAR2	OUT
FUNCTION Return Value	NUMBER	OUT-Function

Oracle Examples: Ws_Job_Status

– The p_ VARIABLES are normally PARAMETERS in a RED-generated Procedure.
p_sequence number;
p_job_name varchar2(256);
p_task_name varchar2(256);
p_job_id number;
p_task_id number;
p_return_msg varchar2(256);
p_status number;
v_result_num number;
v_return_code varchar2(1);
v_return_msg varchar2(256);
v_job_status_simple varchar2(1);
v_job_status_standard varchar2(1);
v_job_status_enhanced varchar2(2);
v_result_num := Ws_Job_Status
( p_sequence, p_job_name, p_task_name, p_job_id, p_task_id
, NULL, 'Daily Run', 10, NULL
, v_return_code
, v_return_msg
, v_job_status_simple, v_job_status_standard, v_job_status_enhanced
);

DB2

DB2 Parameters: Ws_Job_Status
Callable Routine Type: PROCEDURE.

Parameter Name	Datatype	Mode
p_sequence	INTEGER	IN
p_job_name	VARCHAR(64)	IN
p_task_name	VARCHAR(64)	IN
p_job_id	INTEGER	IN
p_task_id	INTEGER	IN
p_check_sequence	INTEGER	IN
p_check_job	VARCHAR(64)	IN
p_started_in_last_mi	INTEGER	IN
p_started_after_dt	TIMESTAMP	IN
p_return_code	VARCHAR(1)	OUT
p_return_msg	VARCHAR(256)	OUT
p_result	INTEGER	OUT
p_job_status_simple	VARCHAR(1)	OUT
p_job_status_standard	VARCHAR(1)	OUT
p_job_status_enhanced	VARCHAR(2)	OUT

DB2 Examples: Ws_Job_Status

– The p_ VARIABLES are normally PARAMETERS in a RED-generated Procedure.
DECLARE p_sequence integer;
DECLARE p_job_name varchar(256);
DECLARE p_task_name varchar(256);
DECLARE p_job_id integer;
DECLARE p_task_id integer;
DECLARE p_return_msg varchar(256);
DECLARE p_status integer;
DECLARE v_result_num integer;
DECLARE v_return_code varchar(1);
DECLARE v_return_msg varchar(256);
DECLARE v_job_status_simple varchar(1);
DECLARE v_job_status_standard varchar(1);
DECLARE v_job_status_enhanced varchar(2);
CALL [METABASE].Ws_Job_Status
( p_sequence, p_job_name, p_task_name, p_job_id, p_task_id
, NULL, 'Daily Run', 10, NULL
, v_return_code
, v_return_msg
, v_result_num
, v_job_status_simple, v_job_status_standard, v_job_status_enhanced
);

Content

Space Tools

SQL Server

Teradata

DB2