It’s not that hard for a busy Peoplesoft user accidently to run an SQR program, or any other batch process, twice. After we click OK on the Process Scheduler Request page, we return to the Run Control page. Throw in a phone call and we might forget that the process is running, not notice the links to Report Manager and Process Monitor, and run the process again.

OK, we would never do it, but our users might do it.

If the SQR program is a report, it may not matter if it runs twice. If it adds or changes rows in a table, it could take a lot of work and a long time to undo the effects of a second, unwanted process. If the two instances run concurrently, adding, changing, or deleting each other’s rows, the mess could take even longer to untangle.

Those programs should check for concurrent or recent instances of themselves. Here’s a way to check whether another instance of the program is running or about to run. This procedure calls the Parse_Filename procedure that we discussed in the last post.

begin-procedure check_concurrent_programs local
! $sqr-program and $prcs_process_instance are global variables and
! this is a local procedure, so put an underscore after the $.
  do parse_filename($_sqr-program, $drive, $path, $prcsname, $ext)
  uppercase $prcsname
begin-select
PRCSINSTANCE
PRCSNAME
  show &PRCSNAME ' is already running - process instance ' &PRCSINSTANCE
  stop
 from PSPRCSRQST
! Look for process instances before this one, not after this one,
! because a process that is scheduled for periodic execution (i.e.
! once a day) will have tomorrow’s instance queued right after
! today’s instance starts running.
where PRCSINSTANCE < $_prcs_process_instance
  and PRCSNAME = $prcsname
  and RUNDTTM <= SYSDATE
  and RUNSTATUS in ('5','6','7') ! queued, initiated, processing
end-select
end-procedure check_concurrent_programs

This routine chooses to stop itself if it finds a concurrent process. Alternately, a program could look for another process and stop if it cannot find it; “don’t run me unless my precedessor ran within the last hour and was successful.” Or a program could wait for another process; “loop until my predecessor ran within the last hour and was successful.”

Here are the character string values of the RUNSTATUS column in the PSPRCSRQST table.

'1'	Cancel
'2'	Delete
'3'	Error
'4'	Hold
'5'	Queued
'6'	Initiated
'7'	Processing
'8'	Cancelled
'9'	Success
'10'	Not Successful
'16'	Pending
'17'	Success With Warning
'18'	Blocked
'19'	Restart

Looking Ahead

Next week, the SQR Evaluate command will face-off with the C and Java Switch command. Which one will win? Hint: I’m not biased or anything, but this is not a C/Java blog.

Brain Teaser

Meanwhile, here is a brain teaser. Please post solutions as comments.

In the United States, most general elections occur on the first Tuesday following the first Monday in November. Given #year is a variable containing the integer value of a year or $year is a variable containing the string of the integer value of a year, what are some ways to determine in SQR the date of the general election in that year?