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
ANY_OK + ANY_ABEND
(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.
SCRI.STATISTICS
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.
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
ANY_OK + ANY_ABEND
(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.
SCRI.STATISTICS
: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:SET &HND2#=PREP_PROCESS_VAR(VARA.SEC_SQLI.KPI)
: PROCESS &HND2#
: SET &Client# = GET_PROCESS_LINE(&HND2#,3)
: SET &Type# = GET_PROCESS_LINE(&HND2#,4)
: SET &Number# = GET_PROCESS_LINE(&HND2#,5)
: SET &Status# = GET_PROCESS_LINE(&HND2#,6
VARA.SEC_SQLI.KPIselect '>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.
What ... or how big are the differences to your Statistics search via GUI? What are the values you had until automating it?
For a potential crosscheck you could use the reporting tool:
https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/11.2/All%20Guides/help.htm#uczadt.htm%3FTocPath%3DAdministratorhandbuch%7CDienstprogramme%7CAE%2520DB%2520Reporting%2520Tool%7C_____0
hope this helps