Pages

Wednesday, September 21, 2022

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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.