Maximo maintains the relationship between a person and their supervisor in the PERSONANCESTOR table and likewise maintains the relationship between a work order and its parent work order in the WOANCESTOR table. From time to time one or both of these tables can get out of sync with the tables they are supposed to represent. This is especially true of the PERSONANCESTOR table if the MIF is used to update the PERSON table as several versions of Maximo fail to properly maintain the table. While it is possible to run the integrity checker in repair mode to fix the problems this frequently is not a viable solution for a production environment due to the fact that it tries to fix other stuff as well. To overcome this issue I wrote a set of Oracle stored procedures to rebuild either table.

Normally you could write a stored procedure that would just cycle through all the PERSON records and then build the PERSONANCESTOR records. Unfortunately this approach does not build the necessary tree structure representing the organization. The easiest way to overcome this approach is to write a recursive procedure to parse through the organization building the records regardless of the depth of supervision in the company. This same thinking also works for the WOANCESTOR table for parent work orders.

To accomplish my goal I wrote three procedures for each ancestor table. The first procedure, DoPersonAncestor.sql, just loads and compiles the two Oracle stored procedures that will do the work. It then truncates the target table and then calls the stored procedure that does the actual work. The called stored procedure, PersonAncestorCall.sql reads through the person table calling the recursive procedure that does the actual insert to PERSONANCESTOR. The recursive procedure, WoAncestorBuild.sql then runs down through the person’s supervisor tree building the necessary records, until no more are found.

There are several things you need to be aware of when running these procedures. The first is they truncate the ancestor table of interest, so you should have a backup of the ancestor table prior to running. Secondly you should count how many rows are in the ancestor table so you can tell if it puts back about the same number. Thirdly you can run the integrity checker, not in repair mode to confirm all is good. These procedures all assume you are running them in the Maximo schema as Maximo.

There are also several database items you should be aware of. The stored procedures are very fast as they run in the database environment. So fast in fact you may think it failed. In addition the depth of the relationship is only limited by the amount of memory you have available for your database instance to run the stored procedure. Finally there is one tuning parameter in the PersonAncestorCall.sql procedure that can changed. A review of the code will show that it only commits every 1K person reads. The commit is the most expensive operation this procedure performs so you need to balance speed against the size of your rollback segments. I found that 1K would process 100K persons in less than a minute. Obviously performance will change dramatically based on your environment.

In the case of WOANCESTOR I use a value of 5K before committing as I had millions of work orders to process so it ran into the minute’s category. In addition you will see in the code that there is an additional process to drop the WOANCESTOR indexes then rebuild them after the inserts are complete. This will allow the stored procedure to run in minutes instead of hours. Also you will need to confirm you have the same name and number of indexes.

This code should be run on an idle or down Maximo implementation to avoid conflicts with the MBO’s trying to update at the same time.

If you need to reset the sequence for the ancestor table of interest it is also a good time to do this right after the table is truncated. There is some commented out code to do this included.

I have used this code successfully against Oracle 11, and 12 and Maximo 7.x implementations. I normally execute the process from within Oracle SQL developer. Some syntax may have to change if you use a different tool. If you have a SQL Server implementation you could use this code as a guide for a rewrite in SQL Server style SQL.

For those that have never worked with a recursive procedure, the first trick is make sure you have a way to exit the procedure. In PersonAncestorBuild it is:

--
-- Check to see if we are done with the chain.
--
        If new_supervisor is null then
          return;
        else
          PersonAncestorBuild(level+1,Person_In, new_supervisor);
        end if;

The second trick is to call itself with the new parameters, see above.

The third trick is to use a separate stored procedure to run down through the persons. In this case the PersonAncestorCall procedure sets up a cursor to read through the entire person table, and then calls the recursive procedure with each person:

Open Person_Cursor;
    Loop
      fetch Person_Cursor into PersonVal;
      exit when Person_Cursor%NOTFOUND;
     personancestorbuild(0,personval.personid,personval.supervisor);
      Commit_Count := Commit_Count + 1;
      If Commit_Count > 1000 then
        commit;
        Commit_Count := 0;
      end if;
    End Loop;
Close Person_Cursor;

There are seven files of interest to be download. Each should be reviewed for compliance with your environment.

Download Stored Procedures (ZIP)

Hope this helps folks interested in fixing their ancestor tables and gives insight into using recursion to build tree structures in a relational database.

 

Jerry R.

Maximo Systems Architect.