DB2 Examples:
1. DB2 example of a call from a Stored Procedure:
Add the following to the declaration section of the stored procedure:
DECLARE v_result INTEGER;
DECLARE v_return_code VARCHAR(1);
DECLARE v_return_msg VARCHAR(256);
Then add the call to the procedure:
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);
Perhaps add a check of the result:
IF ( v_result <> 1 ) THEN
SET p_status = v_result;
SET p_return_msg = 'Failed to clear Job archive. Return Code was ' || v_return_code;
SET p_return_msg = p_return_msg || '. ' || RTRIM(v_return_msg);
END IF;
2. DB2 example of a call from the DB2 Command Line Processor (db2.exe):
Enter the following after connecting (the metadata is in a schema called QAD Data Warehouse Designer):
call Data Warehouse Designer.Ws_Job_Clear_Archive(0,'ClearArch','Clear',0,0,90,'Daily Run','',?,?,?)
The following out is typical:
Value of output parameters
--------------------------
Parameter Name : P_RETURN_CODE
Parameter Value : S
Parameter Name : P_RETURN_MSG
Parameter Value : 0 audit archive logs removed and 0 detail archive logs removed.
Parameter Name : P_RESULT
Parameter Value : 1
Return Status = 0
3. DB2 example of a call from QAD SQL Admin (the metadata is in a schema called QAD Data Warehouse Designer):
{Call Data Warehouse Designer.Ws_Job_Clear_Archive(0,'ClearArch','Clear',0,0,90,'Daily Run','',?,?,?)}
Ws_Job_Clear_Logs
This procedure allows the removal of old job logs and the archiving of the associated audit trail. The number of logs to retain is specified and all older logs are deleted. If zero is specified all logs are deleted. For example if 3 is specified then the latest 3 logs are retained and all older logs are removed.
Note: The functionality that this function provides is also provided via the scheduler. Set the number of logs to keep in the job properties and the scheduler will automatically call this function to clear out the old logs.