ERP Associates, Inc.

In my experience, scheduling tools like Autosys are really good about creating schedules with dependencies, firing off notifications, and working with your trouble ticketing system.  But how they kick off these jobs is very simple:  They execute commands from the Operating System command line.

Here’s something I’ve been working on to allow my client to use a 3rd party tool like Autosys to drive the batch schedule, but do it in such a way that lets process scheduler run the individual processes so we can see the status & logs in process monitor and restart app engine jobs through the front end when they fail.

Before we get started, let me just say that I realize that 3rd party scheduling tools like Autosys sell a PeopleSoft plugin that will do this.  But these days not every company is willing to spring for the extra cash for nice-to-have functionality like this.  If you’re reading this article chances are you’re not the guy in charge of your IT organization’s budget and you know what I mean.

Usually to set up your batch schedule, you come up with a generic script to fire off an app engine job based on certain parameters like run control id and process name.  Then you create the same script for SQR’s, COBOL’s, throw in some FTP scripts to move files around, spend like 10,000 hours debugging, and viola you’re done.

Now there’s no reason why the scripts that launch your AE’s, SQR’s or COBOL’s couldn’t schedule the process inside of process scheduler instead of launching it directly.  Except for the fact that you probably don’t want to do all of the required inserts to the process scheduler tables manually because you never know when you’ll screw up process scheduler, either now by getting the next PRCSINSTANCE wrong or in the future when you do a PeopleTools upgrade.

But PeopleCode has a ProcessRequest API that takes care of all of the details regarding how to schedule a process.  And you can write PeopleCode in an Application Engine program which can be called from a command line by Autosys.  If we write our code so that our AE program keeps running until the job it scheduled completes and returns a response of Success or Fail so that Autosys can go to the next job or handle the exception as required, we’ll have something pretty cool.

Sound like fun?  Great, let’s get started!

Picture if you will:  An Application Engine program with two steps.  Step 1 is called SetupAE, and it consists of a single SQL that loads the State Record with information from the run control table about which process to run.   Fields in the state record include OPRID, RUN_CNTL_ID, PRCS_RUN_ID (representing the run control of the process you want to schedule), PRCSTYPE, PROCESSNAME, etc.

Step 2 is called Schedule, and it consists of a single PeopleCode program.   To get the return codes correct, the On Return for the PeopleCode program is set to Abort, and the On Error for the Step is set to abort.

The PeopleCode program gets right to the point, calling the CreateProcessRequest method to create the process request object, it sets the Run Control Id’s, Output Options and distribution options, and calls the Schedule() method.  If the status doesn’t return something valid, we call Exit(1).

Local ProcessRequest &RQST;
&RQST = CreateProcessRequest(ZPT_SCHD_AET.PRCSTYPE, ZPT_SCHD_AET.PROCESSNAME);
&RQST.RunControlID = ZPT_SCHD_AET.ZPTF_PRCS_RUN_ID;
&RQST.SetOutputOption(ZPT_SCHD_AET.OUTDESTTYPE, ZPT_SCHD_AET.OUTDESTFORMAT, ZPT_SCHD_AET.OUTDEST);

&RQST.AddDistributionOption("User", "%OPRID");

&RQST.Schedule();
If &RQST.Status <> 0 Then
   Exit (1);
End-If;

The rest of the program is geared toward waiting until the process finishes and returning the appropriate return code when it does.  And we want to do it without causing much load on the system by looping.  So we get the process instance from the ProcessInstance property, select Runstatus from PSPRCSRQST, and while the runstatus is Queued, Initiated or Processing sleep for 20 seconds and loop.  To do the sleep, I used a PL/SQL function called DBMS_LOCK.sleep because I couldn’t find a sleep function for PeopleCode.  If you have a better way to do it let me know, but we do need something to slow us down in this loop so we don’t pound the database with too many requests.  Once we’re done with the loop, we’ll use an Exit(1) if it’s an error, and an Exit(0) otherwise. 

Here’s what that code looks like:

ProcInst = &RQST.ProcessInstance;
&ret = SQLExec("select RUNSTATUS from PSPRCSRQST where PRCSINSTANCE = :1", &ProcInst, &RunStatus);
While &RunStatus = 5 Or
      &RunStatus = 6 Or
      &RunStatus = 7
   &ret = SQLExec("exec DBMS_LOCK.sleep(seconds => 20)");
   &ret = SQLExec("select RUNSTATUS from PSPRCSRQST where PRCSINSTANCE = :1", &ProcInst, &RunStatus);
End-While;
If &RunStatus <> 9 Then
   Exit (1);
Else
   Exit (0);
End-If;

The App Engine properties include “Disable Restart”, which means you don’t have to go through an elaborate restart mechanism if it dies for some reason.

To call the app engine on Unix, I built a shell script to set PS_SERVER_CFG environment variable to be the full path down to psprcs.cfg, and it calls psae with all of the parameters you might expect.

#!/usr/bin/ksh
export PS_SERVER_CFG=$PS_HOME/appserv/prcs/$ORACLE_SID/psprcs.cfg
psae -CT ORACLE -CD PS90HRDV -CO PSBATCH -CP mysecretpwd -R AdHoc -I 0 -AI Z_SCHDPRCS -OT 6 -FP /tmp
export ret=$?
echo $ret

We did go ahead and create a run control page to launch the app engine from.  Although it will seldom be run from the run control page, the run control itself will be where it looks up the parameters necessary to schedule the job.  We considered building other related information as part of the run control like e-mail notifications and where to move any file it might generate to, but that’d just mean creating Unix scripts to read it and for now we decided standard parameter files would be more straightforward.  Here’s what it looks like:

This isn’t live yet, and we certainly have a robust contingency plan if this doesn’t work.  But so far testing has been very encouraging.  I’ve had as many as 4 of these processes running concurrently and they all return recognizable return codes (0=success, 1=fail) on the command line.  One issue we need to figure out is the best way to restart app engine jobs.  If a job successfully completes after restarting it from process monitor, we need to figure out how to let Autosys know to clear the error and move on.  That’ll probably be manual, but hey you can’t have everything.