PS Query & SQL

 View Only

  • 1.  Process Monitor Evaluation - HELP!

    Posted 2 hours ago

    Hello Friends!

    Does anyone have a working SQL for the process monitor that evaluates what has been run over a duration of time to determine where overlaps exist, and how long processes are taking? I've been investigating record PNM_PRCSLIST but I am open to all ideas.

    My goal is to find where process and process types are running at the same time to make adjustments in the schedule and to outline all the processes that run scheduled or off cycle to create appropriate schedules for future schedules and to share with end users on when to schedule or not schedule processes.

    Key fields:

    Process Instance

    Process Type - App Engine, PSJob, SQL Cobol etc.

    Process Name

    Run Control ID

    Server Name

    Recurrence Name (I'd like to join the recurrence settings too since the name isn't always true)

    Run Date/Time (reduced to only the run date)

    Begin Date/Time (reduced to start time only)

    End Date/Time (reduced to end time only)

    Duration between being time and end time

    Whose running it (OPRID)

    Thanks!



    ------------------------------
    Lidia Anderson
    Manager, Campus Solutions
    Central Washington University
    lidia.anderson@cwu.edu
    ------------------------------
    Alliance 2026 Recordings are coming soon!


  • 2.  RE: Process Monitor Evaluation - HELP!

    Posted 36 minutes ago
    Hey Lidia!

    We have a query that we run daily to see the status of our nightly job processes.  Not sure it gets you everything you're looking for, but maybe it'll be a starting point for you.  The SQL is below followed by a screenshot of what it looks like in PS Query.

    SELECT B.PRCSINSTANCE, B.RUNCNTLID, A.DESCR, B.PRCSNAME, B.PRCSTYPE, B.RUNSTATUSDESCR, B.OPRID, TO_CHAR(CAST((B.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), TO_CHAR(CAST((B.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')
      FROM PS_PRCSDEFN A, PS_PMN_PRCSLIST B
      WHERE ( A.PRCSTYPE = B.PRCSTYPE
         AND A.PRCSNAME = B.PRCSNAME
         AND TO_CHAR( B.RUNDTTM, 'YYYY-MM-DD-HH24') >= TO_DATE(:1,'YYYY-MM-DD') || '-17'
         AND TO_CHAR( B.RUNDTTM, 'YYYY-MM-DD-HH24') <= TO_CHAR((TO_DATE(TO_DATE(:1,'YYYY-MM-DD'),'YYYY-MM-DD') + 1), 'YYYY-MM-DD') || '-10'
         AND ( B.RUNCNTLID LIKE 'FA%'
         OR B.PRCSNAME LIKE 'NC%FA%'
         OR B.PRCSNAME LIKE 'FAP%'))
      ORDER BY 8
    image.png
    -Jamie

    Jamie Pendergrass
    Associate Director
    North Carolina State University
    Office of Scholarships and Financial Aid
    2016 Harris Hall, Box 7302
    Raleigh, NC 27695-7302
    P: 919-515-NCSU (6278)
    F: 919-515-8422



    All electronic mail messages in connection with State business which are sent to or received by this account are subject to the NC Public Records Law and may be disclosed to third parties.



    Original Message
    Alliance 2026 Recordings are coming soon!


  • 3.  RE: Process Monitor Evaluation - HELP!

    Posted 36 minutes ago

    I don't have one that looks for overlapping jobs but this one looks for long running.  The prompt is # of days.  I defaulted mine to 1 day but can change it.  It gives you anything that was longer than 15 minutes.

     


    SELECT DISTINCT A.PRCSINSTANCE, A.PRCSTYPE, A.PRCSNAME, A.JOBNAMESRC, A.RUNCNTLID, 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'), SUBSTR(ENDDTTM - BEGINDTTM,12,8), A.RUNSTATUSDESCR
      FROM PS_PMN_PRCSLIST A
      WHERE ( A.ENDDTTM > SYSDATE - :1
         AND SUBSTR(ENDDTTM - BEGINDTTM,12,8) > '00:15:00'
         AND A.PRCSTYPE <> 'PSJob')
      ORDER BY 8 DESC

     

     

    Expression by Jeffrie Brooks:

     

     

     

    This one you put in a process name it and gives you everything that's in the process monitor. 

     


    SELECT DISTINCT A.PRCSINSTANCE, A.PRCSNAME, A.PRCSTYPE, A.OPRID, A.RUNCNTLID, 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'), SUBSTR(ENDDTTM - BEGINDTTM,12,8), A.RUNSTATUSDESCR, MAX( C.MESSAGE_PARM)
      FROM ((PS_PMN_PRCSLIST A LEFT OUTER JOIN  PS_MESSAGE_LOG B ON  B.PROCESS_INSTANCE = A.PRCSINSTANCE AND B.PROGRAM_NAME IN ('ROWPROC','psaemain') ) LEFT OUTER JOIN  PS_MESSAGE_LOGPARM C ON  B.PROCESS_INSTANCE = C.PROCESS_INSTANCE AND B.MESSAGE_SEQ = C.MESSAGE_SEQ )
      WHERE ( A.PRCSNAME = :1)
      GROUP BY  A.PRCSINSTANCE,  A.PRCSNAME,  A.PRCSTYPE,  A.OPRID,  A.RUNCNTLID,  A.BEGINDTTM,  A.ENDDTTM,  SUBSTR(ENDDTTM - BEGINDTTM,12,8),  A.RUNSTATUSDESCR
      ORDER BY 1

     

     

    This one puts in a beginning process instance number and an ending process instance number and dumps out everything in between (I use this one to get my mack daddy job run times, it's a job of jobs):

     


    SELECT DISTINCT A.PRCSINSTANCE, A.PRCSJOBSEQ, A.PRCSTYPE, A.PRCSNAME, A.JOBNAMESRC, A.RUNCNTLID, 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'), SUBSTR(ENDDTTM - BEGINDTTM,12,8), A.RUNSTATUSDESCR, MAX( C.MESSAGE_PARM)
      FROM ((PS_PMN_PRCSLIST A LEFT OUTER JOIN  PS_MESSAGE_LOG B ON  B.PROCESS_INSTANCE = A.PRCSINSTANCE AND B.PROGRAM_NAME = 'ROWPROC' ) LEFT OUTER JOIN  PS_MESSAGE_LOGPARM C ON  B.PROCESS_INSTANCE = C.PROCESS_INSTANCE AND B.MESSAGE_SEQ = C.MESSAGE_SEQ )
      WHERE ( A.PRCSINSTANCE BETWEEN :1 AND :2)
      GROUP BY  A.PRCSINSTANCE,  A.PRCSJOBSEQ,  A.PRCSTYPE,  A.PRCSNAME,  A.JOBNAMESRC,  A.RUNCNTLID,  A.BEGINDTTM,  A.ENDDTTM,  SUBSTR(ENDDTTM - BEGINDTTM,12,8),  A.RUNSTATUSDESCR
      ORDER BY 1

     

     

     

    Thanks!

    Dana Pawlowicz

    Business Systems Analyst Sr - ERP

    Business Enterprise Systems and Technologies
    Digital Technology Solutions

    University of Cincinnati

    51 Goodman Dr.

    Cincinnati, Oh 45221

     

     




    Original Message
    Alliance 2026 Recordings are coming soon!


Related Content