Determining which objects have changed since last save

There is an old library, I am going to call it OLDLIB1, that is still in everyone's library list. I needed to determine if there are files in this library that are still changed. I have used the word "changed" rather than "used" as they mean two different things. A file is used when it is opened in a program, the data within might not be changed. A file is changed when a record is added to the file, modified, or deleted. At present the entire library is saved using the SAVLIB command once a week. But if files are still being used we need to do a more often backup.

I can get to this information using the Display Object Description command, DSPOBJD, but with that I have to build an output file and then search the output file for the information I need.

Fortunately the SQL View SYSTABLESTAT contains the information too in the following columns:

1. LAST_CHANGE_TIMESTAMP
2. LAST_USED_TIMESTAMP
3. LAST_SAVE_TIMESTAMP

I think it is obvious from the columns names the information they contain.

Let me show some of the data from OLDLIB1 to illustrate what I mean. Here is the SQL statement I used to show those columns for the first five rows returned in the results.

01 SELECT SYSTEM_TABLE_NAME AS "File", 02 LAST_CHANGE_TIMESTAMP,LAST_SAVE_TIMESTAMP,LAST_USED_TIMESTAMP 03 FROM QSYS2.SYSTABLESTAT 04 WHERE SYSTEM_TABLE_SCHEMA = 'OLDLIB1' 05 LIMIT 5 ;

Line 1: I need the file name, so I know which files have changed, or not.

Line 2: The three timestamp columns I mentioned above.

Line 5: I only want five rows from the results.

The results are:

File LAST_CHANGE_TIMESTAMP LAST_SAVE_TIMESTAMP LAST_USED_TIMESTAMP ------ ---------------------- ---------------------- ---------------------- FILE1 2022-06-15 11:18:21... 2022-08-20 22:32:15... 2022-08-23 00:00:00... FILE2 2022-06-30 21:42:53... 2022-08-20 22:32:15... 2022-08-17 00:00:00... FILE3 2022-08-23 00:34:32... 2022-08-20 22:32:15... 2022-08-23 00:00:00... FILE4 2022-03-25 15:19:46... 2022-08-20 22:32:15... 2022-08-23 00:00:00... FILE5 2022-06-18 23:30:30... 2022-08-20 22:32:15... 2022-08-23 00:00:00...

The LAST_SAVE_TIMESTAMP shows me that the library was last saved on August 20.

I can see that all of the file, except FILE2, have been used after the last save by looking at the LAST_USED_TIMESTAMP. But only one of the files, FILE3 has been changed since the save.

How could I identify the other files that have been changed since the last save?

I want to create a SQL statement that I can use with other libraries, and when SAVOBJ or SAVCHGOBJ commands had been used. Firstly, I would need to establish the date the last library saved occurred. If the library is being saved with SAVLIB or as part of a system save then the date of that save must be the lowest value in the LAST_SAVE_TIMESTAMP. I can retrieve that using the MIN scalar function in the following statement:

01 SELECT MIN(LAST_SAVE_TIMESTAMP) AS "Min save timestamp" 02 FROM QSYS2.SYSTABLESTAT 03 WHERE SYSTEM_TABLE_SCHEMA = 'OLDLIB1'

This returns to me:

Min save timestamp -------------------------- 2022-08-20 22:32:15.000000

I can then build this SQL statement:

01 SELECT SYSTEM_TABLE_NAME,LAST_CHANGE_TIMESTAMP 02 FROM QSYS2.SYSTABLESTAT 03 WHERE SYSTEM_TABLE_SCHEMA = 'OLDLIB1' 04 AND LAST_CHANGE_TIMESTAMP > (SELECT MIN(LAST_SAVE_TIMESTAMP) 05 FROM QSYS2.SYSTABLESTAT 06 WHERE SYSTEM_TABLE_SCHEMA = 'OLDLIB1')

Lines 4 – 6: To get the last save timestamp of the last library save I use a subselect that returns the minimum timestamp value in the LAST_SAVE_TIMESTAMP and compares it to the LAST_CHANGE_TIMESTAMP. If the last change timestamp is greater than that the file has changed since the last library save.

I get two rows returned:

SYSTEM_TABLE_NAME LAST_CHANGE_TIMESTAMP ----------------- -------------------------- FILE3 2022-08-23 00:34:32.000000 FILE75 2022-08-22 13:36:06.000000

Both have the last change timestamp greater than the last time the library was saved.

To be sure I have captured all of the files that have changed I would want to run this just before the next time the library is saved. And I would want to run this after a week and a month ends. With that list I could just save the changed files, rather than the entire library.

I know I could use SAVCHGOBJ, but after testing I have found that it is very slow compared to just saving the changed objects I have identified with a SAVOBJ command.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.