Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only
Expand all | Collapse all

Does the ROWSTAMP column represent a date?

Bart Sikorski

Bart SikorskiThu June 10, 2021 10:27 AM

  • 1.  Does the ROWSTAMP column represent a date?

    Posted Thu May 27, 2021 06:28 PM
    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.
    #AssetandFacilitiesManagement
    #Maximo


  • 2.  RE: Does the ROWSTAMP column represent a date?

    Posted Thu May 27, 2021 08:10 PM
    Edited by System Admin Wed March 22, 2023 11:54 AM
    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
    ------------------------------



  • 3.  RE: Does the ROWSTAMP column represent a date?

    Posted Tue June 01, 2021 02:04 PM
    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
    ------------------------------



  • 4.  RE: Does the ROWSTAMP column represent a date?

    Posted Fri May 28, 2021 07:07 AM
    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/
    ------------------------------



  • 5.  RE: Does the ROWSTAMP column represent a date?

    Posted Fri May 28, 2021 09:04 AM
    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
    ------------------------------



  • 6.  RE: Does the ROWSTAMP column represent a date?

    Posted Wed June 02, 2021 09:39 PM
    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
    ------------------------------



  • 7.  RE: Does the ROWSTAMP column represent a date?

    Posted Thu June 03, 2021 10:03 AM
    Edited by System Admin Wed March 22, 2023 11:48 AM
    @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? ​​​​
    #Maximo
    #AssetandFacilitiesManagement


  • 8.  RE: Does the ROWSTAMP column represent a date?

    Posted Thu June 03, 2021 10:20 AM
    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.


  • 9.  RE: Does the ROWSTAMP column represent a date?

    Posted Thu June 03, 2021 05:02 PM
    I found this MAXSEQ sequence :



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



  • 10.  RE: Does the ROWSTAMP column represent a date?

    Posted Thu June 10, 2021 10:28 AM
    Edited by System Admin Wed March 22, 2023 11:46 AM


  • 11.  RE: Does the ROWSTAMP column represent a date?

    Posted Thu June 10, 2021 10:27 AM
    Edited by System Admin Wed March 22, 2023 11:47 AM


  • 12.  RE: Does the ROWSTAMP column represent a date?

    Posted Thu June 10, 2021 10:27 AM
    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
    ------------------------------



  • 13.  RE: Does the ROWSTAMP column represent a date?

    Posted Thu June 10, 2021 04:07 PM
    Edited by System Admin Wed March 22, 2023 11:46 AM
    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).

    #Maximo
    #AssetandFacilitiesManagement


  • 14.  RE: Does the ROWSTAMP column represent a date?

    Posted Thu June 10, 2021 05:02 PM
    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/
    ------------------------------



  • 15.  RE: Does the ROWSTAMP column represent a date?

    Posted Sat June 12, 2021 07:29 PM
    Edited by System Admin Wed March 22, 2023 11:54 AM
    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.
    #Maximo
    #AssetandFacilitiesManagement


  • 16.  RE: Does the ROWSTAMP column represent a date?

    Posted Sat June 12, 2021 09:42 PM
    Edited by System Admin Wed March 22, 2023 11:49 AM
    I suppose I could create a custom Maximo table that had a ROWSTAMP column and a DATE column. And then find a way to add a record each day (or hour, etc.).

    Each record would have a ROWSTAMP and a DATE. I could use those records to generate the CHANGEDATE in a classification query (using the CLASSSTRUCTURE table's ROWSTAMP).

    That might be a bit safer than using the cron task history. At least I'd have total control over the records, how often they're inserted, and how often they're deleted.
    #AssetandFacilitiesManagement
    #Maximo