Querying the PeopleSoft Message Log with SQL

It is easy to access the PeopleSoft message log in Process Monitor component, but it can be a little difficult to work with in SQL because it is stored in multiple tables.

This started when I wanted to generate a PeopleSoft log message as a single string of text, so I could investigate shared pool memory errors by searching for ORA-04031 errors.  Ultimately, the string 'ORA-04031' is stored in PS_MESSAGE_LOGPARM, but I wanted to see the whole error message.

• Each process, has a request record on PSPRCSRQST, it can have many messages.
• Each message is stored in the message log table PS_MESSAGE_LOG
• The text of each message is stored in the message catalogue table PSMSGCATDEFN.  It can have up to 9 substitution strings (%1, %2, etc).
• These correspond to up to 9 parameters stored on PS_MESSAGE_LOGPARM that are substituted into the message string.

I assemble the message text in a PL/SQL function exactly as PeopleTools programs do, substituting the variables in the message string from the message catalogue with the parameter values.  The PL/SQL function is put into the SQL query as a common table expression so that I don't have to create a function or package in the database.  The function returns the full message text in a CLOB, thus I can then easily manipulate the message string in SQL.

In this case, I wrote a SQL query to search for ORA-04031 (see psmsglogora4031.sql on Github), but the same PL/SQL function can be used in various queries.  

It can be slow to search the generated message string.  It can be faster to search PS_MESSAGE_LOGPARM directly. 

WITH FUNCTION psmsgtext(p_process_instance INTEGER, p_message_seq INTEGER) RETURN CLOB IS
  l_message_log ps_message_log%ROWTYPE;
  l_message_text CLOB;
BEGIN
  SELECT *
  INTO   l_message_log
  FROM   ps_message_log 
  WHERE  process_instance = p_process_instance
  AND    message_seq = p_message_seq;

  SELECT message_text
  INTO   l_message_text
  FROM   psmsgcatdefn
  WHERE  message_set_nbr = l_message_log.message_set_nbr
  AND    message_nbr     = l_message_log.message_nbr;

  --dbms_output.put_line(l_message_text);
  FOR i IN (
    SELECT *
    FROM   ps_message_logparm
    WHERE  process_instance = p_process_instance
    AND    message_seq = p_message_seq
    ORDER BY parm_seq
  ) LOOP
    --dbms_output.put_line(i.message_parm);
    l_message_text := REPLACE(l_message_text,'%'||i.parm_seq,i.message_parm);
  END LOOP;

  --and tidy up the unused replacements at the end
  RETURN REGEXP_REPLACE(l_message_text,'%[1-9]','');
END;
x as (
select r.prcstype, r.prcsname, r.oprid, r.runcntlid
, l.*, psmsgtext(l.process_instance, l.message_seq) message_text
from ps_message_log l
LEFT OUTER JOIN psprcsrqst r ON r.prcsinstance = l.process_instance
WHERE …
)
select *
from x
ORDER BY dttm_stamp_sec
/

Now, I can easily produce a report of messages, like this:

Process              Process         Operator                             Process  Msg                                   Msg         Msg
Type                 Name            ID         Run Control              Instance  Seq JOBID           PROGRAM_NAME     Set#  Msg#   Sev DTTM_STAMP_SEC
-------------------- --------------- ---------- ---------------------- ---------- ---- --------------- --------------- ----- ----- ----- ----------------------------
MESSAGE_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
nVision-ReportBook   RPTBOOK         VP1        NVS_XXXXXXX_99            1234567    1 PRCS SCHDL      psprschd           65    70     0 01/04/2023 20.37.21
Process Request shows status of 'INITIATED' or 'PROCESSING' but no longer running

nVision-ReportBook   RPTBOOK         VP1        NVS_XXXXXXX_99            1234567    2 PRCS SCHDL      psprschd           65    73     0 01/04/2023 20.37.23
PSNT1 failed to post files to the report repository.  Server scheduled to try again on 2023-04-01-20.37.41.239539.  See log
...

Purging PeopleTools Physical Cache Files after Database Restore or Database Flashback

I have written previously about how to clear the physical cache files on a PeopleTools process, but I have found myself explaining it a few times recently, so I am going to post about it again.

When you refresh the database of a PeopleSoft system, you need to clear the physical cache files of the PeopleTools processes.  The files are outside the database and they no longer reflect what is inside the database.  This includes all application servers and process schedulers and anywhere where Application Designer or a client process is used.

It is common to refresh a database when testing a PeopleSoft system.  For example, to copy production to a performance test environment.  It is also increasingly common to use Oracle database flashback during testing.  A guaranteed restore point is taken, a test is performed, and then the database is flashed back to that restore point.  Flashback returns the whole database not just to the same logical state, but also the same physical state.  Block for block, the entire database is physically the same as when the restore point was taken.  Thus a test is completely repeatable with the same initial conditions.  Although the database instance will have been restarted during the flashback so the content of the database memory will have been cleared.

It is also common, after the flashback to then make small changes or corrections, take a new restore point and repeat the test.  Some of those changes might include Application Designer projects that will then be loaded into the physical cache.  Flashing the database back won't change the physical cache files stored outside the database, so they need to be cleared too.  Otherwise, they may have higher version numbers than the objects in the database, and caching won't work correctly.  When you retest, your changes may not be loaded and executed by PeopleTools processes.

The officially approved method is to go around each server and use either the purge option in the psadmin utility or manually delete the files.  See:

• Oracle Support Note: E-WS: How to Delete / Purge Cache for the PeopleSoft Application (Doc ID 753215.1)
• PeopleBooks: System and Server Admin ► Using the Application Server Administration Menu 

However, since at least PeopleTools 5, it has been possible to invalidate all physical cache files on all servers by updating the LASTREFRESHDTTM on the single row in table PSSTATUS.  Any cached object older than the value of LASTREFRESHDTTM will be purged from the cache when the process that reference that cache is started. Therefore, if immediately after a restore or flashback that value is updated to the current system time, all caches will be purged as the processes are restarted.

UPDATE PSSTATUS
SET    LASTREFRESHDTTM = SYSDATE
/
COMMIT
/

Programmatically Suspending and Restarting the Process Scheduler

I found this question on a message forum, wrote a note, and forgot about it:

Anyone have any tricks on suspending the process schedulers programmatically?  I just tried using the following but the weird thing was I saw at least one of my process schedulers unsuspend itself, so I must be missing an update to a table.

UPDATE PSSERVERSTAT set SERVERSTATUS = '2' where SERVERNAME like '%PSUNX%'

The process scheduler writes its status to SERVERSTATUS so that it can be seen in the Process Monitor.  Instructions to the process scheduler are read from SERVERACTION, so this is the column that must be updated.  Both columns have a set of XLAT values that translate the status.

PeopleSoft Field Name	Description
SERVERSTATUS	Server Status 0=Error 1=Down 2=Suspended 3=Running 4=Purging 5=Running With No Report Node 6=Suspended - Disk Low 7=Suspended - Offline 8=Running - Report Rep. Full 9=Overloaded
SERVERACTION	Process Server Action 0=None 1=Stop 2=Suspended 3=Restart 4=Purge

See PSSERVERSTAT.

You can see how PeopleSoft does this by tracing the Process Monitor component as it issues commands to the process scheduler.  Hence you can issue commands as follows:

• Stop

update psserverstat
set    serveraction = 1 /*Stop*/
where  serverstatus = 3 /*Running*/
and    servername = …
/
commit
/

• Suspend

update psserverstat
set    serveraction = 2 /*Suspend*/
where  serverstatus = 3 /*Running*/
and    servername = …
/
commit
/

• Restart (after suspension)

update psserverstat
set    serveraction = 3 /*Restart*/
where  serverstatus = 2 /*Suspended*/
and    servername = …
/
commit
/

• Startup (if the Tuxedo domain is running)

update psserverstat
set    serveraction = 3
where  servername = …
/
commit
/

Oracle SQL Tracing Processes from Startup

Sometimes, ASH and AWR are not enough.  SQL may not be sampled by ASH if it is short-lived, and even if it is sampled, the SQL may not be captured by AWR.  Sometimes, in order to investigate a problem effectively, it is necessary to use database session SQL trace.  

It is easy to trace a process initiated by the process scheduler with a trigger (see Enabling Oracle Database Trace on PeopleSoft processes with a Trigger).

Another tactic is to use an AFTER LOGON trigger with logic to look at the program name.  The program name can be read using SYS_CONTEXT().  If it matches what I am looking for, I can enable session trace.

Here is an example I used for the OpenXML nVision server PSNVSSRV

• I want to trace SQL and not any wait events or bind variables.  Therefore, I will set event 10046 at level 1.
• I also set a tracefile_identifier that will be included in the trace file name, so I can more easily identify the trace file.

REM additional SQL trace triggers
CREATE OR REPLACE TRIGGER sysadm.gfc_nvision_trace_on_logon
AFTER LOGON
ON sysadm.schema
DECLARE
  l_process_instance INTEGER;
  l_program          VARCHAR2(64 CHAR);
  l_sql              VARCHAR2(100);
BEGIN

  SELECT sys_context('USERENV', 'CLIENT_PROGRAM_NAME')
  INTO   l_program
  FROM   dual;

  IF l_program like 'PSNVSSRV%' THEN --then this is a NVISION session
    EXECUTE IMMEDIATE 'ALTER SESSION SET tracefile_identifier = ''PSNVSSRV''';
    EXECUTE IMMEDIATE 'ALTER SESSION SET events ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 1''';
  END IF;

EXCEPTION 
  WHEN OTHERS THEN NULL;
END;
/
show errors
ALTER TRIGGER sysadm.gfc_nvision_trace_on_logon ENABLE;

See also Reading Trace files with SQL