SQR is a language primarily for batch processing, but it does have limited support for real time, interactive applications. Let’s build a process monitor that is better than (or at least different from) Peoplesoft’s version.
The Original
The Peoplesoft Process Monitor is a webpage that displays a selection of batch processes that have been initiated by the Peoplesoft Process Scheduler. It displays data from the PSPRCSRQST table; process instance number (the unique key), process type, process name, user ID, run date and time, run status, and distribution status. There are data entry fields for the user to specify selection criteria and a refresh button to perform the selection.
The Process Monitor is a flexible and useful feature of Peoplesoft, but it cannot be all things to all people. We can take a different approach an serve different users. Sometimes we might want to monitor all the batch jobs through their lifecycles (queued, initiated, processing, success) as we’re working on something else. We want to open a window, watch the processes run, and have it update itself. We want a dashboard.
This Week’s Design
The SQR Process Monitor will run on a client PC under Microsoft Windows. It will use the show command to open and populate a “DOS” window of 25 lines by 80 columns, updating every 10 seconds. It will loop until the user closes the window. It will abbreviate the data in order fit the last 50 process instances in the window. We’ll use the loops= parameter of begin-select to exit the selection at the right point.
I usually use show without position parameters to document the operation of the SQR program in the log file. The output of show also appears in the “DOS” window, filling it and then scrolling it to allow the latest output to be visible. The user can scroll backwards to a limited extent. This application might run for hours, and might display any particular process instance many times. We will use show position parameters to stabilize the display and convey additional information about the recency of each process instance.
Application Designer defines PRCSINSTANCE as a ten digit number. The Process Scheduler assigns those numbers sequentially. We will display the last three digits with each process.
Peoplesoft delivers sixteen process types. We will consolidate and abbreviate them as AE (Application Engine), CBL (COBOL), CRW, CRY (Crystal Report), CUB (Cube Builder), DM (Data Mover), DB (Database Agent), MSG (Message Agent), NV (nVision), OE (Optimization Engine), JOB (PS Job), SQR, WRD (Microsoft Word for Windows).
There are four date-time fields for when the process was requested, when was the earliest time it could be run, when did it begin, and when did it end. We will work backwards, end to begin to run, and show the first date-time that is not null. It should be sufficient to show hours and minutes in military (24 hour) time. We’ll use the format 14:00- to indicate that the process will begin, or did begin, at 2:00 PM and is not finished. We’ll use the format -14:15 to indicate that the process ran until 2:15 PM and ended.
There are 13 run status values. We’ll use their XLATSHORTNAME from the PSXLATITEM table to describe them in ten characters or less. There are eight distribution status values, but the critical issue is that the output files are either posted or not. We will indicate that the files are posted by putting an asterisk at the end of the run status description.
One of the advantages of show over display is that it can output a list of values rather than just one. We’ve used rpad() on several of the values to ensure that each entry is aligned with the others and that it completely overwrites the previous entry in that position.
The Program
begin-setup
declare-variable
date $min_date $start_time
end-declare
end-setup
begin-program
while 1
do read_prcsrqst
let $start_time = datenow()
while datediff(datenow(), $start_time, 'second') < 10
end-while
end-while
end-program
begin-procedure read_prcsrqst
move 1 to #col
move 1 to #row
begin-select loops=50
PRCSINSTANCE
let $instance = edit(mod(&PRCSINSTANCE, 1000), '099') || ' '
PRCSTYPE
evaluate &PRCSTYPE
when = 'Application Engine'
let $type = 'AE '
when = 'COBOL SQL'
let $type = 'CBL '
when = 'Crw Online'
let $type = 'CRW '
when = 'Crystal'
let $type = 'CRY '
when = 'Cube Builder'
let $type = 'CUB '
when = 'Data Mover'
let $type = 'DM '
when = 'Database Agent'
let $type = 'DBA '
when = 'Message Agent API'
let $type = 'MSG '
when = 'Optimization Engine'
let $type = 'OE '
when = 'PSJob'
let $type = 'JOB '
when = 'SQR Process'
when = 'SQR Report'
when = 'SQR Report For WF Delivery'
let $type = 'SQR '
when = 'Winword'
let $type = 'WRD '
when = 'nVision-Report'
when = 'nVision-ReportBook'
let $type = 'NV '
when-other
let $type = ' '
end-evaluate
PRCSNAME
let $name = rpad(&PRCSNAME, 13, ' ')
RUNDTTM
RQSTDTTM
BEGINDTTM
ENDDTTM
let $min_date = strtodate('01-JAN-1900', 'dd-mon-yyyy')
evaluate $min_date
when < &ENDDTTM
let $min_date = &ENDDTTM
let $prefix = '-'
let $postfix = ''
when < &BEGINDTTM
let $min_date = &BEGINDTTM
let $prefix = ''
let $postfix = '-'
when < &RUNDTTM
let $min_date = &RUNDTTM
let $prefix = ''
let $postfix = '-'
when-other
let $prefix = 'X'
let $postfix = 'X'
end-evaluate
let $min_time = $prefix || edit($min_date, 'hh:mi') || $postfix || ' '
DISTSTATUS
let $diststatus = cond(&DISTSTATUS = '5', '*', ' ')
XLATSHORTNAME
let $runstatus = rpad(&XLATSHORTNAME || $diststatus, 11, ' ')
show (#row,#col) $instance $type $name $min_name $min_time $runstatus noline
if #row = 25
move 41 to #col
move 1 to #row
else
add 1 to #row
end-if
from PSPRCSRQST P, PSXLATITEM X
where X.FIELDNAME = 'RUNSTATUS'
and X.FIELDVALUE = P.RUNSTATUS
and X.EFFDT = (select max(EFFDT)
from PSXLATITEM
where FIELDNAME = X.FIELDNAME
and FIELDVALUE = X.FIELDVALUE
and EFFDT <= SYSDATE)
order by P.PRCSINSTANCE desc
end-select
end-procedure read_prcsrqst
Looking Ahead
This is somewhat useful, but (1) boring, (2) hard to read, and (3) not necessarily the information we want. Next week, we’ll try again.
ME WANT GUI FRONT END!
Eloquent, yet Twitter compliant.