Maximo escalations demystified (2/3) Performances and the ‘repeat’ option

In our first Maximo escalations demystified post I have described the general functionality of Escalations. We will now dig into some details that will help you using this powerful feature at its best.

The Repeat flag

When defining an escalation you always have to create an escalation point and then you can assign actions and communications to run. The ‘repeat flag’ have a somewhat strange meaning that is described in the official documentation in this way:

Specifies whether to continue monitoring for records that meet this escalation point’s criteria after the first instance is found. If selected, the escalation will continue to trigger actions and/or notifications associated with this escalation point when the conditions are met. If the check box is cleared (the default), the escalation only runs once when the conditions are met.

This flag has some serious implications that I will explain hereafter.

Common mistakes

Escalations are a great tool in the hand of the Maximo specialist but it’s not rare to see misuse of it. I will share some examples here.

  1. Escalations scheduled to run each minute (or even more frequently) – This particularly dangerous for system performances especially if the query is running on large tables like WORKORDER, TICKET, WFASSIGNMENT, etc. Always ask yourself, can I reduce the frequency? If the answer is no, you are probably using the wrong tool. Think about using an automation script instead. Do you know Maximo 7.6 allows to run a script with ‘After Save’ or ‘After Commit’ trigger?
  2. Badly designed where condition – I’m very cynical here. Most of the Maximo specialists have poor SQL skills. This is probably because they started their career as developers (programmers). A badly designed SQL where cause can have execution time of more than a minute. In one of my engagements I have reduced the the execution time of the escalation from more than 2 minutes to less than 1 second by simply adding the right index to the WORKORDER table and rewriting an inner clause.
  3. Wrong selection of records – Inexperienced consultants relies too much on the ‘Repeat’ flag on the escalation point to exclude records that shouldn’t be processed. This means that the condition does not filter out useless records. Se the example below.

Lets pretend you want to close automatically WORKORDERS if the status has not been changed for more that 60 days.

The following escalation contains all the errors mentioned above.

  • Escalation: Close inactive Work Orders
  • Applies to: WORKORDER
  • Frequency: each minute
  • Condition: statusdate>sysdate-60
  • Escalation Point
    • Elapsed Time Attribute: STATUSDATE
    • Elapsed Time Interval: 60
    • Unit of Measure: DAYS
    • Repeat: False
  • Action
    • WO CLOSE
escalation

Error 1 – Schedule

Why do you need to run the escalation each minute?
I’m sure you can perfectly run the escalation each day during night hours without any considerable side effect.

Error 2 – Poor SQL

The escalation will translate to the following SQL clause.

select * from workorder
where (statusdate <= { ts '2019-06-28 16:04:00.796' })
  and not exists (select escrepeattrack.ownerid from escrepeattrack where escrepeattrack.ownerid = workorderid and objectname= 'WORKORDER' and refpointid=228 and escalation= 'WOCLOSE')

Each time the escalation runs Maximo will have to find records in the workorder table where STATUSDATE is more that a specific timestamp. Now if you look at WORKORDER table indexes you will find out that there is no index defined on the STATUSDATE field. This means that the database will do a full table scan on the WORKORDER table that can have millions of records.

Adding an index on the STATUSDATE could improve performance. However this is not the case because, after years of operations’, 99% of the records will have an older status date so there is no meaning in adding the index because it will always be convenient for the database to perform a full scan.

In this case we have to rethink the whole thing. Let’s see the third error.

Error 3 – Wrong selection of records

Why do you have to include work orders that are already closed?

The escalation relies on the ‘repeat flag’ set to false to avoid that Maximo tries to close the same record twice but this is not the most efficient approach.

Solution

  • Escalation: Close inactive Work Orders
  • Applies to: WORKORDER
  • Frequency: every day at 2 AM
  • Condition: status!=’CLOSE’ and historyflag=0 and statusdate>sysdate-60
  • Escalation Point
    • Repeat: True
  • Action
    • WO CLOSE

This will be translated with the following query.

select * from workorder
where (status!='CLOSE' and  historyflag=0 and statusdate>sysdate-60)

This will have the following benefits:

  • Schedule is now more relaxed and is running in low-workload time.
  • The is no join with the ESPREPEATTRACK because the ‘repeat flag’ is selected. Once the escalation runs on a record it will set the status to CLOSE so it will be excluded in the next run without having to look in the ESPREPEATTRACK table.
  • The condition on STATUS and HISTORYFLAG will help per database query optimizer to use the right database index (there are several indexes on those fields).

I hope this will help you improving escalation’s design for your customers.

Continues: Maximo escalations demystified (3/3) Monitoring execution

Maximo escalations demystified (2/3) Performances and the ‘repeat’ option

6 thoughts on “Maximo escalations demystified (2/3) Performances and the ‘repeat’ option

  1. Our DBA said to look at the indexes provided with Maximo and write our queries leveraging pre-existing indexes as much as possible. She also taught me the “EXPLAIN PLAN” tool is very powerful to see how the database will run the query. Like you said, full table scan on work order table is not optimized query.

    I’ve also had to do some SQL “hacks” where I include in condition for escalation to not run if it is a weekend or outside business hours. Escalation scheduler is useful for basic recurrences but sometimes we get interesting requests from the users.

  2. the problem is to know why we use escalation, and the frequency must be determinated with the matter of the business , if we want to do it only in 2 days or three why we did it every day ?
    So i agree with you Mr Bruno for this subject and every transaction in maximo will be transformed into SQL query to collect data from the BD in order to track them, that means thats the most important question here why we use MAXIMO or other CMMS ?
    The answer is to make the best decision at the best time and with the minium cost.

  3. Hi Bruno ,
    I believe we should ignore tasks and include synonym values ,and date filer should be (statusdate sysdate-60), am I right?

    select *
    from workorder
    where
    status not in ( select value from synonymdomain where domainid =’WOSTATUS’ and maxvalue = ‘CLOSE’) and istask = 0 and historyflag=0 and statusdate <sysdate-60 ;

    1. Yes you are definitely right.
      However, i always try to simplify things to better focus on the key concepts explained in the article.
      Here I was explaining the repeat flag so the SQL query is not so important.
      Thank you for the feedback anyway.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top