Automic Workload Automation

  • 1.  Oracle SQL statement for daily total object execution statistics

    Posted Jan 21, 2018 05:50 AM
    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. 



  • 2.  Oracle SQL statement for daily total object execution statistics
    Best Answer

    Posted Jan 21, 2018 09:53 AM
    Yoho,
    had the same request last week (but no cancelled workflows :-) )

    so I crated a call object as well with an SQLI VARA.
    in 2 seperate VARA objects you can define Object types and Status codes you want to be included in the mail.
    there are two modes availabl, automatic (takes the last month - 1st to last day) or interactive (unsing read- you can choose begin and endate)

    1f9q2yem50pn.jpghttps://us.v-cdn.net/5019921/uploads/editor/yo/1f9q2yem50pn.jpg" width="242">
    xdfemaydn1ry.jpghttps://us.v-cdn.net/5019921/uploads/editor/8d/xdfemaydn1ry.jpg" width="763">

    if you want the XMLs pls let me know.

    cheers, Wolfgang


  • 3.  Oracle SQL statement for daily total object execution statistics

    Posted Jan 21, 2018 10:42 AM
    FrankMuffke
    Thanks for the quick answer!
    Yes, i would like to receive the XMLs.
    The issue with statistics for full month is coming from our arch and reorg settings. 
    We are running the utilities daily and the settings are 0 days and last 32 records.
    Due that I can not pull relevant monthly statistics and I implement this as workaround.


  • 4.  Oracle SQL statement for daily total object execution statistics

    Posted Jan 21, 2018 01:09 PM
      |   view attached
    Here you go...

    If you need a list of all status codes in a VARA - here's a solution attempt
    https://community.automic.com/discussion/11490/print-status-description-with-the-help-of-status-code#latest

    parameters to change would be:

    VARA.STATISTICS_AMOUNT.2_CONFIG_JOBTYPES
    jobtypes that should be included

    VARA.STATISTICS_AMOUNT.2_CONFIG_STATUSCODES
    stati that should be included

    CALL object (variables & prompts tab)
                   

    &RECIPIENTS#uc4@local.com

    &SHOW_ZERO_VALUES#N

    recipients mail adresses - one or more, semikolon seperated

    have fun.

    cheers, Wolfgang



    Attachment(s)

    xml
    V112_statistics.xml   12 KB 1 version