Running Unix Commands and Scripts from the PeopleSoft Process Scheduler

(Update 26.2.2017) This article has been updated.  See http://blog.psftdba.com/2017/02/process-scheduler-shell-script.html.

Some PeopleSoft systems generate interfaces files that are then delivered to other systems by other shell scripts. These scripts may simply be initiated by the UNIX cron facility. The problem with this is that the scripts run irrespective of whether the PeopleSoft process that generated the interface file ran and completed successfully.

It is possible to use the Process Scheduler to run operating system commands, and to have those command interact appropriately with the generic Process Scheduler functionality.

Process Type Definition

First you need to create a new process type, I chose to call it ‘Shell Script’, that will run a named shell script, psft.sh. This wrapper script performs the interaction with the Process Scheduler and it in turn calls the script that is to be executed. The name of the database, the access ID and password, and the process instance are passed to the wrapper. Other parameters will be appended in the individual Process Definition.

Process Definition

It is necessary to create a Process Definition for each system command or script that is to be executed by the Process Scheduler. You could simply run the command directly from the Process Scheduler but then it could not be API aware, and the status in the Process Monitor will always be success when it script completes. When using the wrapper script, the API Aware check box should be checked.

The name of the script of system command and any parameters should be APPENDED to the command line defined in the Process Type definition, as shown below.


Wrapper Script (psft.sh)

This is the wrapper script that is specified in the Process Definition.

#!/bin/ksh
#
# Script:  psft.sh
#
# Syntax:  psft.sh DBNAME ACCESSID ACCESSPSWD PRCSINSTANCE 
# where
# DBNAME is the name of the PeopleSoft datbase with a corresponding TNS entry
# ACCESSID is the schema containing the PeopleSoft database
# ACCESSPSWD is the password to ACCESSID
# PRCSINSTANCE is the process instance number supplied by PeopleSoft
#
# Purpose: To start Standard UNIX Shell Script from Process Scheduler, and interface with the PeopleSoft Process Scheduler
#

if [ $# -lt 4 ]; then
echo "Usage $0:     "
exit 1
fi

CONNECT=$2/$3@$1 
PRCSINSTANCE=$4
shift 4

#
# Function to set status of API aware process instance
#
function prcsapi 
{
if [ $# -lt 2 ]; then
echo "Parameter Error in function $0"
exit 1
fi

TIMESTAMPCOL=${1}
STATUS=${2}

if [ ${PRCSINSTANCE} -gt 0 ];then
echo "Setting process request ${PRCSINSTANCE} to status ${STATUS}"
sqlplus -S /nolog <<!
set termout off echo off feedback off verify off
connect ${CONNECT}
UPDATE  psprcsque
SET  runstatus = ${STATUS}
, sessionidnum = $$ 
,  lastupddttm = SYSDATE
WHERE  prcsinstance = ${PRCSINSTANCE}
;
UPDATE psprcsrqst 
SET  runstatus = ${STATUS}
,  prcsrtncd = ${PRCSRTNCD}
, continuejob = DECODE(${STATUS},2,1,7,1,9,1,0) 
, ${TIMESTAMPCOL} = SYSDATE
,  lastupddttm = SYSDATE
WHERE  prcsinstance = ${PRCSINSTANCE}
;
COMMIT;
exit
!

RET=$?
if [ ! $RET ];then
echo "SQL*Plus Error Return Code: $?"
fi
fi
}

#
# Process files in ${PSPRCSLOGDIR)/*
#

function logfiles 
{
#set -x 
SEQNUM=0
if [ -d "${PSPRCSLOGDIR}" ]; then
for FILELIST in ${PSPRCSLOGDIR}/*
do
if [ "${FILELIST}" != "${PSPRCSLOGFILE}" ];then
SEQNUM=$(expr 1 + ${SEQNUM})

sqlplus -S /nolog <<!
set termout off echo off feedback off verify off
connect ${CONNECT}
INSERT  INTO psprcsrqstfile
( prcsinstance, seqnum, prcsrqstfile)
VALUES (${PRCSINSTANCE}
, ${SEQNUM}
, '${FILELIST}');
COMMIT;
exit
!
RET=$?
if [ ! $RET ];then
echo "SQL*Plus Error Return Code: $?"
fi
fi
done
else
echo "Directory ${PSPRCSLOGDIR} does not exist"
fi

}

#
# Main Execution Starts Here
#

echo $0:$*
date
uname -a
#set
PRCSRTNCD=0
prcsapi begindttm 7 

#Run the command
$*
PRCSRTNCD=$? 

if [ ${PRCSRTNCD} -ne 0 ]; then
prcsapi enddttm 3 # failure
else
prcsapi enddttm 9 # success
fi

date

The newly defined Process can be run just as any other process is usually run. Any output from the script on the standard output channel is captured by the Process Scheduler and written to a logfile that can then be viewed from the View Log/Trace facility within Process Monitor.


Two files are shown in the Process Monitor.

• OTH_DMKTEST_1561.log is the standard output of the script that was captured by the Process Scheduler.
• dmktest.log is a file emitted by the called shell script dmktest.sh to the reporting directory.

Demonstration

This is the test script called by the Process Definition DMKTEST.

banner "HelloWorld" 

BASENAME=$(basename $0 .sh)
if [ -d "${PSPRCSLOGDIR}" ] ; then
echo "This script is running under Process Scheduler"
cp $0 ${PSPRCSLOGDIR}/${BASENAME}.log 
else
echo "This script is not running under Process Scheduler"
fi

exit $*

This is the standard output of the script found in file OTH_DMKTEST_1561.log

/usr/local/bin/psft.sh:/usr/local/bin//dmktest.sh 0
Mon  2 Jul 11:53:15 2007
Setting process request 1561 to status 7
#     #                                 #     #
#     #  ######  #       #        ####  #  #  #   ####   #####   #       #####
#     #  #       #       #       #    # #  #  #  #    #  #    #  #       #    #
#######  #####   #       #       #    # #  #  #  #    #  #    #  #       #    #
#     #  #       #       #       #    # #  #  #  #    #  #####   #       #    #
#     #  #       #       #       #    # #  #  #  #    #  #   #   #       #    #
#     #  ######  ######  ######   ####   ## ##    ####   #    #  ######  #####

This script is running under Process Scheduler
Setting process request 1561 to status 9
Mon  2 Jul 11:53:16 2007

A more detailed version of this document can be found at http://www.go-faster.co.uk/docs/process_scheduler_shell_scripts.pdf

Please not this blog is not a support site.  Please do not use the comments to ask for support on this script, you can contact me directly.  You will need a basic knowledge of Unix shell scripting to use this technique.