How To Prevent An SQR Program From Running Twice

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?

6 Comments

  1. Bob Josephson says:

    Very cool idea, checking whether the program is running or (even cooler) has run recently. But isn’t it an even more likely scenario that two different people, on different PCs, would run the same program? Forgive my ignorance (I’m more suited to writing a C/Java blog myself :-) but does this SQR program run on the server or on clients? If the latter, you need to use a network resource for the lock.

    • administrator says:

      Good question. This technique uses a database table maintained by the Peoplesoft Process Scheduler. It will detect any copy of the program that has been launched by Peoplesoft, no matter which file server is running the SQR program. Users should not have direct access to the pssqr.exe interpreter.

  2. Sundeep Kumar says:

    !Below is the Pseudo code of finding the election date.

    Let #day = 1
    Let $Find-Monday = ‘N’
    While #day <8
    Do Find-Monday
    If $Find-Monday = ‘Y’
    Break
    End-If
    Let #day = #day +1
    End-While
    $Election-Date = #day+1

    Begin-Procedure Find-Monday
    $Date_Char = ‘11/0′ || to_char(#day) || ‘/’ || $year
    $Date = StrToDate($Date_Char,’MM/DD/YYYY’)
    Begin-Select
    to_char($Date,’D') &Day
    If &Day = ‘2′
    Let $Find-Monday = ‘Y’
    End-If
    End-Select
    End-Procedure Find-Monday

    • administrator says:

      Thank you for responding to my brain teaser and welcome to my blog, Sundeep. Good solution – I hope you enjoyed working it out.

  3. Sundeep Kumar says:

    Yeah i enjoyed solving this riddle. Post some more riddles. I’ll Try solving them.

    • administrator says:

      Look around. The earlier blog entries have brain teasers, most of which were never solved. I’ll try to invent some more.