ESP dSeries Workload Automation

 View Only
  • 1.  Is there a way to get intermediary runs with LASTRUN vs just last execution

    Posted 12 days ago
    We're in the process of moving more of our application schedules from ESP to d-Series.  One of the things that we find useful in ESP is the LTJ job that displays the last 10 runs of a job including start/end time, return code, etc.  If within a generation of the application, a job fails and is re-submitted multiple times before successfully completing, the LTJ display will list each of those rather than just the last.

    We were trying to find an equivalent in d-Series and thought the LASTRUN CLI command was it but, at least from our testing, while the command does list multiple executins of the requested job, it seems to be limited to the last run from each generation of the application.

    Does anyone know if there's a way to get a list of the runs of a job whether they were successful or not?

    The reason we're asking this is because the App Teams sometimes contact us, usually after the job has completed, and say that the restart logic isn't working but it's usually related to the job getting a different return code than was specified.  If we had a way to list the runs of the job including failed runs, it would be easier to show them why the restart logic wasn't executed.

    Once again, thanks for your help with this.  Despite asking the question, I'll continue looking to see if I can find something that will work for us.

    Thanks,
    Donna Domovic


  • 2.  RE: Is there a way to get intermediary runs with LASTRUN vs just last execution

    Posted 12 days ago
    Hi  Donna
    You can go to Services - Reports - JOBS_BY_APPLICATION, however this doesn't tell you if the job completed successfully on its own or if it was forced complete.  

    There is also the option to create your own report within dSeries.  I have not had much luck figuring this out.

    I created a sql in sql developer and used these fields to get this report.  This is my go to but the downfall is someone on my team needs to run it and modify to application or job in question. 
    • if something is forced complete the conditions is Forced
    • If a job completes successfully after being resubmitted the state will be COMPLETED and no there is CONDITIONS will be empty
    APPL NAME APPL GEN NO SCHEDULED DATE_TIME APPL TAG JOB_NAME START DATE_TIME END DATE_TIME SUBMISSION INSTANCE AGENT_NAME STATE CONDITIONS COMPLETION
    CODE
    XX  15 2023-01-23 12:15:38   JOB1 2023-01-23 12:15:38 2023-01-23 12:20:38 1 server name COMPLETE   0
    XX  16 2023-01-23 12:16:18   JOB2 2023-01-23 12:20:56 2023-01-23 12:20:56 1 server name FAILED   127
    XX  16 2023-01-23 12:16:18   JOB2 2023-01-23 12:21:15 2023-01-23 12:21:15 2 server name FAILED   127
    XX  16 2023-01-23 12:16:18   JOB2 2023-01-23 12:22:10 2023-01-23 12:22:10 3 server name FAILED   127
    XX  16 2023-01-23 12:16:18   JOB1 2023-01-23 12:20:39 2023-01-23 12:22:39 1 server name FAILED   9
    XX  16 2023-01-23 12:16:18   JOB1 2023-01-23 12:20:39 2023-01-23 12:23:03 1 server name COMPLETE Forced 9
    XX  16 2023-01-23 12:16:18   JOB2 2023-01-23 12:22:57 2023-01-23 12:23:57 4 server name COMPLETE   0



  • 3.  RE: Is there a way to get intermediary runs with LASTRUN vs just last execution

    Broadcom Employee
    Posted 12 days ago
    Hi,

    I did a small test on the lastrun cli command and I see that it shows the job details even if it is not started or failed
    As you can see the job - HTTP-V20 is in different application generations in different states. Please let me know if anything else is needed

    -> lastrun job("HTTP-V20")
    HTTP-V20
    Job type: SERVLETV2
    Start time:
    End time:
    Application: MSSQL_SERVER_TEST.3
    State: WAITING
    Completion Code:
    HTTP-V20
    Job type: SERVLETV2
    Start time: Wed Jan 18 14:03:19 IST 2023
    End time: Tue Jan 24 16:30:21 IST 2023
    Application: OPP_XML.3
    State: COMPLETE
    Completion Code:
    HTTP-V20
    Job type: SERVLETV2
    Start time: Wed Jan 18 14:03:20 IST 2023
    End time: Wed Jan 18 14:03:20 IST 2023
    Application: JSON_PUBLIC_RESTAPI.3
    State: SUBERROR
    Completion Code:
    HTTP-V20
    Job type: SERVLETV2
    Start time: Wed Jan 18 14:03:19 IST 2023
    End time: Wed Jan 18 14:03:19 IST 2023
    Application: HEADER_SANDY.3
    State: SUBERROR
    Completion Code:
    HTTP-V20
    Job type: SERVLETV2
    Start time: Wed Jan 18 13:06:02 IST 2023
    End time: Wed Jan 18 13:06:02 IST 2023
    Application: JSON_PUBLIC_RESTAPI.2
    State: SUBERROR
    Completion Code:
    HTTP-V20
    Job type: SERVLETV2
    Start time: Wed Jan 18 13:06:02 IST 2023
    End time: Wed Jan 18 13:06:02 IST 2023
    Application: OPP_XML.2
    State: SUBERROR
    Completion Code:
    HTTP-V20
    Job type: SERVLETV2
    Start time: Wed Jan 18 13:06:02 IST 2023
    End time: Wed Jan 18 13:06:02 IST 2023
    Application: HEADER_SANDY.2
    State: SUBERROR
    Completion Code:
    HTTP-V20
    Job type: SERVLETV2
    Start time: Wed Jan 18 12:10:05 IST 2023
    End time: Wed Jan 18 12:10:05 IST 2023
    Application: JSON_PUBLIC_RESTAPI.1
    State: SUBERROR
    Completion Code:
    HTTP-V20
    Job type: SERVLETV2
    Start time: Wed Jan 18 12:10:04 IST 2023
    End time: Wed Jan 18 12:10:04 IST 2023
    Application: OPP_XML.1
    State: SUBERROR
    Completion Code:
    HTTP-V20
    Job type: SERVLETV2
    Start time: Wed Jan 18 12:10:03 IST 2023
    End time: Wed Jan 18 12:10:03 IST 2023
    Application: HEADER_SANDY.1
    State: SUBERROR
    Completion Code:


    - Ravi Kiran


  • 4.  RE: Is there a way to get intermediary runs with LASTRUN vs just last execution

    Posted 12 days ago
    Not sure if you want to go this route, but if my Apps Team asked me to provide me the information that yours are asking you for, I would write a little Python script to dive into the database on the backend for it.

    Here is SQL that I wrote to get you started:

    SELECT eap.appl_name, 
           egj.job_name, 
           egj.job_qualifier, 
           egj.start_date_time, 
           egj.end_date_time, 
           egj.state, 
           egj.completion_code, 
           egj.submission_instance
    FROM ESP.ESP_GENERIC_JOB egj
    JOIN ESP.ESP_APPLICATION eap
    ON egj.appl_id = eap.appl_id
    WHERE egj.job_name = '[job_name]'
    ORDER BY egj.end_date_time
    FETCH FIRST 10 ROWS ONLY​

    I know other database engines have different syntax for row limiting.  Our database is Oracle 12c,

    ------------------------------
    Erik Schminke
    Systems Engineer
    ITS Tech Services
    Hormel Foods Corporation
    ------------------------------



  • 5.  RE: Is there a way to get intermediary runs with LASTRUN vs just last execution

    Posted 12 days ago
    Edited by SHARON SHIMANEK 12 days ago
    Hi Kiran
    the problem with the CLI is it only shows 1 instance of the job per appl.gen, the latest instance and state.  It doesn't show every attempt of running like we see when using SQL or a python script.  The reports will show all instances, but in the end when the job says complete on the report you do know if it really ran successful or if it was force completed.

     -> LASTRUN JOB("JOB2") APPLICATION("XX_SHARON2") RUNS(10)

    JOB2

      Job type:        UNIX

      Start time:      Tue Jan 24 09:14:28 CST 2023

      End time:        Tue Jan 24 09:15:28 CST 2023

      Application:     XX_SHARON2.17

      State:           COMPLETE

      Completion Code:

     SQL - This helps more with troubleshooting what happened with a job

    APPL

    GEN

    JOB2

    Start Date Time

    End Date Time

    Server

    State

    Condition

    XX_SHARON2

    17

    JOB2

    1/24/2023 9:10

    1/24/2023 9:10

    server

    FAILED

     

    XX_SHARON2

    17

    JOB2

    1/24/2023 9:11

    1/24/2023 9:11

    server

    FAILED

     

    XX_SHARON2

    17

    JOB2

    1/24/2023 9:13

    1/24/2023 9:13

    server

    FAILED

     

    XX_SHARON2

    17

    JOB2

    1/24/2023 9:14

    1/24/2023 9:14

    server

    FAILED

     

    XX_SHARON2

    17

    JOB2

    1/24/2023 9:14

    1/24/2023 9:15

    server

    COMPLETE

     

     




  • 6.  RE: Is there a way to get intermediary runs with LASTRUN vs just last execution

    Posted 11 days ago
    Thanks to everyone for all the replies and discussion.  Sharon is correct about why the CLI command doesnt' work for us.

    While the Jobs by Application report does show multiple runs of the job, it doesn't show the return/exit code which is something we'd like to see. 

    Based on that, I think the SQL query will be our best option.  I'll work on getting access so that I can run the reports with sql.

    One follow up question on that if you don't mind.  Are the d-Series tables and associated columns documented in one of the manuals or some place online?  I've written SQL before but having the list of tables and columns would help.

    Thanks,
    Donna Domovic


  • 7.  RE: Is there a way to get intermediary runs with LASTRUN vs just last execution

    Posted 11 days ago
    Not sure what version you are on, we are R12.3.  In sql developer once we log in and connect to DB we can see all the tables and field too.

    Database Tables (broadcom.com)
    if you click on table you see all the fields



  • 8.  RE: Is there a way to get intermediary runs with LASTRUN vs just last execution

    Posted 11 days ago
    Edited by SHARON SHIMANEK 11 days ago
    This my sql that give me the most info. On every application properties in the tag field we have - %APPL._truser    this tells us who triggered the flow, if the flow was automatically scheduled it will say SCHEDMASTER, else the userid of the person that triggered it.  I remove and add fields and update the where like or = for specific situation. This gives me a lot of info.

    select WA.esp_APPLICATION.APPL_NAME,WA.esp_APPLICATION.APPL_gen_no,WA.esp_APPLICATION.scheduled_date_time,WA.esp_APPLICATION.tag,WA.esp_GENERIC_JOB.JOB_NAME,WA.esp_GENERIC_JOB.JOB_qualifier,WA.esp_GENERIC_JOB.subappl,WA.esp_GENERIC_JOB.tag,
    WA.esp_GENERIC_JOB.start_date_time,WA.esp_GENERIC_JOB.end_Date_time,WA.esp_GENERIC_JOB.submission_instance,WA.esp_GENERIC_JOB.agent_name,
    WA.esp_GENERIC_JOB.state,WA.esp_GENERIC_JOB.conditions,WA.esp_GENERIC_JOB.completion_code,WA.esp_GENERIC_JOB.status,WA.esp_GENERIC_JOB.script,WA.esp_GENERIC_JOB.argument
    from WA.esp_APPLICATION,WA.esp_GENERIC_JOB
    WHERE WA.esp_APPLICATION.APPL_ID=WA.ESP_GENERIC_JOB.APPL_ID and (WA.esp_GENERIC_JOB.end_Date_time > sysdate-2
    and (WA.esp_GENERIC_JOB.job_name like 'JOB2'));