How To Review Post To WIP Job Performance

Products

• Clarity PPM SaaS

Issue/Introduction

Is there a way to determine how long the Post To WIP job ran and how many transactions it processed?

Resolution

The following queries can be used

1. To query the parameters and runtime of each job instance

select j.id, j.name,
a.column_name, p.value as column_value,
to_char(r.start_date, 'yyyy-mm-dd hh24:mi:ss') job_start,
to_char(r.end_date, 'yyyy-mm-dd hh24:mi:ss')job_finish,
ROUND(R.TOTAL_EXECUTION_TIME/1000/60, 3) EXEC_MINS,
ROUND(R.TOTAL_EXECUTION_TIME/1000/60, 3)/60 EXEC_HOURS,
r.status_code, r.user_id, r.scheduler_id
from cmn_sch_jobs j
left join cmn_sch_job_runs r on j.id = r.job_id
left join cmn_sch_job_run_params p on r.id = p.job_run_id
left join cmn_attributes a on p.attribute_id = a.id
where 1=1
and r.start_date is not null
and j.job_definition_id in
(select id from cmn_sch_job_definitions where id in
(select id from cmn_sch_job_definitions where job_code = 'POST_TO_WIP' and is_active = 1)
)
AND a.column_name = 'param_post_options'
order by r.start_date desc

2. To query how many records were processed at each job run

SELECT count(1), LASTUPDATEDATE  
FROM PPA_WIP
GROUP BY LASTUPDATEDATE
order by LASTUPDATEDATE desc