Over the past week, I’ve had the opportunity to talk with several PeopleSoft customers who want to improve how they run and manage processing that occurs on the process scheduler.
This posting focuses on pulling together information from the run control (or report request) and the runtime statistics. The original question came from the following ITTOOLBOX posting.
Restating the problem
The person asking the question wanted to be able to be able to determine for each run of a specific nVision report, statistics, such as how long the report ran and other attributes of the processing. This will allow him to identify long-running reports and focus in on items to tune. Our High Volume Report Security, Distribution, and Management product has similar functionality in a page that allows an administrator to identify and manage all the reports to be run (or run) for a specific user.
The way to solve this problem is to find a way to join the run control (or report request) that contains all the parameters used to run the process with the process request table (which contains the status and processing information maintained by the process scheduler server.
The table in the process scheduler that contains the process status and other pertinent running statistics is the PSPRCSRQST table. For nVision, the table that stores the request information is the PS_NVS_REPORT. Therefore, we will want to join the PSPRCSRQST table to the PS_NVS_REPORT table to get what we need.
Unfortunately, this isn’t as straightforward as it might seem. There are no common fields between these two tables. In fact, the keys to the PS_NVS_REPORT table (Business_Unit and Report_Id) are actually parameters passed to the process and stored in a 3rd table in a string that concatenates all parameters passed to the process (this is PSPRCSPARMS). Here is an example of the parameters stored in this table for running an nVision report (the Report_ID is in red, and the Business_Unit is in blue):
How to do the join
Okay, so now we know the tables involved, and where the keys are stored. Let’s review where we are so far:
Okay, now let’s figure out how do do this 3-way join. Probably the easiest thing to do is to use the like verb (although a purer solution would be to use substring functions in the SQL instead). The Business_Unit is the -NBU parameter, and the Report_ID is the -NRN parameter, so our SQL will look for both of those parameters and see if that parameter concatenated with the appropriate key value in the PS_NVS_REPORT record is found.
Here’s the resulting SQL (which uses PeopleSoft meta-SQL to ensure platform independence… if you paste this into a view in application designer, it will work).
SELECT PRMS.PRCSINSTANCE, NVS.BUSINESS_UNIT, NVS.REPORT_ID, RQST.OPRID, RQST.RUNSTATUS, RQST.BEGINDTTM, RQST.ENDDTTM
FROM PS_NVS_REPORT NVS, PSPRCSPARMS PRMS, PSPRCSRQST RQST
WHERE PRMS.CMDLINE LIKE '%PSNVS.EXE' AND PRMS.PARMLIST LIKE '%NRN' %CONCAT NVS.REPORT_ID %CONCAT '%'
AND PRMS.PARMLIST LIKE '%NBU' %CONCAT NVS.BUSINESS_UNIT %CONCAT '%'
AND RQST.PRCSINSTANCE = PRMS.PRCSINSTANCE
What you can do with this…
Now that we have the SQL or view determined, we can put it to use. Again, what we’ve done is to identify which processes in the process scheduler table are associated with a given run control or report request (and all the pertinent values stored in each). You can now see all the parameters in the run control as well as the process scheduler stauts and runtimes for each time it was run.
Although this example is specific to nVision because it joins the nVision report request table, you can substitute the query run control record (PS_QUERY_RUN_CNTRL) for the PS_NVS_REPORT table (or other run control records) in its place to do the same thing for other process types.
Here are some things you can do with the results:
One final thing that came up at the UKOUG is to use this as the basis for better managing the running of reports in the scenario where a user kicks of the same report while a report with the same parameters is still processing on the server (this can happen when running to window and the report is slow… the user doesn’t have patience, or can happen when with other scenarios when a run control is re-used for multiple sets of parameters). I plan to write a follow-on posting to this.
Labels: nVision, Process_Scheduler