Hi Lidia,
Duke has a few processes that run regularly that monitor the following:
Process Scheduler – Duplicate processes
Schedule – Weekly, Monday at 8A; emails results to our Operations team.
Purpose – Checks Process Scheduler Queue for processes that have more than one instance of the same run control and Recurrence.
Fix – Notify user to cancel the duplicate processes. It is possible that one instance is legit (ie – distribution going to multiple places like file and email). We suggest that the intentional dups either run temporarily, or schedule is varied for duplicates
SQL –
SELECT A.PRCSINSTANCE, A.PRCSTYPE, A.PRCSNAME, A.PRCSJOBNAME, A.RECURNAME, A.OPRID, A.RUNCNTLID, A.RUNSTATUS, TO_CHAR(CAST((A.RUNDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.JOBINSTANCE, A.RUNLOCATION, A.DBNAME, A.SERVERNAMERQST, A.SERVERNAMERUN
FROM PSPRCSRQST A
WHERE ( EXISTS (SELECT 'X'
FROM PSPRCSRQST B
WHERE B.PRCSINSTANCE <> A.PRCSINSTANCE
AND B.OPRID = A.OPRID
AND B.RUNCNTLID = A.RUNCNTLID
AND B.PRCSNAME = A.PRCSNAME
AND B.RUNSTATUS = A.RUNSTATUS
AND B.RECURNAME = A.RECURNAME
AND B.PRCSJOBNAME = A.PRCSJOBNAME
AND B.RUNDTTM = A.RUNDTTM)
AND A.RUNSTATUS = '5'
AND A.RECURNAME <> ' ')
ORDER BY 6, 3, 7, 5, 9
Process Scheduler – Queued processes under Locked User
Schedule – Weekly, Monday at 8A; emails results to our Operations team.
Purpose – Checks Process Scheduler Queue for processes that are Queued under a user with a Locked PSOPRDEFN record.
Fix – If the PSOPRDEFN lock is legit, contact user's team to see if process should be transferred to another user. SISSOPS team can help the department setup a new run control/recurrence under an active user and cancel the processes scheduled under the locked account.
SQL –
SELECT A.RUNCNTLID, A.OPRID, C.NAME, A.PRCSTYPE, A.PRCSNAME, A.RECURNAME, A.RUNSTATUS, A.DISTSTATUS, TO_CHAR(CAST((A.RUNDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.PRCSINSTANCE, A.JOBINSTANCE, A.OUTDESTTYPE, A.OUTDESTFORMAT, A.SERVERNAMERUN, A.PRCSJOBSEQ, A.PRCSJOBNAME, A.SERVERNAMERQST, A.SERVERASSIGN, B.ACCTLOCK, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM (PSPRCSQUE A LEFT OUTER JOIN PSOPRDEFN B ON B.OPRID = A.OPRID ), PS_SCC_NAMES_QVW C
WHERE ( A.RUNDTTM > SYSDATE - 7
AND A.RUNSTATUS IN ('5')
AND B.ACCTLOCK = 1
AND C.EMPLID = B.EMPLID
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_SCC_NAMES_QVW C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.NAME_TYPE = C_ED.NAME_TYPE
AND C_ED.EFFDT <= SYSDATE)
AND C.NAME_TYPE = 'PRF')
ORDER BY 2, 9 DESC, 1
Process Scheduler – Processes that do not end in Success for specific user(s) in the past few days
Schedule – Daily at 7A; emails results to our Operations team.
Purpose – Checks Process Scheduler processes and jobs that have status of "No Success", "Blocked", "Error", "Hold", "Processing" in the past 4 days. The prompt lets you enter a list of OPRID's that you want to pull.
Fix – Scan this report; ignore processes that have a statuses that are expected. Correct/re-run processes that ran unsuccessfully; Cancel/correct/re-run processes in "Processing" status that are stuck due to a Process Scheduler bounce.
SQL –
SELECT A.RUNCNTLID, A.OPRID, A.PRCSTYPE, A.PRCSNAME, A.RECURNAME, A.RUNSTATUS, A.DISTSTATUS, TO_CHAR(CAST((A.RUNDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.PRCSINSTANCE, A.JOBINSTANCE, A.OUTDESTTYPE, A.OUTDESTFORMAT, A.SERVERNAMERUN, A.PRCSJOBSEQ, A.JOBNAMESRC, A.SERVERNAMERQST, TO_CHAR(CAST((A.RQSTDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), TO_CHAR(CAST((A.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), TO_CHAR(CAST((A.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM PS_PMN_PRCSLIST A
WHERE ( A.RUNDTTM > SYSDATE - 10
AND instr(',' || :1 || ',', ',' || A.OPRID || ',') > '0'
AND A.RUNSTATUS IN ('10','18','3','4','7'))
ORDER BY 8 DESC, 1
Process Scheduler – List of Recurring Processes in last # days
Schedule – Currently runs ad-hoc as part of our audit.
Purpose – Lists Processes or Jobs that are running on a recurrence in the past # of days including output and email destination info.
Improvements needed – would like to incorporate Run control Parm info for processes that deal with incoming and outgoing files.
Fix – N/A. This query is used to determine what's scheduled to run on the Process scheduler in a given year (ignoring ad-hoc processes)
SQL –
SELECT DISTINCT A.OPRID, A.RUNCNTLID, A.PRCSTYPE, A.PRCSNAME, A.SCHEDULENAME, B.RECURNAME, A.RECURNAME, MIN( TO_CHAR(CAST((A.RUNDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')), Max( TO_CHAR(CAST((A.RUNDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')), A.OUTDESTTYPE, A.OUTDESTFORMAT, Count( A.PRCSINSTANCE), C.OUTDEST, LISTAGG (DISTINCT E.EMAILID,'; ') within group (order by D.DISTID), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM ((((PS_PMN_PRCSLIST A LEFT OUTER JOIN PSPRCSPARMS C ON A.PRCSINSTANCE = C.PRCSINSTANCE ) LEFT OUTER JOIN PS_SCHDLDEFN B ON B.SCHEDULENAME = A.SCHEDULENAME AND B.JOBNAMESRC = A.JOBNAMESRC ) LEFT OUTER JOIN PS_PRCSRQSTDIST D ON A.PRCSINSTANCE = D.PRCSINSTANCE ) LEFT OUTER JOIN PSOPRDEFN E ON D.DISTID = E.OPRID )
WHERE ( A.RUNDTTM > SYSDATE -:1
AND ( A.RECURNAME <> ' '
OR B.RECURNAME <> ' '))
GROUP BY A.OPRID, A.RUNCNTLID, A.PRCSTYPE, A.PRCSNAME, A.SCHEDULENAME, B.RECURNAME, A.RECURNAME, A.OUTDESTTYPE, A.OUTDESTFORMAT, C.OUTDEST
ORDER BY 1, 2
Natalie
| 
| Natalie Maines
Senior Business Systems Analyst, Data Analytics and Reporting Student Information Services and Systems (SISS) Duke University 1121 West Main St., Suite 2200 Durham, NC 27701 natalie.maines@duke.edu Tel +1.919.684.1263 |