As the Maximo SME, we are often called upon to troubleshoot issues with the application. This usually involves looking at the history of data updates to identify what happened. Most major tables in Maximo already have the ChangeBy and ChangeDate fields which contains the details of the time and the person who made the last change. Combine it with other historical data such as Status Change, Audit Tracking, System Log, it allows us to traced down on what happened.
However, for certain data tables, the ChangeBy and ChangeDate fields are not available. In such case, one trick that I used is to use the rowstamp of the record to provide such information. Rowstamp is a special field by Maximo which is included in every table. It is set with a new incremental binary number everytime when the record is updated. The number comes from a global sequence, and thus, it can be use to identify the sequence of activities. In this case, we use it to identify the rough date/time when a record is updated.
Consider the following example, at one client that I support, there is a frequence occurence of an issue that prevent the user from closing a work order. The error occurs when there are records in the ASSETHISTORY and ASSETANCESTOR tables that are not supposed to be there. When a work order is closed, Maximo write new entries to the the two tables, essentially registering that the work has been done on the asset and all of its ancestors. This is a common error which could occurs for many different reasons. If the work order is not closed, there shouldn’t be entries in those two tables that referencing the same work order number.
I needed to identify the root cause of this issue in this case, and wanted to know when the records were inserted and by whom. To do this, I use the following query which compares the rowstamp of the ASSETHISTORY table with the WORKORDER table.
SELECT *,
(SELECT max(changedate) FROM workorder w1 WHERE w1.rowstamp < assethistory.rowstamp) lowDate,
(SELECT min(changedate) FROM workorder w2 WHERE w2.rowstamp > assethistory.rowstamp) maxDate
FROM assethistory WHERE wonum = 'M1041146'Since this is the most frequently updated table in Maximo, it has records updated every few minutes. The result shows me the date range when the ASSETHISTORY record was inserted:
In this case, I can see that the record was inserted at roughly around 11/Sep 17:18 PM. By looking at other Maximo activities such as WO status history and audit data, I can also identify the person who initiated the activity that resulted in this orphan record. It allows me to give him a call to ask a few additional questions for the ongoing investigation.
