Automic Workload Automation

Expand all | Collapse all

SQL Queries

Jump to Best Answer
  • 1.  SQL Queries

    Posted 10-11-2018 04:27 PM

    Dear Experts,

     

    Can somebody help me with SQL Queries to find out the following ?

     

    1.  Query to find out the list of jobs executed per day with the status of the Job

    2. Find the Automic table that holds SAP Job and Variant along with the MRT settings for the job set.

     

    Thanks and Regards

    Vimalan



  • 2.  Re: SQL Queries

    Posted 10-11-2018 05:49 PM

    Hey Vimalan,

     

    1. I have this one set up to be automated where the from date and todate are defined in Automic. So you'll need to change that along with client if you run this outside of Automic.

     

    SELECT AH_Name, AH_Alias, AH_Status
    FROM AH
    WHERE AH_Timestamp4 between to_date('&FROMDATE1# 00:00:00','yyyy-mm-dd HH24:MI:SS')
    and to_date('&TODATE1# 23:59:59', 'yyyy-mm-dd HH24:MI:SS')
    and Ah_Client = '&$CLIENT#'
    and AH_Otype not in ('REPORT', 'CLNT', 'USER','QUEUE')
    order by AH_Name asc

     

    2. I dont know how to pull from the forms tab but I think it is just a java process that populates the OT table in the database. If anyone knows if we can query the forms tab directly that would be nice.

     

    SELECT OT_Content, OH_Name
    FROM OH,OT
    WHERE OH_HostAttrTypeDst = 'R3'
    and OT_OH_IDNR = OH_IDNR
    and OH_Client = '&$CLIENT#'
    and OT_Content LIKE 'R3_%'



  • 3.  Re: SQL Queries
    Best Answer

    Posted 10-12-2018 07:43 AM

    Be aware that TIMESTAMP4 from AH is deactivation, which may happen much later if your jobs, like some of ours, get deactivated by some person, by hand, in the activity window  at the end of the month

     

    Pick the one that fits best:

     

    AH_TIMESTAMP1: Activation

    AH_TIMESTAMP2: Start

    AH_TIMESTAMP3: End

    AH_TIMESTAMP4: Deactivation



  • 4.  Re: SQL Queries

    Posted 10-15-2018 12:36 PM

    I believe that AH_TIMESTAMP4 is the time including Post processing. With deactivate set to never I am still getting a populated AH_timestamp4 in the DB. Additionally, If I add a wait to the post process I see the time difference between AH_TIMESTAMP3 and AH_TIMESTAMP4



  • 5.  Re: SQL Queries

    Posted 10-15-2018 01:23 PM

    Hi

     

    from docs...

     

    AH_TimeStamp1datetimeDATEtimestamptimestamptrueactivation time
    AH_TimeStamp2datetimeDATEtimestamptimestamptruestart time
    AH_TimeStamp3datetimeDATEtimestamptimestamptrueend time (before post processing if available)
    AH_TimeStamp4datetimeDATEtimestamptimestamptrueend time (including post processing time if available)

     

    https://docs.automic.com/documentation/webhelp/english/AWA/11.2/AE/latest/DB%20Schema/db/_structure/HTML/AH.html 

     

    cheers, Wolfgang



  • 6.  Re: SQL Queries

    Posted 11-06-2018 07:35 PM

    Hi MariaJosephVimalan605617 

    Was the suggestion provided by Michael, Carsten & Wolfgang helpful in answering your question/help in finding the data you needed? If their info help resolved your question, please help us by clicking on the "Mark Correct" button on the respective comment or let us know if you still need help.

    Regards,
    Luu



  • 7.  Re: SQL Queries

    Posted 11-11-2018 09:03 AM

    Reporting on Jobs =>  see ucyrepg.jar in the utilities to generate a report with your criteria. Can be used in a job scheduled every day to give you info on the las 24h job runs.

     

    Tables =>  see ../db/_structure/HTML (Unix/Linux)  or ..\db\_structure\HTML (Windows) for all tables description. Then take some time to go through the chaining of some tables starting by OH to find the info you are looking for. Need some practice but at end, you should be able to find everything you need. Just remeber that OH_Idnr is the key to access an object, AH_Idnr is the key to access the detail information of a run of an object and AH_OH_Idnr is making the link between run and object definition ....



  • 8.  Re: SQL Queries

    Posted 02-15-2019 04:05 AM

    ***********************************For BW jobs

    SELECT 

    OH_Client, OH_Name, JBA_IntAccount as 'Change Reuqest',
    substring(Substring(OT_Content,0,charindex(',',OT_Content)),19,len(Substring(OT_Content,0,charindex(',',OT_Content)))) as 'Report\Variant',
    Substring(OT_Content,charindex(',',OT_Content)+1,LEN(OT_Content)-charindex(',',OT_Content)) as 'Report\Variant',
    LEFT(Substring(JBA_Rest,charindex('9',JBA_Rest)+3,charindex('=',Substring(JBA_Rest,charindex('9',JBA_Rest)+3,100))),LEN(Substring(JBA_Rest,charindex('9',JBA_Rest)+3,charindex('=',Substring(JBA_Rest,charindex('9',JBA_Rest)+3,100))))-3) as 'SM37 Job name',
    CASE OH_MrtMethod
    WHEN 0 THEN 'no audit'
    WHEN 1 THEN 'fix in seconds'
    WHEN 2 THEN 'fix time'
    WHEN 4 THEN 'ERT+xx%'
    WHEN 256 THEN 'Cancel'
    WHEN 512 THEN 'Start Object'
    WHEN 65536 THEN 'SRT in seconds'
    WHEN 131072 THEN 'SRT ERT+xx%'
    ELSE CAST(OH_MrtMethod as varchar(MAX))
    END as 'MRT_Method', OH_Mrt, OH_MrtTime, OH_MrtErt, OH_MrtExecute
    FROM OH
    JOIN OT ON OH_IDNR = OT_OH_Idnr
    JOIN JBA ON JBA_OH_Idnr = OH_Idnr
    WHERE
    OH_DELETEFLAG = 0
    AND OT_Content LIKE '%R3_ACTIVATE_REPORT%';



  • 9.  Re: SQL Queries

    Posted 02-15-2019 04:06 AM

    **************************For ABAP jobs
    SELECT
    OH_Client, OH_Name, JBA_IntAccount as 'Change Reuqest',
    substring(Substring(OT_Content,0,charindex(',',OT_Content)),19,len(Substring(OT_Content,0,charindex(',',OT_Content)))) as 'Report\Variant',
    Substring(OT_Content,charindex(',',OT_Content)+1,LEN(OT_Content)-charindex(',',OT_Content)) as 'Report\Variant',
    LEFT(Substring(JBA_Rest,charindex('9',JBA_Rest)+3,charindex('=',Substring(JBA_Rest,charindex('9',JBA_Rest)+3,100))),LEN(Substring(JBA_Rest,charindex('9',JBA_Rest)+3,charindex('=',Substring(JBA_Rest,charindex('9',JBA_Rest)+3,100))))-3) as 'SM37 Job name',
    CASE OH_MrtMethod
    WHEN 0 THEN 'no audit'
    WHEN 1 THEN 'fix in seconds'
    WHEN 2 THEN 'fix time'
    WHEN 4 THEN 'ERT+xx%'
    WHEN 256 THEN 'Cancel'
    WHEN 512 THEN 'Start Object'
    WHEN 65536 THEN 'SRT in seconds'
    WHEN 131072 THEN 'SRT ERT+xx%'
    ELSE CAST(OH_MrtMethod as varchar(MAX))
    END as 'MRT_Method', OH_Mrt, OH_MrtTime, OH_MrtErt, OH_MrtExecute
    FROM OH
    JOIN OT ON OH_IDNR = OT_OH_Idnr
    JOIN JBA ON JBA_OH_Idnr = OH_Idnr
    WHERE
    OH_DELETEFLAG = 0
    AND OT_Content LIKE '%R3_ACTIVATE_REPORT%';



  • 10.  Re: SQL Queries

    Posted 02-15-2019 04:10 AM

    Thank you so much .  Works as expected. 

     

    is there any way to also to add the job start time , End Time  as well ? 



  • 11.  Re: SQL Queries

    Posted 02-15-2019 04:13 AM

    is it scheduling in a workflow?



  • 12.  Re: SQL Queries

    Posted 02-15-2019 04:16 AM

    Yes Rohit. 

     

    Most of the jobs are in a workflow and few are running independently



  • 13.  Re: SQL Queries

    Posted 02-15-2019 04:20 AM

    select OH_Name, OH_Idnr, JPP.JPP_Otype, JPP.JPP_Object, JPP.JPP_Lnr, JPP.JPP_Alias, JPP.JPP_TcpATime, JPP.JPP_TcpExecute, JPP.JPP_TcpATimetTZ, JPP.JPP_ErlstSTTime, JPP.JPP_ErlstStTimeTZ, JPP.JPP_LtstEndTime, JPP.JPP_LtstTimeTZ,
    CASE JPP.JPP_CCType
    WHEN 1 THEN 'All conditions Match'
    WHEN 2 THEN 'One condition Match'
    WHEN 3 THEN 'No condition Match'
    WHEN 4 THEN 'External dependent calendar'
    ELSE CAST(JPP.JPP_CCType as varchar(MAX))
    END as Calendar_Condition, JPPC.JPPC_CaleName, JPPC.JPPC_CaleKeyName,
    CASE JPPO.JPPO_Location
    WHEN 1 THEN 'Pre-Condition'
    WHEN 2 THEN 'Post-Condtion'
    WHEN 3 THEN 'IF Condition'
    ELSE CAST(JPPO.JPPO_Location as varchar(MAX))
    END as Condition_Type
    , JPPO.JPPO_JPP_Lnr, JPPO.JPPO_CarName, JPPO.JPPO_Active, JPPO.JPPO_Once, JPPO.JPPO_Type, JPOV.JPOV_VName, JPOV.JPOV_Value, JPOV.JPOV_Type, JPOP.JPOP_VName, JPOP.JPOP_Value
    from OH
    INNER JOIN JPP on JPP.JPP_OH_Idnr = OH.OH_Idnr
    -- pre, post and IF condition joins ************ start
    left outer join JPPO on JPPO.JPPO_OH_Idnr = JPP.JPP_OH_Idnr and JPPO.JPPO_JPP_Lnr = JPP.JPP_Lnr --and JPPO.JPPO_Active = 1
    left outer join JPOV on JPOV.JPOV_OH_Idnr = JPPO.JPPO_OH_Idnr and JPOV.JPOV_JPP_Lnr = JPPO.JPPO_JPP_Lnr and JPOV.JPOV_Location = JPPO.JPPO_Location and JPOV.JPOV_JPPO_Lnr = JPPO.JPPO_Lnr
    left outer join JPOP on JPOP.JPOP_OH_Idnr = JPOV.JPOV_OH_Idnr and JPOP.JPOP_JPP_Lnr = JPOV.JPOV_JPP_Lnr and JPOP.JPOP_Location = JPOV.JPOV_Location and JPOP.JPOP_JPOV_Lnr = JPOV.JPOV_JPPO_Lnr and JPOP.JPOP_JPOV_VName = JPOV.JPOV_VName
    -- pre, post and IF condition joins ************ end
    -- Calendar joins ************ start
    left outer join JPPC on JPPC.JPPC_OH_Idnr = JPP.JPP_OH_Idnr and JPPC.JPPC_JPP_Lnr = JPP.JPP_Lnr
    where OH_Name like '%<workflow_name>%'
    and OH.OH_Client = <client>
    ;



  • 14.  Re: SQL Queries

    Posted 02-15-2019 04:39 AM

    Thanks Rohit. But the above query is not showing the start and end time of the jobs 

    any ways to figure it out ? 



  • 15.  Re: SQL Queries

    Posted 02-15-2019 04:59 AM

    would you need the historical details? or

    the scheduled details? - if it is scheduled details, then JPP.JPP_ErlstSTTime, JPP.JPP_ErlstStTimeTZ