Friday, September 07, 2007

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.

16 comments :

Yardboy said...

Hi -

This was a great find. I'm trying to implement, but PeopleSoft doesn't seem to be kicking off the script. I can pull the parameters from the process monitor and issue that command, and everything runs perfectly. When kicked off from process schedule, though, I get no joy - stays in queued and nothing kicks off on the server.

Would really appreciate some assistance. cayce -dot- balara -at- crowley.com

Yardboy said...

Never mind, my bad - recycled my process scheduler and all is well.

Thanks again - great tip!

Unknown said...

You wouldn't happen to have a similar wrapper script from windows commands?

ras said...

Great tip. In the override options, how can we pass multiple parameters to the unix script? I only see first one (parm1) I have tried

/scripts/bobtest.sh parm1 parm2 parm3

/scripts/bobtest.sh parm1, parm2, parm3,

/scripts/bobtest.sh %parm1 %parm2 %parm3
Thanks

ras said...

Opps...Helps if you save the called unix script ;) scripts/bobtest.sh BOB Was Here

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

This script is running under Process Scheduler
value = BOB
value = was
value = here
Setting process request 7691 to status 9
Sat Sep 13 09:45:44 EDT 2008

Unknown said...

Hi

Is this technique capable of doing Sftp as well ?
I have shell script that has sftp command.
When i run that this way it says "sftp command not found".It runs succesfully through crontab though......
Any ideas......

David Kurtz said...

If you can script it, you can run from the Process Scheduler. Problems like this are often down to environment and path. Try listing the environment variables with either set or env. I sometimes use set -x to see how execution of the script progresses.

Unknown said...

Hi
I could resolve the "sftp command not found" error..However when i run the shell script from process scheduler it says "Couldn't wait for ssh process: No child processes"

Result - Process monitor run status is error.SFTP shell script process and does get the file transfer job, though this is only visible through crontab

Think i got real close and stuck with this last one
any inputs ......

Chris said...

Krishna,
were you able to solve the "Couldn't wait for ssh process: No child processes" issue?

Thanks in advance,
Chris

ras said...

Is there a way to hide the password from command line?

Kicked off from the Process Scheduler, the from command line issued: ps -ef|grep uv

Results (password replaced with x's)

psadmin 934092 1282132 10 11:40:52 - 0:06 /bin/ksh /u01/psoft/psenv/CSDEV/UVAcust/scripts/uv_sf_nelnet_billfile_ftp.sh bobtest rs2wq SYSADM/xxxxxxxx@CSDEV
psadmin 1282132 1683642 0 11:40:52 - 0:00 /bin/ksh /u01/psoft/psenv/CSDEV/UVAcust/scripts/uv_psft.sh CSDEV SYSADM xxxxxxxx 36173 bobtest rs2wq /u01/psoft/psenv/CSDEV/UVAcust/scripts/uv_sf_nelnet_billfile_ftp.sh

Parameter List:
%%DBNAME%% %%ACCESSID%% %%ACCESSPSWD%% %%INSTANCE%% %%RUNCNTLID%% %%OPRID%%

Unknown said...

Hi

Nopes , no luck with the "Couldn't wait for ssh process: No child processes" issue .this comes while running the SFTP scripts through the proces scheduler.

If you have any solution do let me know.

Thanks

Unknown said...

This is pretty awesome. Thanks a lot David.

Unknown said...

Hi David, we met a few years back - do you remember coming to Perot Systems? Anyway, I have a question about Unix Scripts and report distribution. It seems like the report distribution feature only works for generic process types of SQR and App Engine. Using OTH for Unix scripting does not allow saving the report distribution (User or role)values when I run the job. When I trace this, I'm not finding anywhere this is being saved, so my fear is that this information is being deleted/saved within Peoplesoft propietary code which we don't have access to. I did update the process output destination type to defalut to Web as Web and Client are the only two that allow this functionality. Any ideas?

Paul Tap said...

Hi David,

I think I spotted a minor problem in your psft.sh script, where it updates the PSPRCSRQST table with status 7. At that point the variable PRCSRTNCD doesn't have a value which makes the sql fail. Giving it an initial value of 0 does the job.

Great script. I've been writing a lot of stuff over the past months that I can bring into the PeopleSoft envionment now.

I noticed some issues with ssh/sftp in other posts. We have similar situations, so if I get that to run, I'll post another comment.

David Kurtz said...

I have corrected the script in this blog posting, and uploaded a new script to the Go-Faster website.

Sam10 said...

Hi David, I have worked with you in BT. I am getting the error when i try to run the script via process scheduler as you can see from the log. But i am able to successfully execute from unix.

/local/apps/psoft/DEVM/pt849_hcm/bin/psft.sh:sh sdtest.sh 0
Sat Sep 4 00:36:24 BST 2010
Linux hwl027a002 2.6.9-55.ELsmp #1 SMP Fri Apr 20 17:03:35 EDT 2007 i686 i686 i386 GNU/Linux
parameter 1: begindttm parameter 2: 7
PRCSINSTANCE : 1226516
Setting process request 1226516 to status 7
prcsapi: /local/apps/psoft/DEVM/pt849_hcm/bin/psft.sh[53]: sqlplus: not found
RETURN CODE: 127
SQL *Plus Error Return code: 0
Sat Sep 4 00:36:24 BST 2010
welcome
Sat Sep 4 00:36:24 BST 2010
parameter 1: enddttm parameter 2: 9
PRCSINSTANCE : 1226516
Setting process request 1226516 to status 9
prcsapi: /local/apps/psoft/DEVM/pt849_hcm/bin/psft.sh[62]: sqlplus: not found
RETURN CODE: 127
SQL *Plus Error Return code: 0
Sat Sep 4 00:36:24 BST 2010