Last week we wrote a real-time application in SQR. Yes, in SQR, the batch programming language. That should have been more thrilling than it turned out to be. Let’s try to make it more interesting, accessible, and informative.

Imagine Using It

Unlike the Peoplesoft Process Monitor, our dashboard doesn’t let the user select which processes to display. It matches the information from the Process List page except for user ID. However, the other pages (Process Detail, Parameters, Transfer, Message Log, Batch Timings, and View Log/Trace) offer information that our version does not.

The architectural difference is that the Peoplesoft Process Monitor responds to user actions while our dashboard updates automatically. The Peoplesoft Process Monitor can either monitor many processes in minimal detail or delve into a single process in extensive detail, while our dashboard only monitors fifty processes in minimal detail.

Although PCs can tell time, and so much more, there is still demand for wrist watches. When might we prefer to use the dashboard? I imagine using it as a background process. I could be writing a new SQR program or a user’s manual while my colleagues in the Payroll Department are working hard to generate paychecks for me and my fellow employees. As I work, I can keep an eye on the time card and payroll processing. Are all the programs running properly? Are there any problems? (I should warn you that it will generate about one megabyte in the log file for every 75 minutes it runs, and it will get worse with some of my suggestions below.)

Our dashboard is a hands-free, background process monitor that never times out. Now, let’s make it more effective.

Sharpen The Filter

Our first dashboard sorted PSPRCSRQST (the process request table) by PRCSINSTANCE DESC (process instance number, descending), and selected the first fifty rows. The fifty most recent process requests are relevant, but they may not be the most relevant processes. There may be scheduled processes that were requested some time ago but have only started to run now. There may be scheduled processes that were just requested but will not run until some time in the future. Let’s select processes by the earliest date and time that they may start running (RUNDTTM) and set a range from one day in the past to approximately one hour in the future. (One day and one hour are arbitrary choices, so I don’t care that 0.04 day is not quite one hour.)

where RUNDTTM between SYSDATE - 1 and SYSDATE + 0.04

Highlight What’s Important

Most processes will be successful and not need our attention. We want to focus first on the processes that have problems and second on the processes that could have problems; they are still running and are not yet successfully finished. The RUNSTATUS (a character field) can take the following values:

1      Cancel
2      Delete
3      Error
4      Hold
5      Queued
6      Initiated
7      Processing
8      Cancelled
9      Success
10    No Success
16    Pending
17    Warning
18    Blocked
19    Restarted

We still prefer more recent processes to earlier ones, but we’d rather see an older problem than a newer non-problem. Here’s a way to sort them.

order by decode(RUNSTATUS,'3',1,'4',1,'10',1,'17',1,'18',1,'1',3,'2',3,'8',3,'9',3,2), RUNDTTM desc

The decode function sets error, hold, no success, warning, and blocked to priority 1. It sets cancel, delete, cancelled, and success to priority 3. It sets all other codes (queued, initiated, processing, pending, and restarted) to priority 2. You can change the decode parameters to fit your preferences. The minor sort is by RUNDTTM DESC, which favors the more recently started (or about to start) processes.

This sort is a selection criterion, because we will only read the first fifty rows in this order. It also prioritizes the processes by their display positions, because we start in the left upper corner and fill the window to the right lower corner.

Color And Sound

The show command has keywords beep, bold, blink, normal, reverse, and underline. The SQR Language Reference cautions that these work “on terminals that support those characteristics.” My modern color graphics PC card and monitor do beep, but they react to the other keywords only by changing the text color. However, some of the ANSI escape sequences that I used in my MS-DOS batch files twenty years ago work properly.

ANSI (American National Standards Institute – also the definers of ASCII) escape codes start with the “escape” character. The escape key is the first key on the left, on the first row of most American PC keyboards (to the left of the function keys), however pressing the key never puts an escape value (ASCII 27) into a file. We can add the escape value to our program in three ways:

  • let $esc = chr(27)
  • encode ‘<27>’ into $esc
  • let $esc = ‘←’ (Enter the left-pointing arrow on a PC by holding down either Alt-key while typing 2 and 7 on the numeric keypad, then releasing the Alt-key.)

After the escape character, the color codes have a left square-bracket ([), a color code (or two color codes separated by a semicolon), and a lower case “m”. The color codes are 30 – 37 for foreground (character) colors and 40 – 47 for background colors. The colors are black (0), red (1), green (2), yellow (3), blue (4), magenta (5), cyan (6), and white (7).

The code for black letters on a white background (reverse) is ‘←[30;47m’. The code to switch to green letters without affecting the background is ‘←[32m’. Let’s remember to end every color change with a code for white letters on a black background, which is ‘←[37;40m’.

My first impulse was to substitute color for as much text as possible, to increase the information density, and perhaps to pack more processes into the limited space of our window. There are 13 process types, but my organization uses much fewer. Perhaps I could color the process name to indicate Application Engine, COBOL, Crystal Report, PSJob, or SQR. There are 14 run statuses, but my organization uses fewer of those too. Perhaps I could color the time to indicate status.

My conclusion was that information shouldn’t be too dense and that color should be used sparingly. I chose white-on-red for problems, white-on-green for running processes, and white-on-black (the default) for successfully completed processes. I could have done this with an array, but my choices are clearer this way.

evaluate &RUNSTATUS
  when = '1' ! Cancel
  when = '2' ! Delete
  when = '3' ! Error
  when = '4' ! Hold
  when = '8' ! Cancelled
  when = '10' ! No Success
  when = '17' ! Warning
  when = '18' ! Blocked
    let $color_on = '←[37;41m' ! white on red

  when = '5' ! Queued
  when = '6' ! Initiated
  when = '7' ! Processing
  when = '16' ! Pending
  when = '19' ! Restarted
    let $color_on = '←[37;42m' ! white on green

  when = '9' ! Success
    if $diststatus = '*' or datediff(datenow(), &ENDDTTM, 'minute') < 1
      let $color_on = $color_off ! white on black
      let $color_on = '←[37;41m' ! white on red

The show command has a beep option. Alternately, we can show a string variable with ASCII 7. Sound can be useful; it alerts us even when we’re not looking, but it must be used with care. The PC beep is not a pleasant sound for us, the users of this application, and it could easily annoy the co-workers nearby.

We wouldn’t want a beep every ten seconds, when our program refreshes the window. We need to either beep only for events that are less than ten seconds old or keep a flag for each process instance number to indicate whether it has triggered a beep already. We probably don’t want to beep for every new process, or for every process that finishes successfully. We might want to beep once for problems only, or for a small group of critical process names.

We have up to sixty-four color choices; eight foregrounds and eight backgrounds, although many combinations are hard to read. Although our PCs may be capable of CD quality music reproduction, there’s only one tone available to us. However, we could experiment with double beeps; a beep followed by a short pause caused by a loop followed by another beep. We could express three states through sound; ignore, attention, and ATTENTION!

Your Turn

I invite you to experiment with conveying information through position, sound, and color as well as text; SQR multimedia. If the 25 line, 80 column canvas is too limited for your vision, write two variations of this program and run them simultaneously to double the bandwidth.

Looking Ahead

There’s much more information in the Peoplesoft Process Monitor (and beyond) that doesn’t fit in our real-time dashboard. Next week, we’ll create utilities that answer tough questions for us.