Abs Cal Creation

 

To Find Queries run by a User

 To Find Queries run by a User

EXECCOUNT or count(PRCSINSTANCE) can give how many times it was run recently.

LASTEXECDTTM or EXECDTTM or begindttm can give when it was last executed.

oprid or runoprid can give who ran it.

Using the PSQRYSTATS (Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).

SELECT B.QRYNAME, B.DESCR,  A.EXECCOUNT,   TO_CHAR(CAST((A.LASTEXECDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI'),

A.OPRID,B.QRYTYPE, A.QRYNAME, A.AVGEXECTIME, A.AVGFETCHTIME, A.AVGNUMROWS, A.NUMKILLS

  FROM (PSQRYSTATS A 

  LEFT OUTER JOIN  PSQRYDEFN B ON  A.OPRID = B.OPRID AND A.QRYNAME = B.QRYNAME )

  where a.oprid = 'userId' and lastexecdttm >= '01-Jan-2021'

  order by execcount desc,a.lastexecdttm desc

  ;

Using the PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher).

  

  select B.QRYNAME, B.DESCR,0,EXECDTTM, a.RUNOPRID, a.OPRID,a.* 

  from PSQRYEXECLOG A 

  LEFT OUTER JOIN  PSQRYDEFN B ON -- A.OPRID = B.OPRID AND 

  A.QRYNAME = B.QRYNAME 

  where a.runoprid = 'userId'  and execdttm >= '01-Jan-2021'

  order by  a.execdttm desc;

Using the PSPRCSRQST

  

select B.QRYNAME, B.DESCR,count(PRCSINSTANCE) ,max(   TO_CHAR(CAST((a.begindttm) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI')),a.OPRID , a.RUNCNTLID

from psprcsrqst A 

 LEFT OUTER JOIN  PSQRYDEFN B ON -- A.OPRID = B.OPRID AND 

  A.RUNCNTLID = B.QRYNAME 

where   A.prcsname = 'PSQUERY' AND A.oprid = 'userId'

  -- and  a.PRCSINSTANCE =     (select MAX( C1.PRCSINSTANCE) from PSPRCSRQST C1 where C1.RUNCNTLID = a.RUNCNTLID)

 group by B.QRYNAME, B.DESCR, a.RUNCNTLID,a.OPRID

 order by 3 desc,1;

PS_PRCSRUNCNTLEOPT is another record which stores data of the email ids,  etc.

One more.. to pull to whom the query sends info

SELECT A.PRCSINSTANCE, TO_CHAR(CAST((A.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.RUNCNTLID

-- ,(COUNT(E.OPRID) + COUNT(F.OPRID))

,B.DISTID, B.DISTIDTYPE,   D.ROLENAME, D.DYNAMIC_SW

,E.OPRID, E.EMAILID, E.ACCTLOCK, F.OPRID, F.EMAILID, F.ACCTLOCK

  FROM PSPRCSRQST A,    -- Main Record 

  (((PS_PRCSRQSTDIST B  -- Dist Record

      LEFT OUTER JOIN  PSROLEUSER D ON  D.ROLENAME = B.DISTID )   -- Role User

      LEFT OUTER JOIN  PSOPRDEFN E ON  E.OPRID = D.ROLEUSER AND E.ACCTLOCK = 0 )  -- Role User & Email

      LEFT OUTER JOIN  PSOPRDEFN F ON  F.OPRID = B.DISTID AND F.ACCTLOCK = 0 )  -- Direct user hard code

  WHERE ( A.PRCSINSTANCE = B.PRCSINSTANCE AND A.RUNCNTLID = '~RUNCNTLID ~'

     AND A.PRCSINSTANCE = (SELECT MAX( C.PRCSINSTANCE)

      FROM PSPRCSRQST C

  WHERE C.RUNCNTLID = A.RUNCNTLID));