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).
(@Mark Robbins , @Steven Shull , and @Akshay T 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.
#Maximo
#AssetandFacilitiesManagement