Message Log

Q) How to get log from different processes to compare them over time?

A) Join below tables, and you can get some info. Add additional conditions to reduce the scope of analysis/reporting.

a) PS_MESSAGE_LOG 

b) PS_MESSAGE_LOGPARM

d) PSMSGCATDEFN

c) psprcsrqst

select to_char(BEGINDTTM,'yyyy-mm-dd hh24.mi'),runcntlid, PRCSINSTANCE ,to_char(ENDDTTM,'dd hh24.mi'),--d.DESCRLONG,

d.MESSAGE_TEXT,b.MESSAGE_SEQ, b.PARM_SEQ, b.MESSAGE_PARM,

a.JOBID, a.PROGRAM_NAME ,to_char(a.DTTM_STAMP_SEC,'dd hh24.mi'),a.MESSAGE_SET_NBR, a.MESSAGE_NBR

from PS_MESSAGE_LOG a,PS_MESSAGE_LOGPARM b, psprcsrqst c, PSMSGCATDEFN d where -- a.PROCESS_INSTANCE = 28227262 and

a.process_instance = b.process_instance and a.MESSAGE_SEQ = b.MESSAGE_SEQ 

and a.process_instance = c.PRCSINSTANCE

and oprid = 'UAFSBCH' and  begindttm >=  '26-Dec-2021' and 

(runcntlid like 'DC%' or runcntlid in ('8511','HO','LPALL','USALP','AFSWZ') or c.ENDDTTM-c.BEGINDTTM >= '+00 01:30:00') and 

prcsname = 'TL_TIMEADMIN'  and a.MESSAGE_SET_NBR in (13507) and a.MESSAGE_NBR in (8,9,3015)

and a.MESSAGE_SET_NBR in d.MESSAGE_SET_NBR and a.MESSAGE_NBR = d.MESSAGE_NBR and a.MESSAGE_SEQ <= 30

order by b.PROCESS_INSTANCE desc, b.MESSAGE_SEQ, b.PARM_SEQ