AnsweredAssumed Answered

Oracle SQL statement for daily total object execution statistics

Question asked by Krum_Ganev on Jan 21, 2018
Latest reply on Jan 21, 2018 by Wolfgang_Brueckler_1288
Hello All,

For short period of time (1-2 months top) we need to gather daily executions statistics.
Currently I am using CALL object with VARA.SQLI

SQL Statement
select ah_client, ah_otype, count(*) as Total, case when ah_status between 1800 and 1899 then 'ANY_ABEND' when ah_status between 1900 and 1999 then 'ANY_OK' else 'Other:'||ah_status end status from GAEP.ah where ah_client = 100 and ah_otype in ('JOBS','JOBF','JOBP') and ah_timestamp4 >= to_timestamp(SYSDATE -1) and ah_timestamp4 <= to_timestamp(SYSDATE) group by ah_client,ah_otype,case when ah_status between 1800 and 1899 then 'ANY_ABEND' when ah_status between 1900 and 1999 then 'ANY_OK' else 'Other:'||ah_status end order by ah_otype

It works great BUT we have quite large number of flows which are blocking and we are manually canceling them. Due that we have good amount of job/flows with status 1850, 1851.
I would like to not count this exit codes in the total number.
Most probably it will be best to target only 1800 instead of full range?

Would you please help and advise. 

Outcomes