Skip to main content
Solved

How to notify when background job hangs

C
Do Gooder (Customer)
Forum|alt.badge.img+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.  

Best answer by paul harland

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:

 

 

 

View original
    Did this topic help you find an answer to your question?

    12 replies

    paul harland
    Superhero (Employee)
    Forum|alt.badge.img+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:

     

     

     

    A
    1 person likes this
    • A
      AddRangaF
    C
    Do Gooder (Customer)
    Forum|alt.badge.img+6
    • CHFFORBESN
    • Author
    • Do Gooder (Customer)
    • 13 replies
    • August 11, 2020

    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
    Do Gooder (Customer)
    Forum|alt.badge.img+6
    • CHFFORBESN
    • Author
    • Do Gooder (Customer)
    • 13 replies
    • August 11, 2020

    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
    Superhero (Employee)
    Forum|alt.badge.img+24
    CHFFORBESN wrote:

    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
    Superhero (Employee)
    Forum|alt.badge.img+24

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

     

    A
    1 person likes this
    • A
      AddRangaF
    C
    Do Gooder (Customer)
    Forum|alt.badge.img+6
    • CHFFORBESN
    • Author
    • Do Gooder (Customer)
    • 13 replies
    • August 14, 2020

    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
    Superhero (Employee)
    Forum|alt.badge.img+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
    Superhero (Employee)
    Forum|alt.badge.img+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
    Superhero (Employee)
    Forum|alt.badge.img+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
    Superhero (Employee)
    Forum|alt.badge.img+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
    1 person likes this
    • C
      CHFFORBESN
    C
    Do Gooder (Customer)
    Forum|alt.badge.img+6
    • CHFFORBESN
    • Author
    • Do Gooder (Customer)
    • 13 replies
    • September 22, 2020

    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 )
     

    paul harland
    1 person likes this
    • paul harland
      paul harland
    Maja Stanislawska
    Sidekick (Customer)
    Forum|alt.badge.img+7

    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

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings