There are many approaches to monitor the PeopleSoft process scheduler. One of the options that come to mind is to have a script running on the OS hosting the process scheduler to monitor the OS process. But having process schedulers running on multiple OS (PSUNX and PSNT) will need development of scripts running on both servers.

My preferred method is to use the below SQL to monitor all the process schedulers running on the database.

SELECT A.SERVERNAME, B.XLATSHORTNAME,B.FIELDNAME,B.FIELDVALUE,TO_CHAR(B.EFFDT,’YYYY-MM-DD’)

FROM PSSERVERSTAT A, PSXLATITEM B

WHERE B.EFFDT =

(SELECT MAX(B_ED.EFFDT) FROM PSXLATITEM B_ED

WHERE B.FIELDNAME = B_ED.FIELDNAME

AND B.FIELDVALUE = B_ED.FIELDVALUE

AND B_ED.EFFDT <= SYSDATE)

AND B.FIELDNAME = ‘SERVERSTATUS’

AND B.FIELDVALUE = A.SERVERSTATUS

Psnt_status

Depending on the results of the above SQL you can trigger an email notification or any other form of action.

Posted by Nitin Pai
Comments (14)
August 2nd, 2007

Comments (14)

Nitin - November 14th, 2007

Hi Prashant - This SQL was written for Oracle DB. You can use similar logic to build syntax appropriate for DB2. Thanks Nitin

Prashant - November 13th, 2007

The query provided does not run on DB2.

Nitin - November 6th, 2007

The new post is now available. Please check out http://blogs.hexaware.com/peoplesoft/2007/11/how-to-determin.html Thanks Nitin

Ed - November 5th, 2007

Sounds great. Look forward to seeing it soon.

Nitin - November 5th, 2007

Ed - I think a response to your question will be better served with examples using screen shots. Look out for my new post which will help you quickly identify what is currently being executed. Thanks Nitin

Ed - November 2nd, 2007

Thanks for explanation! on #3 point above, the process could be anything, like batch job. Sometime short one but running longer than before. So client will ask what it is doing, and most important is to ask how long it's going to be finished. They want a quick answer from me that they should kill it if it has long time to run, or keep it running if it's close to the end of run. In this circumstance, I can't use trace to figure, time is to tight. That's why I am asking how you deal with it via any fast steps to determine and give client the answer. Someone said I need to be familiar with the funtional design which means I need to know every steps the process is going. But from my perspective, I just need to check if this process is working on in database, or app server, or web server. Which section and anyway can tell me how much the process has done and how long it will end. Thanks again.

Nitin - November 1st, 2007

1. I saw it's running via querying PSPRCSRQST table, said name is INH07 something, how do I know how many and which SQL queries belong to this process? There are multiple ways to determine the SQL being executed by the process. - Trace the process using the PeopleSoft process definition or process scheduler trace flags. - Determine the SID from v$session and trace the program or simply query the v$sqltext view joining with v$session. 2. If query is not being seen in Database side, where I can find its status running on App server or Web server, or any other locations? I am assuming by

Ed Johnson - November 1st, 2007

Hi Nitin, In terms of PS process monitoring, I have some questions about it. 1. I saw it's running via querying PSPRCSRQST table, said name is INH07 something, how do I know how many and which SQL queries belong to this process? 2. If query is not being seen in Database side, where I can find its status running on App server or Web server, or any other locations? 3. One method I am using to find queries for this process is comparing the start time of the queries and INH07 process, this could be correct, but sometime consumed a lot of time. Do you have a quick way (steps) to find process, queries belong to the process, what is this process doing and where (if it is not running in database)? Thank you in advance! Ed

Nitin Pai - October 5th, 2007

There are a few methods that you can use to determine if App Server is running. 1. You can check in your database and monitor V$SESSION where PROGRAM LIKE 'PSAPP%' 2. You can use psadmin command line option in your script and grep for status on PSAPPSRV. psadmin -c sstatus -d FDMO The above command will provide you with server status. Note - The above methods ONLY verify the existence of application server processes. This does not validate the connectivity. Thanks Nitin

Ahsan Imam - October 4th, 2007

Is there a way to see if the application server is up and running properly? We have 9 app servers and sometimes I have to log in to each one of them to see if they are running. Any suggestions. Thanks Ahsan

Nitin Pai - September 12th, 2007

Hi Alok Unfortunately Data Mover does not support using IMPORT ... AS ... when you have a trigger defined on the table you are trying to import from the DAT file. However, I have a method that you can use to trick the data mover and get this accomplished. Follow these steps. 1. Open your DAT file using an editor such as TEXTPAD. 2. Search for the record name in the DAT file. 3. You will find something as shown below. EXPORT HX_CMP_TBL.PS_HX_CMP_TBL 4. Replace the above text with your new table name. EXPORT HX_CMP_TBL_TEMP.PS_HX_CMP_TBL_TEMP 5. Create this table in your database ensuring that it has the same definition as the original table without any rows. CREATE TABLE PS_HX_CMP_TBL_TEMP AS SELECT * FROM PS_HX_CMP_TBL WHERE 1=2; 6. Log on to data mover and import the table. IMPORT PS_HX_CMP_TBL_TEMP; Thanks Nitin

Alok - September 12th, 2007

Hi Nitin, I am trying to import a table from a .dat file to a database with a different name e.g. Import PS_A AS PS_B; But I am getting an error Error: Syntax error: IMPORT A AS PS_B Invalid command for object with trigger definition. Is there a way by which we can import a table having trigger associated to it. Thanks, Alok

Nitin Pai - September 6th, 2007

I am assuming that you are on tools version greater than 8.44.x. 1. Ensure that your URL in "PeopleTools > Process Scheduler > Report Nodes" is correct for the distribution node used by your process scheduler. For example, http://servername.com:nnnn/psreports/FDMO The FDMO is case sensitive. 2. Ensure that the Connection Information is correct. URI Host should be server name, URI Port should be the webserver port number as provided in the URL, URI Resource has to be SchedulerTransfer/FDMO (note - this is case sensitive). These two settings should most probably ensure that you can view the reports through the PIA hyperlink. Let me know if you still have problems. Thanks Nitin

ws - September 6th, 2007

Hi, I am new to PSFT. I am done with the installation but stuck in Process Scheduler. It wants me to point to http://servername:port/psreports/ps but when i linked to that url, it is showing me invalid url. I can submit the job and i can see the job in c:\psreports folder but i can't see it via hyperlink. do you have any doc or description in your url that i can pick up? thanks.