Monthly statistics of object execution

Discussion created by Krum_Ganev on Dec 10, 2017
Latest reply on Dec 11, 2017 by Wolfgang_Brueckler_1288
Hello All,

For internal purposes and of course audit we need to extract job execution (success rate) on monthly basis.
When the system was small this was easily done via "Statistics > Period..." but now if I select whole month even with filters for JOBS and FT there is big change to cause service interruption. And of course why to do it manually when we can automate it ;)

So the way i build it - Script, Call Object and few SQLI variables.
Defined Objects (JOBS, JOBF, JOBP, EVNT)
Objects run statistics (for each object ANY_ABEND and ANY_OK) 
Agents by type

Everything was running smoothly until i decided today to check how many executions we have for December 3th.
As it is only 1 day i used the menu from the UI.
03/12/2017 - 00:00 to 03/12/2017 23:59
JOBS + FileTransfers
(as simple as that)
And I've got below number : 361336
Then i recheck the auto report from December 1st. Total number 357266
Pretty close to what i've got for just one day.

And now im speachless. I've recheck the script and the statement - everything looks correct. Should DB utilities somehow mess up with the statistics?
I will provide you my scripts and please cross-check them. Maybe im missing something or just SQL statements via script provide different output than the UI option.

:set &Time = sys_time_physical('HHMMSS','TZ_CET') :set &Date = sys_date_physical('TTMMJJJJ') :set &TStamp = '&Date-&Time' ! :set &LastMonth = sub_period('TTMMJJJJ:&Date',"MM:1",'TTMMJJJJ') ! :set &StartDate = first_of_period('TTMMJJJJ:&LastMonth',"MM","JJJJ-MM-TT") :set &EndDate = last_of_period('TTMMJJJJ:&LastMonth',"MM","JJJJ-MM-TT") ! :read &LocalStartDate#,"00","Bitte Start 'JJJJ-MM-TT' eingeben:", &StartDate :read &LocalEndDate#,"00","Bitte Ende 'JJJJ-MM-TT' eingeben:", &EndDate ! :set &RepI = diff_date('JJJJ-MM-TT:&LocalEndDate#', 'JJJJ-MM-TT:&LocalStartDate#') :set &RepI = add(&RepI,1) :set &RepI = format('&RepI') ! :set &FileStartDate = '&LocalStartDate#' :set &FileEndDate = '&LocalEndDate#' ! :set &LocalStartDate# = '&LocalStartDate# 00:00:00' :set &LocalEndDate# = '&LocalEndDate# 23:59:59' ! !:set &StartDate = conv_timestamp(&LocalStartDate,'TZ_CET','UTC') !:set &EndDate = conv_timestamp(&LocalEndDate,'TZ_CET','UTC') ! ! ! :pset &LocalStartDate# ! :pset &LocalEndDate# :PUT_READ_BUFFER = &LocalStartDate# :PUT_READ_BUFFER = &LocalEndDate# :set &HND# = ACTIVATE_UC_OBJECT(CALL.KPI,,,,,PASS_VALUES)
The call object is simply creating some pretty tables for the email

select '>ACTC', ah_client, ah_otype, count(*) as anzahl, 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','SCRI','JOBG','EVNT') and ah_stype <> 'AUTO' and ah_timestamp4 >= to_timestamp(?,'YYYY-MM-DD HH24:MI:SS') and ah_timestamp4 <= to_timestamp(?,'YYYY-MM-DD HH24:MI:SS') 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_client, ah_otype, status desc

I will appreciate your input and please DO correct me. Im only newbie here.
Also if there is already bulletproof script/statement to gather such statistics - i will be more than thankful to share it with me.

I will also attach my full 'solution' here.