Discovering the number of deleted records in a file

In last week's post, here, I gave an example of how I determine the 250 biggest objects on my IBM i server. I created the information about the objects by using the Display Object Description command, DSPOBJD, and mentioned if anyone knew of a better way to get the same information to contact me.

Bill Gravelle did just that suggesting that I look for a table in the QSYS2 library, see here. After poking around I did find a promising table, SYSTABLESTAT, which contains information for every user table/file. This would not have identified all of the biggest objects, as 28 of them were Journal Receivers, *JRNRCV. But the table does contain information that is useful, I am not going to list all the columns/fields in this table as IBM does such a good job on their Knowledge Center web site here.

As we have been talking about releasing wasted disk space the column that grabbed my attention was NUMBER_DELETED_ROWS. Most of the files on the IBM i I use at work do not reuse delete records, therefore, of them have increasing numbers of deleted records that are just wasting disk space. If I could identify the worst offenders I could purge these deleted records and release that disk space. With this in mind I am only interested in the following columns:

Column name	Description
DATA_SIZE	Size of table/file in bytes
SYSTEM_TABLE_NAME	Table/file name
SYSTEM_TABLE_SCHEMA	Library that contains table/file
NUMBER_ROWS	Number of valid rows/records
NUMNER_DELETED_ROWS	Number of deleted rows/records in the file

I can put that all in a SQL in a CL program like this:

01 PGM 02 RUNSQL SQL('CREATE TABLE MYLIB.OUTFILE AS + (SELECT DATA_SIZE,SYSTEM_TABLE_NAME, + SYSTEM_TABLE_SCHEMA,NUMBER_ROWS, + NUMBER_DELETED_ROWS + FROM QSYS2.SYSTABLESTAT + ORDER BY NUMBER_DELETED_ROWS DESC + FETCH FIRST 250 ROWS ONLY) + WITH DATA') + COMMIT(*NONE) NAMING(*SQL) 03 ENDPGM

If you are not familiar with CREATE TABLE in SQL you ought to read the post Creating SQL table on the fly.

I have decided to use the SQL naming convention, NAMING(*SQL), just because I can and to show you that it is not much different from the standard IBM i convention. In the SELECT statement I have selected the columns mentioned in the table above, and sorted (order by) by the number of deleted rows in descending order, i.e. largest first. Then the first 250 rows will be written to by output file, MYLIB.OUTFILE. This will include all the tables/files in IBM libraries too, e.g. QSYS.

When run this took about five times as long as program in the previous post that used DSPOBJD.

The output looks like this:

DATA_SIZE SYSTEM_TABLE_NAME SYSTEM_TABLE_SCHEMA NUMBER_ROWS NUMBER_DELETED_ROWS 2116046848 FILE1 LIB1 75 5,351,845 1981816832 FILE2 LIB1 11,781 4,988,364 1361072128 FILE3 LIB1 890 3,504,467 421531648 FILE4 LIB1 19,130 3,438,994 304144384 FILE5 LIB2 855,966 3,423,437

Only the names have been changed to protect the innocent files and libraries.

There is a mutlitude of other information in this table that may prove useful in the future, such as:

• LOGICAL_READS - Number of logical read operations since the last IPL.
• PHYSICAL_READS - Number of physical read operations since the last IPL.
• SEQUENTIAL_READS - Number of sequential read operations since the last IPL.
• RANDOM_READS - Number of random read operations since the last IPL.

 

You can learn more about the SYSCL's SYSTABLESTAT table on the IBM website here.

 

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