Maximo

MAM 7.6.1.2 / Oracle 19c:

In Maximo tables, such as CLASSSTRUCTURE, it looks like we have a ROWSTAMP column.

Question:
Do the values in that column represent dates?
In other words, can the ROWSTAMP column be used as a poorman's CHANGEDATE column?

Thanks.

Some background information on the rowstamp:

https://www.ibm.com/support/pages/usage-rowstamp-column-maximo-tables

Maybe use normal audit; I mean knowing what's changed by whom and when.

Set the table to audit, choose columns to track and you're done.

------------------------------
Christiaan Lok
------------------------------

Original Message:
Sent: Thu May 27, 2021 06:28 PM
From: User1971
Subject: Does the ROWSTAMP column represent a date?

MAM 7.6.1.2 / Oracle 19c:

In Maximo tables, such as CLASSSTRUCTURE, it looks like we have a ROWSTAMP column.

Question:
Do the values in that column represent dates?
In other words, can the ROWSTAMP column be used as a poorman's CHANGEDATE column?

Thanks.

Rowstamp is a sequence that gets incremented for each DB operation performed and the same sequence is used across all Maximo objects.
It can be used as a rough guide, say you have a classstructure change which says rowstamp 2001, then may be you can check one of the most  frequently updated objects like Workorder for rowstamp values between 1900 and 2000 and if there is an entry, the change date associated will be near to the date when classstructure was modified. This will just give us a rough idea and this approach does take significant time as well to find a nearby rowstamp number in another object which has changedate.

------------------------------
Akshay T
------------------------------

Original Message:
Sent: Thu May 27, 2021 08:10 PM
From: Christiaan Lok
Subject: Does the ROWSTAMP column represent a date?

Some background information on the rowstamp:

https://www.ibm.com/support/pages/usage-rowstamp-column-maximo-tables

Maybe use normal audit; I mean knowing what's changed by whom and when.

Set the table to audit, choose columns to track and you're done.

------------------------------
Christiaan Lok

Original Message:
Sent: Thu May 27, 2021 06:28 PM
From: User1971
Subject: Does the ROWSTAMP column represent a date?

MAM 7.6.1.2 / Oracle 19c:

In Maximo tables, such as CLASSSTRUCTURE, it looks like we have a ROWSTAMP column.

Question:
Do the values in that column represent dates?
In other words, can the ROWSTAMP column be used as a poorman's CHANGEDATE column?

Thanks.

No the value does not represent a date.
It represents a number from a sequence. Maximo uses it to detect if a row has changed.

I talk about it's potential role in detecting changes in my series about rolling back Maximo here:
https://www.linkedin.com/pulse/problems-identifying-changes-rollback-mini-series-mark-robbins/

The short answer is that it probably isnt going to help you


------------------------------
Mark Robbins
Support Lead/Technical Design Authority / IBM Champion 2017 & 2018 & 2019 & 2020 & 2021
Vetasi Limited
https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/
------------------------------

Original Message:
Sent: Thu May 27, 2021 06:28 PM
From: User1971
Subject: Does the ROWSTAMP column represent a date?

MAM 7.6.1.2 / Oracle 19c:

In Maximo tables, such as CLASSSTRUCTURE, it looks like we have a ROWSTAMP column.

Question:
Do the values in that column represent dates?
In other words, can the ROWSTAMP column be used as a poorman's CHANGEDATE column?

Thanks.

Christiaan and Mark's responses are good that it won't really give you what you're exactly looking for. I wanted to add though that it can in certain scenarios function like a changedate column when you're trying to identify records that have changed. If you're trying to perform a simple delta sync of records (IE which records have changed since I last synchronized), rowstamp works well. IBM even utilizes it in their REST API for delta sync. You just need to capture the maximum that you retrieved and then search for something greater than that value. But there's no way to turn that back into a literal date time nor help you with who made the change.

E-Audit is useful, but can be bad on transactional tables like WO. With e-audit turned on, every WO created/deleted will be loaded into the audit table, even if the attribute you're performing e-audit on isn't set. This can cause the tables to get large and there's no supported way to add indexes to these objects. That means querying the data has to perform a full table scan which isn't good. We sometimes perform our own e-audit like process for attributes we care about in a custom object that only records the data we want.

------------------------------
Steven Shull
Director of Development
Projetech Inc
------------------------------

Original Message:
Sent: Thu May 27, 2021 06:28 PM
From: User1971
Subject: Does the ROWSTAMP column represent a date?

MAM 7.6.1.2 / Oracle 19c:

In Maximo tables, such as CLASSSTRUCTURE, it looks like we have a ROWSTAMP column.

Question:
Do the values in that column represent dates?
In other words, can the ROWSTAMP column be used as a poorman's CHANGEDATE column?

Thanks.

Stephen,
In the old XML based outbound integration pattern from Maximo - we used to include messageID (OOB) on the XML header to indicate a unique integration-transaction id.  
In the REST/API outbound initiated from Maximo, I do not see any corresponding unique identifier for each outbound API call (in the json payload or header). So, we included the "_rowstamp" in the outbound json payload when making external API call. 


------------------------------
Dianne Woodley
------------------------------

Original Message:
Sent: Fri May 28, 2021 09:03 AM
From: Steven Shull
Subject: Does the ROWSTAMP column represent a date?

Christiaan and Mark's responses are good that it won't really give you what you're exactly looking for. I wanted to add though that it can in certain scenarios function like a changedate column when you're trying to identify records that have changed. If you're trying to perform a simple delta sync of records (IE which records have changed since I last synchronized), rowstamp works well. IBM even utilizes it in their REST API for delta sync. You just need to capture the maximum that you retrieved and then search for something greater than that value. But there's no way to turn that back into a literal date time nor help you with who made the change.

E-Audit is useful, but can be bad on transactional tables like WO. With e-audit turned on, every WO created/deleted will be loaded into the audit table, even if the attribute you're performing e-audit on isn't set. This can cause the tables to get large and there's no supported way to add indexes to these objects. That means querying the data has to perform a full table scan which isn't good. We sometimes perform our own e-audit like process for attributes we care about in a custom object that only records the data we want.

------------------------------
Steven Shull
Director of Development
Projetech Inc

Original Message:
Sent: Thu May 27, 2021 06:28 PM
From: User1971
Subject: Does the ROWSTAMP column represent a date?

MAM 7.6.1.2 / Oracle 19c:

In Maximo tables, such as CLASSSTRUCTURE, it looks like we have a ROWSTAMP column.

Question:
Do the values in that column represent dates?
In other words, can the ROWSTAMP column be used as a poorman's CHANGEDATE column?

Thanks.

@Steven Shull

(Oracle 19c)
I wonder i​f it would be possible to track a date for the ROWSTAMP sequence values somehow?

1. I'm not sure how Oracle sequences work. Maybe there's a mechanism that can be used to log a date for each ROWSTAMP sequence value?
   1. What DB schema is the ROWSTAMP sequence stored in? 
   2. Are DB sequences really just DB tables under the covers?
2. Or is there a mechanism in Maximo that could be used to store dates (time of day isn't as important) for each ROWSTAMP sequence value?
   1. For example: Create a custom ROWSTAMP_DATE table (ROWSTAMP col, ROWSTAMP_DATE col), and populate the values on a schedule via Maximo?
3. And then, for either #1 or #2, join from the Maximo table (i.e. CLASSTRUCTURE) to the ROWSTAMP table...to essentially get the CHANGEDATE value.
4. Or something better?

---

The reason I ask:

When doing analysis/troubleshooting on various Maximo tables (like the classification-related tables) it would be handy to have a CHANGEDATE column...in pretty much any Maximo table. I don't really want to go to the trouble, or take on the overhead, of enabling Maximo Audit Tracking on dozens of Maximo tables.

Instead, it would just be handy to have a simple CHANGEDATE column...just like we do with the WORKORDER table.
We're really close to having that with the ROWSTAMP column...but like we've said, it's not a date. But what if we could store the ROWSTAMP date somewhere else? ​​​​

Regarding the article that @Christiaan Lok​ provided:

Usage of ROWSTAMP column in Maximo tables

"In Oracle, the column is populated by a trigger on each table. The trigger selects values from an Oracle object called a sequence. Every insert and update of a record in any Maximo table causes the trigger to be executed. The sequence is named MAXSEQ. The triggers are named tablename_T."

I can't seem to find a sequence in the Maximo DB called "MAXSEQ":


I wonder where it is? Maybe I've missed something.
Original Message:
Sent: Thu June 03, 2021 10:03 AM
From: User1971
Subject: Does the ROWSTAMP column represent a date?

@Steven Shull

(Oracle 19c)
I wonder i​f it would be possible to track a date for the ROWSTAMP sequence values somehow?

1. I'm not sure how Oracle sequences work. Maybe there's a mechanism that can be used to log a date for each ROWSTAMP sequence value?
   1. What DB schema is the ROWSTAMP sequence stored in? 
   2. Are DB sequences really just DB tables under the covers?
2. Or is there a mechanism in Maximo that could be used to store dates (time of day isn't as important) for each ROWSTAMP sequence value?
   1. For example: Create a custom ROWSTAMP_DATE table (ROWSTAMP col, ROWSTAMP_DATE col), and populate the values on a schedule via Maximo?
3. And then, for either #1 or #2, join from the Maximo table (i.e. CLASSTRUCTURE) to the ROWSTAMP table...to essentially get the CHANGEDATE value.
4. Or something better?

---

The reason I ask:

When doing analysis/troubleshooting on various Maximo tables (like the classification-related tables) it would be handy to have a CHANGEDATE column...in pretty much any Maximo table. I don't really want to go to the trouble, or take on the overhead, of enabling Maximo Audit Tracking on dozens of Maximo tables.

Instead, it would just be handy to have a simple CHANGEDATE column...just like we do with the WORKORDER table.
We're really close to having that with the ROWSTAMP column...but like we've said, it's not a date. But what if we could store the ROWSTAMP date somewhere else? ​​​​

I found this MAXSEQ sequence :



------------------------------
Dianne Woodley
------------------------------

Original Message:
Sent: Thu June 03, 2021 10:19 AM
From: User1971
Subject: Does the ROWSTAMP column represent a date?

Regarding the article that @Christiaan Lok​ provided:

Usage of ROWSTAMP column in Maximo tables

"In Oracle, the column is populated by a trigger on each table. The trigger selects values from an Oracle object called a sequence. Every insert and update of a record in any Maximo table causes the trigger to be executed. The sequence is named MAXSEQ. The triggers are named tablename_T."

I can't seem to find a sequence in the Maximo DB called "MAXSEQ":


I wonder where it is? Maybe I've missed something.
Original Message:
Sent: Thu June 03, 2021 10:03 AM
From: User1971
Subject: Does the ROWSTAMP column represent a date?

@Steven Shull

(Oracle 19c)
I wonder i​f it would be possible to track a date for the ROWSTAMP sequence values somehow?

1. I'm not sure how Oracle sequences work. Maybe there's a mechanism that can be used to log a date for each ROWSTAMP sequence value?
   1. What DB schema is the ROWSTAMP sequence stored in? 
   2. Are DB sequences really just DB tables under the covers?
2. Or is there a mechanism in Maximo that could be used to store dates (time of day isn't as important) for each ROWSTAMP sequence value?
   1. For example: Create a custom ROWSTAMP_DATE table (ROWSTAMP col, ROWSTAMP_DATE col), and populate the values on a schedule via Maximo?
3. And then, for either #1 or #2, join from the Maximo table (i.e. CLASSTRUCTURE) to the ROWSTAMP table...to essentially get the CHANGEDATE value.
4. Or something better?

---

The reason I ask:

When doing analysis/troubleshooting on various Maximo tables (like the classification-related tables) it would be handy to have a CHANGEDATE column...in pretty much any Maximo table. I don't really want to go to the trouble, or take on the overhead, of enabling Maximo Audit Tracking on dozens of Maximo tables.

Instead, it would just be handy to have a simple CHANGEDATE column...just like we do with the WORKORDER table.
We're really close to having that with the ROWSTAMP column...but like we've said, it's not a date. But what if we could store the ROWSTAMP date somewhere else? ​​​​

You can get approximate date/time for a given ROWSTAMP. You can try below query in Oracle:

SELECT 'BEFORE' AS when, c2.endtime AS timestamp, c2.rowstamp
  FROM crontaskhistory c2
 WHERE c2.rowstamp = (SELECT MIN (TO_NUMBER (c0.rowstamp))
                        FROM crontaskhistory c0
                       WHERE TO_NUMBER (c0.rowstamp) > 1328121784)
UNION
SELECT 'AFTER' AS when, c2.endtime AS timestamp, c2.rowstamp
  FROM crontaskhistory c2
 WHERE c2.rowstamp = (SELECT MAX (TO_NUMBER (c1.rowstamp))
                        FROM crontaskhistory c1
                       WHERE TO_NUMBER (c1.rowstamp) < 1328121784); --replace both hardcoded rowstamp values with your rowstamp value​

This method might be useful for a one-off check. It returns two rows with dates. The ROWSTAMP you are checking against was set sometime between those dates.
The precision of this method is between minutes and weeks or more. It depends on how often your crontasks write to CRONTASKHISTORY table.

------------------------------
Bart Sikorski
------------------------------

Original Message:
Sent: Thu May 27, 2021 06:28 PM
From: User1971
Subject: Does the ROWSTAMP column represent a date?

MAM 7.6.1.2 / Oracle 19c:

In Maximo tables, such as CLASSSTRUCTURE, it looks like we have a ROWSTAMP column.

Question:
Do the values in that column represent dates?
In other words, can the ROWSTAMP column be used as a poorman's CHANGEDATE column?

Thanks.

That's an interesting idea.

I've been playing around with a query that does a similar sort of thing based on CRONTASKHISTORY:

select
    classstructureid,
    description,
    class_rowstamp,
    min(starttime) as approx_last_edit_date
from
    (
    select 
        a.classstructureid,
        a.description,
        a.rowstamp as class_rowstamp,
        b.rowstamp as cron_rowstamp,
        b.starttime
    from   
        (select classstructureid, description, cast(rowstamp as number) as rowstamp from maximo.classstructure) a
    cross join
        (select cast(rowstamp as number) as rowstamp, starttime from maximo.crontaskhistory) b 
    where
        b.rowstamp > a.rowstamp
    )
group by
    classstructureid,
    description,
    class_rowstamp
--having
--    trunc(min(starttime)) = to_date('06/01/2021', 'MM-DD-YYYY')

It seems to work ok. Although I haven't had a chance to thoroughly audit the logic/results. There could be a mistake in there somewhere.

The performance isn't as bad as I thought it'd be:

• CLASSTRUCTURE has 2,600 rows
• CRONTASKHISTORY has 41,000
• The query takes 20 seconds to run, which isn't terrible. However, it'll get worse when CRONTASKHISTORY grows (we've only been live in Prod for a month).

Cross referencing against the crontaskhistory table is a clever idea.
It is liable to suffer performance problems as the crontaskhistory table grows. Queries on older DB2 instances also locked the table being selected unless a cause ( as read only ?) was added onto the select statement.

If you just wanted to know the time of the latest run (i.e. within the last few minutes) then you could reduce the resultset by referencing a crontask that runs regularly.

That approach wouldn't work if you wanted to look back at something from last week.
Some system administrators/developers don't set the keep history flag so you may have a limited number of records to go from.
Don't forget the crontask manager also deletes old crontask history records if a limit is set.


------------------------------
Mark Robbins
Support Lead/Technical Design Authority / IBM Champion 2017 & 2018 & 2019 & 2020 & 2021
Vetasi Limited
https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/
------------------------------

Original Message:
Sent: Thu June 10, 2021 04:06 PM
From: User1971
Subject: Does the ROWSTAMP column represent a date?

That's an interesting idea.

I've been playing around with a query that does a similar sort of thing based on CRONTASKHISTORY:

select    classstructureid,    description,    class_rowstamp,    min(starttime) as approx_last_edit_datefrom    (    select         a.classstructureid,        a.description,        a.rowstamp as class_rowstamp,        b.rowstamp as cron_rowstamp,        b.starttime    from           (select classstructureid, description, cast(rowstamp as number) as rowstamp from maximo.classstructure) a    cross join        (select cast(rowstamp as number) as rowstamp, starttime from maximo.crontaskhistory) b     where        b.rowstamp > a.rowstamp    )group by    classstructureid,    description,    class_rowstamp--having--    trunc(min(starttime)) = to_date('06/01/2021', 'MM-DD-YYYY')

It seems to work ok. Although I haven't had a chance to thoroughly audit the logic/results. There could be a mistake in there somewhere.

The performance isn't as bad as I thought it'd be:

• CLASSTRUCTURE has 2,600 rows
• CRONTASKHISTORY has 41,000
• The query takes 20 seconds to run, which isn't terrible. However, it will of course get worse when CRONTASKHISTORY grows (we've only been live in Prod for a month).

( , , and might be interested in this.)​​​
Original Message:
Sent: Thu June 10, 2021 12:46 AM
From: Bart Sikorski
Subject: Does the ROWSTAMP column represent a date?

You can get approximate date/time for a given ROWSTAMP. You can try below query in Oracle:

SELECT 'BEFORE' AS when, c2.endtime AS timestamp, c2.rowstamp  FROM crontaskhistory c2 WHERE c2.rowstamp = (SELECT MIN (TO_NUMBER (c0.rowstamp))                        FROM crontaskhistory c0                       WHERE TO_NUMBER (c0.rowstamp) > 1328121784)UNIONSELECT 'AFTER' AS when, c2.endtime AS timestamp, c2.rowstamp  FROM crontaskhistory c2 WHERE c2.rowstamp = (SELECT MAX (TO_NUMBER (c1.rowstamp))                        FROM crontaskhistory c1                       WHERE TO_NUMBER (c1.rowstamp) < 1328121784); --replace both hardcoded rowstamp values with your rowstamp value​

This method might be useful for a one-off check. It returns two rows with dates. The ROWSTAMP you are checking against was set sometime between those dates.
The precision of this method is between minutes and weeks or more. It depends on how often your crontasks write to CRONTASKHISTORY table.

------------------------------
Bart Sikorski

Original Message:
Sent: Thu May 27, 2021 06:28 PM
From: User1971
Subject: Does the ROWSTAMP column represent a date?

MAM 7.6.1.2 / Oracle 19c:

In Maximo tables, such as CLASSSTRUCTURE, it looks like we have a ROWSTAMP column.

Question:
Do the values in that column represent dates?
In other words, can the ROWSTAMP column be used as a poorman's CHANGEDATE column?

Thanks.

Good idea about referencing a specific cron task. I used the NOTFDELETE cron task, since it runs at midnight (midnight suggests that time of day isn't relevant) and has a max record count of 1000 (~three years of records should be good enough).

And I cleaned up the logic in the query:

select
    classstructureid,
    description,
    class_rowstamp,
    max(starttime) as estimated_changedate
from
    (
    select 
        a.classstructureid,
        a.description,
        a.rowstamp as class_rowstamp,
        b.rowstamp as cron_rowstamp,
        b.starttime
    from   
        (select classstructureid, description, cast(rowstamp as number) as rowstamp from maximo.classstructure) a
    cross join
        (select cast(rowstamp as number) as rowstamp, starttime from maximo.crontaskhistory where crontaskname = 'NOTFDELETE') b 
    where
        b.rowstamp < a.rowstamp
        or b.rowstamp is null
    )
group by
    classstructureid,
    description,
    class_rowstamp
--having
--    trunc(max(starttime)) = to_date('05/04/2021', 'MM-DD-YYYY')
--order by
--    estimated_changedate desc

.

How it works:
Get the closest cron task history record that was created before the classification was last updated. Use that cron task history record's date as the approximate date that the classification was last updated.