Check Process Override Options

After reading this article about how the PSAESRV process scheduler service was failing due to someone setting trace options on an application engine process but using override instead of append in the parameter list, I thought it might be a good idea to write a query that checks all process definitions where the parameter list, command line or working directory has been changed in the process definition override options.

The SQL is provided below. You can also download it here. You might find that the majority of the processes returned are Crystal Reports so you can amend the SQL to skip this process type if required.

select PRCSTYPE, PRCSNAME, DESCR, ( select XLATSHORTNAME from PSXLATITEM XI where FIELDNAME = 'PARMLISTTYPE' and FIELDVALUE = PD.PARMLISTTYPE and EFFDT = ( select max(EFFDT) from PSXLATITEM where FIELDNAME = XI.FIELDNAME and FIELDVALUE = XI.FIELDVALUE ) and EFF_STATUS = 'A' ) as PARMLISTTYPE, PARMLIST, ( select XLATSHORTNAME from PSXLATITEM XI where FIELDNAME = 'CMDLINETYPE' and FIELDVALUE = PD.CMDLINETYPE and EFFDT = (select max(EFFDT) from PSXLATITEM where FIELDNAME = XI.FIELDNAME and FIELDVALUE = XI.FIELDVALUE and EFFDT <= sysdate) and EFF_STATUS = 'A' ) as CMDLINETYPE, CMDLINE, ( select XLATSHORTNAME from PSXLATITEM XI where FIELDNAME = 'WORKINGDIRTYPE' and FIELDVALUE = PD.WORKINGDIRTYPE and EFFDT = ( select max(EFFDT) from PSXLATITEM where FIELDNAME = XI.FIELDNAME and FIELDVALUE = XI.FIELDVALUE and EFFDT <= sysdate) and EFF_STATUS= 'A' ) as WORKINGDIRTYPE, WORKINGDIRfrom PS_PRCSDEFN PDwhere (PARMLISTTYPE != '0' OR CMDLINETYPE != '0' OR WORKINGDIRTYPE != '0')order by PRCSTYPE, PRCSNAME;