Is there a way to get intermediary runs with LASTRUN vs just last execution

[DONNA DOMOVIC]

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

[SHARON SHIMANEK]

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

[Ravi Kiran Kunduri]

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

[Erik Schminke]

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
------------------------------

[SHARON SHIMANEK]

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

 

[DONNA DOMOVIC]

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

[SHARON SHIMANEK]

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

[SHARON SHIMANEK]

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'));