Get sequence value date?

Question

I have an OOB system that has hundreds of tables. Each table has a ROWSTAMP column (integer) that gets populated by a trigger each time a record is created/updated.

All the tables & triggers in the db use the same sequence. Each time a record is updated anywhere in the db, that one sequence is used to get the next available integer. (I suppose I could consider the sequence to be a sort of global sequence).

IBM: Usage of ROWSTAMP column in Maximo tables

It's unfortunate that those ROWSTAMP columns are integers, and not dates. What I really need in the tables are CHANGEDATE columns. Dates would be useful for data entry troubleshooting, reporting, and analysis in general. But I can't really justify customizing hundreds of tables with date columns and adding hundreds of triggers in the OOB system. The customized tables wouldn't be supported by IBM and it wouldn't be worth the effort/added complexity.

---

I feel like those ROWSTAMP columns are so close to being useful to me. I can't help but wonder:

Is there a way to retrieve the date that those ROWSTAMP sequence values were generated?

For example, and I doubt this is possible, could I add a DATE column to the sequence, and then join from my table to the sequence to get the date? Or could I use some sort of logging mechanism on the sequence to put rows (with dates) in a table whenever the next sequence value is used?

Answer 1

There is no way to add a date to a sequence, or to know inherently when a sequence value was generated. You would need to add a CHANGEDATE column to each table and have the trigger update that column for each modified record, or create an audit table with ROWSTAMP and CHANGEDATE and insert into that table in addition to every other change that the triggers are firing on. – pmdba