Analyze Peoplesoft Process Instances With SQR

We built and enhanced a real-time dashboard that alerted us to problems with Peoplesoft process instances. The next challenge is to investigate processes that fail, or processes that succeeded yet dissatisfied our co-workers (the output was not as expected). Let’s build an SQR toolkit to help us do that.

Identify The Process Instance

The first step of the investigation is to determine which process instance has the problem. If we spot a problem in the Peoplesoft Process Monitor, we know the process instance number and the process name. If we spot it on our real-time dashboard, we know the last three digits of the process instance number and the process name. If our co-worker reports the problem we may know very little; “This is Mike Smith, the finance report is missing” is not specific enough … or is it? Our toolkit should take whatever we know about the process instance and help us determine which is it.

Assess The Situation

The second step of the investigation is to examine the situation, verify that there is a problem and define it. Our toolkit should report on the process request and the user’s run control parameters.

The process request table is called PSPRCSRQST. Note this is a PeopleTools table and doesn’t have an underscore after the “PS.” There are a variety of interesting fields.

  • The type of process (PRCSTYPE), which distinguishes Application Engine, Cobol, SQR, and other programs
  • The name of the process (PRCSNAME)
  • The operator who ran the program (OPRID)
  • The run control ID (RUNCNTLID)
  • The date and time that the process was requested (RQSTDTTM), that is was supposed to run (RUNDTTM), that it began to run (BEGINDTTM), and that it stopped running (ENDDTTM)
  • The current status of the process (RUNSTATUS) and the output files (DISTSTATUS)
  • The type (OUTDESTTYPE – email, file, printer, web, window) and format (OUTDESTFORMAT – csv, dat, doc, hp, htm, lp, pdf, ps, rpt, rtf, spf, txt, wks, xls, xml) of the output files

Once we have PRCSNAME from PSPRCSRQST, we can get the run control component from the process definition. The component (or components) are listed in the process definition panel group table, PS_PRCSDEFNPNL. (Pages were called panels and components were called panel groups before Peoplesoft moved into the browser with version 8.0.) The PSPNLGROUP table lists the pages that belong to a component. The PSPNLFIELD table lists the fields that appear on a page. This gives us the name of the run control table and fields that our process uses on that table. We can use a begin-select command with dynamic variables to pick up the right fields from the right table with the right OPRID and RUN_CNTL_ID key values.

We need to view the run control parameters with care. The user may have run the same process again, with the same run control ID, and changed the parameters. Also, the user may have run a different process that uses the same run control table, with the same run control ID, and changed the parameters. If anyone requests, I’ll show you how to detect those conditions, and how to deal with them, in a later blog entry.

View The Output Files

The process parameters table is called PSPRCSPARMS. The files generated by the process instance are stored in the directory named by the PRCSOUTPUTDIR field. We can view those files through the Peoplesoft Process Monitor. It’s also possible, but complicated, for our toolkit to access them.

If we run the toolkit from the Peoplesoft Process Scheduler, on the same server as the process we’re investigating, it’s simple to open and read the output files. However, it becomes harder to have the dialog I described above in the “Identify The Process Instance” section. We could require the full process instance number. Alternately, we could set up the run control page to receive feedback from our toolkit and program the toolkit to wait for a second round of input from the run control page.

If we run the toolkit from our PC client, it’s simple to have a back-and-forth dialog, but more difficult to read files on the file server. It’s not too hard if there’s only one file server and it runs Windows. If it runs UNIX, then our program needs to use FTP to copy the file to the PC before opening it.

If we’re running our toolkit on the file server, or the file server runs Windows, we could use the call system command to pipe a directory listing into a text file. Then we can open the text file and parse the names of the output files. Otherwise, we need to “calculate” the exact names of the log files and trace files that each process type creates.

Finally, we can open the output files and attempt to extract and display relevant information. This is probably too much work just to save us from drilling down in the Peoplesoft Process Monitor, but maybe I’ll tackle it in a future blog entry.

Rerun The Process

The last step of the investigation might be to run the process ourselves. We know the parameters our customer used and we may have modified them. We need to navigate through the Peoplesoft portal, prepare a run control row, and execute the process. Our toolkit should give us the path for the process.

We know the component name from PS_PRCSDEFNPNL. We can navigate up the portal menu tree to “Root” in the PSPRSMDEFN table. This normally required repeated queries, but Oracle has a special recursive option for select statements.

The Code

We include this Peoplesoft file to tell us which database management system we’re using. If you know you’ll always run on Oracle or never run on Oracle, just delete the appropriate code in the get_portal_path and/or get_portal_parent procedures.

#include 'setenv.sqc' ! Contains rdbms.sqc which specifies the database

Here is an array to hold the process request fields that I find interesting. Modify it as you like. Note that we format the date-time fields as “mm/dd hh:mi” and store them as text.

#define num_choices 10

!*******************************************************************************
begin-setup
  create-array name=process size={num_choices}
    field=instance:integer=0
    field=type:char=''
    field=name:char=''
    field=rundttm:char=''
    field=oprid:char=''
    field=run_status:char=''
    field=rqstdttm:char=''
    field=begindttm:char=''
    field=enddttm:char=''
    field=runcntlid:char=''
    field=output_type:char=''
    field=output_format:char=''
    field=dist_status:char=''
    field=emplid:char=''
    field=opr_descr:char=''
end-setup

If you want the toolkit to help you with multiple processes, add a while loop to repeat the get_process, get_run_control_defn, and get_portal_path procedures.

!*******************************************************************************
begin-program
  do start_program
  do get_process
  do get_run_control_defn
  do get_portal_path
end-program

The $divider makes it easier to read the “DOS” window or the log file. There are four fields in PSPRCSRQST containing codes that we need to look up in the translate table, PSXLATITEM. We could add four joins with four EFFDT subqueries to the select statement in the get_process procedure. We could have four load-lookup commands. Or, we could combine FIELDNAME and FIELDVALUE as the key and use the same lookup four times.

!*******************************************************************************
begin-procedure start_program
  let $divider = rpad('', 80, '-')

  let $where =     'FIELDNAME in (''RUNSTATUS'',''OUTDESTTYPE'',''OUTDESTFORMAT'',''DISTSTATUS'') '
            || 'and EFFDT      = (select max(EFFDT) '
            ||                     'from PSXLATITEM '
            ||                    'where FIELDNAME = X.FIELDNAME '
            ||                      'and FIELDVALUE = X.FIELDVALUE '
            ||                      'and EFFDT <= SYSDATE)'
  load-lookup name=xlat
    table='PSXLATITEM X'
    key='FIELDNAME || ''@'' || FIELDVALUE'
    return_value=XLATSHORTNAME
    rows=60
    where=$where
    quiet
end-procedure start_program

The program starts by asking us to tell it something – anything – about the processes. The selection looks for our text in the process instance number, the process name, the operator ID, and operator’s employee ID, and the operator’s description (which could be a name or title). The selection displays the ten most recent matches and asks us to pick the one we meant. This is a small example of the long-sought “do what I meant” command.

We store all the interesting information for all ten processes just to save us from selecting PSPRCSRQST again for an in-depth look.

!*******************************************************************************
begin-procedure get_process
  let #row_num = 0
  clear-array name=process

  input $info 'Identify process'
  show ''
begin-select loops={num_choices}
PR.PRCSINSTANCE
PR.PRCSTYPE
PR.PRCSNAME
PR.RUNDTTM
PR.OPRID
PR.RUNSTATUS
PR.RQSTDTTM
PR.BEGINDTTM
PR.ENDDTTM
PR.RUNCNTLID
PR.OUTDESTTYPE
PR.OUTDESTFORMAT
PR.DISTSTATUS
OD.EMPLID
OD.OPRDEFNDESC
  let $choice = substr(edit(#row_num,'9') || '. Inst='
             || edit(&PR.PRCSINSTANCE,'899999') || ' ' || rpad(&PR.PRCSNAME,13,' ')
             || edit(&PR.RUNDTTM,'mm/dd hh:mi') || ' Opr=' || &PR.OPRID || '/'
             || &OD.EMPLID || ' ' || &OD.OPRDEFNDESC, 1, 80)
  show $choice

  let $key = 'RUNSTATUS@' || &PR.RUNSTATUS
  lookup xlat $key $runstatus
  let $key = 'OUTDESTTYPE@' || &PR.OUTDESTTYPE
  lookup xlat $key $outdesttype
  let $key = 'OUTDESTFORMAT@' || &PR.OUTDESTFORMAT
  lookup xlat $key $outdestformat
  let $key = 'DISTSTATUS@' || &PR.DISTSTATUS
  lookup xlat $key $diststatus
  let $rundttm = edit(&PR.RUNDTTM, 'mm/dd hh:mi')
  let $rqstdttm = edit(&PR.RQSTDTTM, 'mm/dd hh:mi')
  let $begindttm = edit(&PR.BEGINDTTM, 'mm/dd hh:mi')
  let $enddttm = edit(&PR.ENDDTTM, 'mm/dd hh:mi')

  put &PR.PRCSINSTANCE &PR.PRCSTYPE &PR.PRCSNAME $rundttm &PR.OPRID
      $runstatus $rqstdttm $begindttm $enddttm &PR.RUNCNTLID
      $outdesttype $outdestformat $diststatus &OD.EMPLID &OD.OPRDEFNDESC
    into process(#row_num)
  add 1 to #row_num
 from PSPRCSRQST PR, PSOPRDEFN OD
where OD.OPRID = PR.OPRID
  and ( to_char(PR.PRCSINSTANCE) like '%' || $info
       or PR.PRCSNAME = $info
       or PR.OPRID = $info
       or OD.EMPLID = $info
       or OD.OPRDEFNDESC like '%' || $info || '%')
 order by PR.RUNDTTM DESC
end-select
  show ''
  input #row_num 'Select process (0 - 9)'
  show ''
  if range(#row_num, 0, {num_choices} - 1) = 0
    stop
  end-if

  show $divider
  show 'Process Request Data'
  show ''
  get #prcsinstance $prcstype $prcsname $rundttm $oprid
      $runstatus $rqstdttm $begindttm $enddttm $runcntlid
      $outdesttype $outdestformat $diststatus $emplid $oprdefndesc
    from process(#row_num)
  show 'OPRID = ' $oprid ' EMPLID = ' $emplid ' ' $oprdefndesc
  show 'PROCESS = ' #prcsinstance edit 999999 ' ' $prcstype ' ' $prcsname
  show 'REQUEST = ' $rqstdttm ' RUN = ' $rundttm ' BEGIN = ' $begindttm ' END = ' $enddttm
  show 'STATUS = ' $runstatus '/' $diststatus
  show 'RUN_CNTL_ID = ' $runcntlid ' OUTPUT = ' $outdesttype '/' $outdestformat
  show ''
end-procedure get_process

This is the code for determining which table and fields the process used for parameters. I described the algorithm and the caveat above. We select PS_PRCSDEFNPNL separately in order to save the &COMPONENT for the get_portal_path. Also, it’s possible to run the same process from several components. They would all have to use the same run control table and fields, because the process doesn’t “know” from where it was run. Therefore, we only want one of the components and we don’t want SQL to return the same RECNAME and FIELDNAME for each component.

There are records and fields on a run control page that are not the run control table. We eliminate them by selecting RECNAME not blank, FIELDNAME not blank, and only records whose first field is OPRID.

!*******************************************************************************
begin-procedure get_run_control_defn
begin-select
PNLGRPNAME &COMPONENT
 from PS_PRCSDEFNPNL
where PRCSNAME = $prcsname
end-select

show $divider
  show 'Run Control Data'
  show ''
begin-select
PF.RECNAME
PF.FIELDNAME
  let $recname = 'PS_' || &PF.RECNAME
  do get_run_control_data
 from PSPNLGROUP PG, PSPNLFIELD PF
where PG.PNLGRPNAME = &COMPONENT
  and PF.PNLNAME = PG.PNLNAME
  and PF.RECNAME <> ' '
  and PF.FIELDNAME <> ' '
  and exists         (select 1
                      from PSRECFIELD
                     where RECNAME = PF.RECNAME
                       and FIELDNAME = 'OPRID'
                       and FIELDNUM = 1)
order by PF.RECNAME, PF.FIELDNUM
end-select
  show ''
end-procedure get_run_control_defn

Once we have a record name and field name from the get_run_control_defn procedure, we can read its value by using dynamic variables to name the record and field and selecting the proper values of OPRID and RUN_CNTL_ID.

!*******************************************************************************
begin-procedure get_run_control_data
  let $dynamic = 'to_char(' || &PF.FIELDNAME || ')'
begin-select
[$dynamic] &VALUE=char
  show &PF.FIELDNAME ' = ' &VALUE
 from [$recname]
where OPRID = $oprid
  and RUN_CNTL_ID = $runcntlid
end-select
end-procedure get_run_control_data

We get the portal menu path by starting at our component, tracing it upward until we reach “Root,” and presenting it in the reverse order.

Oracle allows us to navigate a relational table of hierarchical data with a single SQL select, and it works in SQR too. I’ve used this wonderful feature to return a portal menu path and to trace the chain of command by the REPORTS_TO field of PS_EMPLOYEES. It may also work for the department security table – I haven’t tried yet. I spent a few hours trying to put it into a view so I wouldn’t have to remember the syntax. So far, no luck.

I haven’t found a similar feature in other DBMS, so I’ve also written the code to do it the “hard” way. Note that the #ifdef#else#end-if commands are compiler directions. If we have defined “DB2” rather than “ORACLE” in the setenv.sqc include file, the Oracle syntax won’t be compiled, or even tested.

!*******************************************************************************
begin-procedure get_portal_path
  show $divider
  show 'Portal Path'
  show ''
  move '' to $path
#ifdef ORACLE
begin-select
PORTAL_LABEL
  if &PORTAL_LABEL <> 'Root'
    let $path = $label || cond($path = '', '', ' > ' || $path)
  end-if
 from PSPRSMDEFN
start with PORTAL_URI_SEG2 = &COMPONENT
connect by prior PORTAL_PRNTOBJNAME = PORTAL_OBJNAME
and PORTAL_NAME = 'EMPLOYEE'
end-select
#else
begin-select
A.PORTAL_LABEL
A.PORTAL_PRNTOBJNAME
  move &A.PORTAL_LABEL to $label
  move &A.PORTAL_PRNTOBJNAME to $parent
  if $label <> 'Root'
    let $path = $label
  end-if
 from PSPRSMDEFN A
where A.PORTAL_URI_SEG2 = &COMPONENT
  and A.PORTAL_NAME = 'EMPLOYEE'
end-select
  while $label <> 'Root'
    do get_portal_parent
  end-while
#end-if

  show $path
  show ''
  show $divider
  input $x noprompt
end-procedure get_portal_path

!*******************************************************************************
begin-procedure get_portal_parent
begin-select
B.PORTAL_LABEL
B.PORTAL_PRNTOBJNAME
  move &B.PORTAL_LABEL to $label
  move &B.PORTAL_PRNTOBJNAME to $parent
  if $label <> 'Root'
    let $path = $label || ' > ' || $path
  end-if
 from PSPRSMDEFN B
where B.PORTAL_OBJNAME = $parent
  and B.PORTAL_NAME = 'EMPLOYEE'
end-select
end-procedure get_portal_parent

Comments are closed.