Back in week 11 I wrote about how you can find the time for the last update of a DB2 table. You can also find this time using the table SYSIBM.SYSLGRNX starting with DB2 10. This table has always existed in DB2, but it has not been possible to access using SQL. In SYSIBM.SYSLGRNX you can also find the last time a table has been updated, well almost. Unfortunately it is hard to use the table using SQL as it is not suited to join with other tables in the DB2 catalog. Data are stored on tablespace level and the identification of the tablespace is done using the internal DBID and PSID columns, but as CHAR(2) FOR BIT DATA instead of SMALLINT. Thus it is far more easy to use the SYSTABLESPACESTATS table to find the last update time. However, if you are just as curious as I am, you simply need to access SYSLGRNX, because it contains information about previous updates.

The interesting columns in SYSLGRNX are LGRSLRSN and LGRELRSN. The column LGRELRSN specifies when the last logrecord has been written to the DB2 log and thus when the last update occurred. You might discover that LGRELRSN contains only 0-bits. In this case you can as an approximation use the value in LGRSLRSN. When LGRELRSN is zero the tablespace is updated frequently, and if LGRSLRSN is hours or even days older than the actual time then the tablespace is updated all day and night long.

To complicate matters further these two columns can exist in either a CHAR(6) FOR BIT DATA version or a CHAR(10) FOR BIT DATA version depending on whether DB2 still runs with so-called 6 bytes log RBA's or the newer 10 bytes log RBA's. This has an impact on how to write your SELECT:

SELECT TIMESTAMP(LGRSLRSN) STARTINTERVAL6BYTES
     , TIMESTAMP(LGRELRSN) ENDINTERVAL6BYTES
     , TIMESTAMP(SUBSTR(LGRSLRSN, 2, 8)) STARTINTERVAL10BYTES
     , TIMESTAMP(SUBSTR(LGRELRSN, 2, 8)) ENDINTERVAL10BYTES
FROM SYSIBM.SYSLGRNX
WHERE DBID = X'0048'
  AND PSID = X'003A'
ORDER BY LGRSLRSN DESC

The above SQL must be tailored. You need to remove either the 10BYTES or 6BYTES columns in the result set and you need to find the DBID and PSID for the table you are interested in. And I am not even sure it works if your DB2 system still uses 6 bytes log RBA's. I know it works for 10 bytes log RBA's as all DB2 systems in my installation are using. When the SQL works the top row returned contains start and end time of the last interval of updates, the next row contains the next last interval of updates and so forth.

Be aware that SYSLGRNX are cleaned up every time you run a so-called MODIFY utility. If your table has not been updated for a long time then all rows in SYSLGRNX may be gone. Execution of a REORG DISCARD LOG(NO) or a LOAD LOG(NO) does not update SYSLGRNX even though rows may be removed or inserted in the table by these utilities.