Solved

How to notify when background job hangs

C
Rank
Userlevel 3
Badge +6

We have many companies and many of these companies run multiple types of costing jobs.  These execute as background jobs in the early morning hours and often on weekends.  These jobs can take up to 2 ½ hours to run and they hold up all other costing jobs when stuck.   I would like to have an email/text message generated  to warn me when a background job is executing for more than 2 ½ hours.  Is this something I would create an application Server task for?    We  don’t have any so I don’t have any examples to look at.

We don’t have weekend staff and I don’t want to have to remote in every Saturday to check if background jobs are behaving.  

icon

Best answer by paul harland 11 August 2020, 20:02

View original

12 replies

paul harland
Rank
Userlevel 7
Badge +24

hello Nancy

 

i don’t believe there’s any way to do this with an application server task.

You need a query on the background job table, something like this:

 

(SELECT D.*

FROM IFSAPP.DEFERRED_JOB D

WHERE UPPER(STATE) = 'EXECUTING'

AND STARTED < SYSDATE - (2.5/24))

 

That will return the set of jobs you are looking for.

 

Question then is how you want to run it.  A fairly easy approach, currently in Apps 10 (using Enterprise Explorer) is to create a FNDMIG migration job based on the Command_sys.mail method.  Use the above query as a data source and then schedule this job to run every hour, using the functionality in the migration area.

This will email you if it finds any jobs that meet the criteria; otherwise you won’t get any email.

 

Instead of the above you could create some custom code and a scheduled task to do much the same thing, but i like the FNDMIG approach because it avoids deploying any custom packages.

 

Example Migration job, something like this:

 

 

 

C
Rank
Userlevel 3
Badge +6

Thank you.  I was able to create the migration job but I had to log in as app owner to do that.  I’m not seeing a way to schedule it though.  I see Scheduled Migration Jobs but I’m not seeing a way to schedule the job I created.

C
Rank
Userlevel 3
Badge +6

Never mind, I found it.  You have to RMB when looking at the details of the job.  I was trying through the list (Migration Jobs).

paul harland
Rank
Userlevel 7
Badge +24

Never mind, I found it.  You have to RMB when looking at the details of the job.  I was trying through the list (Migration Jobs).

You can use that option and then you might want to set it to run every 1 hour

 

paul harland
Rank
Userlevel 7
Badge +24

As for access as an enduser, you need to add your user here:

 

C
Rank
Userlevel 3
Badge +6

I added myself in the Data Migration Basic Data - User tab but I still can’t add a new Migration Job, nor can I see existing jobs - I have to login as app owner.  I even refreshed the security cache and logged out back in but no go.  If I have to do this as app owner I can live with that.  

 

But thanks for giving a great and thorough answer originally.  This is all set up.  We have a costing job scheduled early Saturday morning so maybe we’ll see if this actually works.  Is it wrong for me to want a job to hang so I can see if this works?  Have a great weekend!

paul harland
Rank
Userlevel 7
Badge +24

Hmm. That should have let you create jobs. 
 

In order to see existing jobs you need to add the job to the user on the Pr User tab in basic data. 

paul harland
Rank
Userlevel 7
Badge +24

You should be able to see the migration job running in background jobs every hour (or whatever schedule you set)

 

I’ll write you a better version of the query on Monday that sends just one email. As it is you will get 1 email per job

paul harland
Rank
Userlevel 7
Badge +24

Also Re: hoping for failure…

 

when I want something daft to work, I find a modicum of pessimism often does the trick. 
 

So logically in this case you should try a modicum of optimism… I think. 🤨

paul harland
Rank
Userlevel 7
Badge +24

Improvement: please use this query in the source box

(SELECT 

LISTAGG(D.JOB_ID ||'  '||D.DESCRIPTION, CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY 1) AS JOBS

FROM IFSAPP.DEFERRED_JOB D

WHERE UPPER(STATE) = 'EXECUTING'

AND STARTED < SYSDATE - (2.5/24))

  

 Put the word JOBS in the source column for the TEXT_ attribute - this is the email body.

 

This will lead to a single email with a nice format like the following, in case it finds multiple jobs:


8556356  Schedule id 124 : CustomerPayments
8556357  Schedule id 489 : Touch Apps [ServiceEngApp 1] Scheduled Activation
8556358  Schedule id 4077 : Touch Apps [ServiceEngApp 1] Reprocess Failed Transactions


 

C
Rank
Userlevel 3
Badge +6

Thank you so much for the info. 

One thing I found was that the LISTAGG function always returns one record even if it is null so you have to wrap it inside another select and add a where to avoid sending out a blank email every time the job is ran.

(SELECT JOBS FROM

(SELECT LISTAGG(D.JOB_ID ||' - '|| D.DESCRIPTION, CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY 1) AS JOBS FROM IFSAPP.DEFERRED_JOB D WHERE UPPER(STATE) ='EXECUTING' AND STARTED<SYSDATE - (2.5/24))

WHERE JOBS IS NOT NULL )
 

Maja Stanislawska
Rank
Userlevel 2
Badge +5

Hi  @CHFFORBESN,

Could you share if this setup proved useful in your company over the past years? Do you have some experiences and improvements that you could share?

Reply


Terms & ConditionsCookie settings