Real-Time SQR Process Monitor Dashboard 1

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

    date $min_date $start_time

  while 1
    do read_prcsrqst
    let $start_time = datenow()
    while datediff(datenow(), $start_time, 'second') < 10

begin-procedure read_prcsrqst
  move 1 to #col
  move 1 to #row
begin-select loops=50
  let $instance = edit(mod(&PRCSINSTANCE, 1000), '099') || ' '
  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  '
      let $type = '    '
  let $name = rpad(&PRCSNAME, 13, ' ')
  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  = '-'
      let $prefix = 'X'
      let $postfix = 'X'
  let $min_time = $prefix || edit($min_date, 'hh:mi') || $postfix || ' '
  let $diststatus = cond(&DISTSTATUS = '5', '*', ' ')
  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
    add 1 to #row
  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-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.


  1. Bob Josephson says: