Synopsis
Purges archived job logs that are older than the specified age in days.
Description
Deletes job-related logs that were previously archived (into the WX_WRK_AUDIT_ARCHIVE and WX_WRK_ERROR_ARCHIVE tables via a RED Scheduler and/or RED callable routines, such as Ws_Job_Clear_Logs and Ws_Job_Clear_Logs_By_Date) depending on their age in days.
When the maximum age of the archived logs to retain is exceeded all the older logs are deleted. For example, if 90 days are retained then all the archived logs that are older than 90 days are deleted. If a maximum age of 0 days is specified, then all the archived logs are deleted. Alternatively, the TRUNCATE option can be used to remove all the archived logs, which overrides all other criteria.
Input

Input

Description

Common Input

Includes all 5 inputs of the Callable Routines Common Input.

Maximum Days to Retain

The maximum age (in days) of the archived logs to retain. If 90 days are retained, then all the archived logs that are older than 90 days are purged/deleted. If 0 days are retained, then all the archived logs are purged/deleted.

Job Name to Purge

The name of the job whose archived logs are to be purged. Wild cards are supported. Specifying % will match ALL jobs.

Options

The TRUNCATE option can be used to remove ALL the archived logs, which overrides all other criteria i.e. irrespective of the days to retain or job name.

Output

Output

Description

Return Code

Output Return Code:

  • S     Success.
  • 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.
  • -2     Error. e.g. Due to invalid job name or job not running.
  • -3     Fatal/Unexpected Error.


SQL Server

SQL Server Parameters: Ws_Job_Clear_Archive
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_day_count

INTEGER

IN

@p_job

VARCHAR(64)

IN

@p_options

VARCHAR(256)

IN

@p_return_code

VARCHAR(1)

OUT

@p_return_msg

VARCHAR(256)

OUT

@p_result

INTEGER

OUT

SQL Server Examples: Ws_Job_Clear_Archive

– 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)
EXEC Ws_Job_Clear_Archive
@p_sequence, @p_job_name, @p_task_name, @p_job_id, @p_task_id
, 90, 'Daily Run', ''
, @v_return_code OUTPUT
, @v_return_msg  OUTPUT
, @v_result_num  OUTPUT

Teradata

Teradata Parameters: Ws_Job_Clear_Archive
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_day_count

INTEGER

IN

p_job

VARCHAR(64)

IN

p_options

VARCHAR(256)

IN

p_return_code

VARCHAR(1)

OUT

p_return_msg

VARCHAR(256)

OUT

p_result

INTEGER

OUT

Teradata Examples: Ws_Job_Clear_Archive

– 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);
CALL [METABASE].Ws_Job_Clear_Archive
( p_sequence, p_job_name, p_task_name, p_job_id, p_task_id 
, 90, 'Daily Run', ''
, v_return_code
, v_return_msg
, v_result_num
);

Oracle

Oracle Parameters: Ws_Job_Clear_Archive
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_day_count

NUMBER

IN

p_job

VARCHAR2

IN

p_options

VARCHAR2

IN

p_return_code

VARCHAR2

OUT

p_return_msg

VARCHAR2

OUT

FUNCTION Return Value

NUMBER

OUT-Function

Oracle Examples: Ws_Job_Clear_Archive

– 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_result_num := Ws_Job_Clear_Archive
( p_sequence, p_job_name, p_task_name, p_job_id, p_task_id
, 90, 'Daily Run', ''
, v_return_code
, v_return_msg
);

DB2

DB2 Parameters: Ws_Job_Clear_Archive
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_day_count

INTEGER

IN

p_job

VARCHAR(64)

IN

p_options

VARCHAR(256)

IN

p_return_code

VARCHAR(1)

OUT

p_return_msg

VARCHAR(256)

OUT

p_result

INTEGER

OUT

DB2 Examples: Ws_Job_Clear_Archive

– 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);
CALL [METABASE].Ws_Job_Clear_Archive
( p_sequence, p_job_name, p_task_name, p_job_id, p_task_id
, 90, 'Daily Run', ''
, v_return_code
, v_return_msg
, v_result_num
);


  • No labels