The following sample script can be used to delete successfully provisioned WOs from tbl_work_ord that are older than a specified number of days. In addition, this script calls the SSP_orphan_purge function, which deletes all orphaned records. When you delete WOs, information related to these WO may remain in other tables. The SSP_orphan_purge function purges information that is related to WOs that have been removed from the database. The affected tables include:

• tbl_asap_stats

• tbl_info_parm

• tbl_srq

• tbl_srq_csdl

• tbl_srq_log

• tbl_asdl_log

• tbl_srq_parm

• tbl_srq_asdl_parm

• tbl_wo_event_queue

• tbl_wo_audit

• tbl_usr_wo_prop

• tbl_aux_wo_prop

  Note:

  The SSP_orphan_purge function is time-consuming and requires considerable system resources. Therefore, it should not run during peak hours.

create proc SSP_db_admin @days int
as
begin
    declare @cutoff_dts datetime
    if (@days is not null and @days > 0)
    begin
        select @cutoff_dts = dateadd(day, -@days, getdate())
        delete tbl_wrk_ord
        where comp_dts < @cutoff_dts and wo_stat = 104
    end
    exec SSP_orphan_purge
end

You can customize this script in a variety of ways (for example, you can delete WOs that have successfully completed (104) or orders that have completed and failed due to timeout). When ASAP triggers the DB_admin procedure, ASAP also performs other optimization, such as recompiling stored procedures for optimal database access, and so forth.

In the following example, after the function has deleted each set of 1000 orders, the function performs a commit and the rollback segment is flushed. This prevents the Oracle rollback segment from being exceeded. This example also employs the SSP_orphan_purge function described for the previous example.

CREATE OR REPLACE FUNCTION SSP_db_admin(
days INTEGER )
RETURN INTEGER
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
cutoff_dts DATE;
retval integer;
BEGIN
IF (SSP_db_admin.days IS NOT NULL AND SSP_db_admin.days > 0) THEN
BEGIN
SSP_db_admin.cutoff_dts :=  SYSDATE-SSP_db_admin.days;
BEGIN
StoO_error   := 0;
StoO_rowcnt  := 0;
-- Created a loop to split the deletion of orders in portions of 1000
-- to remove the risk of reaching the rollback segment limit
-- NOTE! The orphans are deleted by a trigger defined on the
-- tbl_wrk_ord table and not by the SSP_orphan_purge function call
-- below
LOOP
  DELETE  FROM tbl_wrk_ord
WHERE comp_dts < SSP_db_admin.cutoff_dts
AND wo_stat = 104
AND rownum <= 1000;
 EXIT WHEN SQL%ROWCOUNT = 0;
  COMMIT;
END LOOP;
StoO_rowcnt := SQL%ROWCOUNT;
COMMIT WORK;
-- EXCEPTION
-- WHEN OTHERS THEN
-- StoO_error  := SQLCODE;
END;

END;
END IF;
BEGIN
retval := SSP_orphan_purge;
EXCEPTION
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
END;
RETURN 0;
END;
/

The following is a sample cron script that clears alarm entries, event logs and process information.

###########################################################################
#
# ASAP database and log housekeeping script
#
# Call this script with one parameter, specifying the number of days. 
# Alarm entries, event logs and process info
# logs are cleared. Log files and directories are also cleared.
#
# The script will exit if the number of days is less than 5
#
############################################################################

. $HOME/.profile > /dev/null

PROG_NAME=`basename "$0"`

if [ "$1" == "" ]
then
    echo "Usage $PROG_NAME <admin_days>"
    exit
fi

let ADMIN_DAYS=$1+0
if [ "$?" != "0" ]
then
    echo "Number of days must be numeric"
    exit
fi

if [ $ADMIN_DAYS -lt 5 ]
then
    echo "Cannot run with less than 5 days lead-time"
    exit
fi


############################################################################
# Clear CTRL entries
############################################################################
echo "Deleting alarm logs, event logs and process info from CTRL more than $ADMIN_DAYS days old"

sqlplus -s $CTRL_USER/$CTRL_USER << HERE

var admin_days number;
exec :admin_days := $ADMIN_DAYS;

delete from TBL_ALARM_LOG where start_dts < sysdate - :admin_days;
commit;
delete from TBL_EVENT_LOG where event_dts < sysdate - :admin_days;
commit;
delete from TBL_PROCESS_INFO where info_dts < sysdate - :admin_days;
commit;

HERE

############################################################################
# Truncate ASAP.Console and ControlProgramOutput so that old entries can be
# cleared with the diagnostic files
############################################################################
cd $LOGDIR
FILE1=ASAP.Console
FILE2=ControlProgramOutput
CUR_DTS=`date +'%Y_%b_%d-%T'`

if [ -f $FILE1 ]; then
    NEWFILE=$FILE1.diag.$CUR_DTS
    echo Copying current $FILE1 to $NEWFILE
    cp $FILE1 $NEWFILE
    cp /dev/null $FILE1
else
    echo $LOGDIR/$FILE1 not found
fi

if [ -f $FILE2 ]; then
    NEWFILE=$FILE2.diag.$CUR_DTS
    echo Copying current $FILE2 to $NEWFILE
    cp $FILE2 $NEWFILE
    cp /dev/null $FILE2
else
    echo $LOGDIR/$FILE2 not found
fi

############################################################################
# Clear log files
############################################################################
echo "Clearing old ASAP log files..."

find . -type f -name '*diag*' -atime +$ADMIN_DAYS -exec echo Removing file {} \; -exec rm -f {} \;

############################################################################
# Clear log directories not accessed within admin days
############################################################################
echo "Clearing old ASAP log directories..."

find . -type d -name '2*' -atime +$ADMIN_DAYS -exec echo Removing directory {} \; -exec rm -rf {} \;

cd -

echo "$PROG_NAME finished"

You run the purge application by running the purgeOrders script. This script is located in the $ASAP_BASE/scripts directory. You must source the ASAP Environment_Profile before running this script.

The command-line syntax of the purgeOrders script is:

purgeOrders [-t <number of threads>] [-l <diag_level>] [-f] [-h]

where:

-t <number of threads> (optional) is the number of threads. If specified, it overrides the value of DB_PURGE_THREADS in ASAP.cfg file.

-l <diag_level> (optional) specifies the diagnostic level (KERN, LOW, SANE, or PROG). The default is SANE.

-f forces the script to run in non-interactive mode. Without this option, the script displays an interactive prompt to confirm running the purge application.

-h prints the usage information.

The purge application reads the following configuration parameters from the SARM section of the ASAP.cfg file.

The purge application logs diagnostic messages to the $ASAP_BASE/DATA/logs/<date>/SARM<ENV_ID>_PURGE.diag file.

The purge application logs progress messages to the diagnostic file while the purge threads are running, and after all threads are finished. The diagnostic messages show the elapsed time, number of orders deleted, and the purging rate.

Since the purge application is a Java application that runs outside of the SARM server, you need to schedule the new purge application to be run outside of SARM. This is done using the UNIX or Linux cron utility. When you run the purge application from cron, you should use the -f command line option so that the purgeOrders script runs in non-interactive mode.

If there is another instance of the purge application running against the same SARM database, the purgeOrders script prints the following error message and stops:

Error: There is another AsapParallelPurge job running for SARM<ENV_ID>. Exiting ... 

If the old purging mechanism is enabled, by setting DB_ADMIN_ON to 1 in the SARM section of ASAP.cfg, the new purge application logs the following warning message to the diagnostic file and continues to run.

WARNING: The SARM database administration thread is enabled (DB_ADMIN_ON=1 in ASAP.cfg). This may result in multiple purge jobs being run concurrently.

You can provide your own stored procedures for determining the SRQ ID range of the work orders and purging the work orders. You do this by specifying the names of your stored procedures in the DB_PURGE_GET_RANGE_PROC and DB_PURGE_RANGE_PROC parameters in the ASAP.cfg.

To use the purge application:

1. Disable the existing SARM database purge operation by setting DB_ADMIN_ON to 0 in the SARM section of the ASAP.cfg file.
2. In the SARM section of the ASAP.cfg file, review the default settings of the DB_PURGE* parameters and change them if necessary. For example:
   • Change DB_PURGE_DAYS to comply with your company's data-retention policy.
   • Change DB_PURGE_THREADS to increase or decrease the level of parallelism.
   • Change DB_PURGE_MAX_TIME if you want to specify a time limit for purging.
   • Change DB_PURGE_GET_RANGE_PROC and DB_PURGE_RANGE_PROC if you need to use your own stored procedures.
3. Schedule the purgeOrders script to be run regularly using an external scheduling tool.