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
Recent Comments