Lots of IBM i Deleted Records?

Want to find the 100 Files with the most deleted records in them?

Ready to purge those Beauties?

For years us AS/400, iSeries and IBM i Techies have been using the Display File Description Command to create output files containing record counts for files.

The traditional process is something like this:

• DSPFD FILE(ALLUSR/ALL) TYPE(MBR) OUTPUT(OUTFILE) OUTFILE(yourlib/bigfatfile)
• Write a CL program to read the bigfatfile (aka SQL Table) and issue a RGZPFM based on whatever it finds in there with more than x% deleted records (aka SQL rows)

Let’s do this using the IBM i SQL SCRIPT tool:

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 100 ROWS ONLY

This will interrogate the IBM i System table using the “number_deleted_rows” functions and return us a nice spreadsheet with all the file info on the system.

Obviously this can be a monstrously huge list… so let’s tell it we just want to see the top 100 files with the most deleted records:

But Wait!!

This takes a long time to run. It took over 2 hours when I ran it on our development machine.

So let’s submit it to batch and pipe the output into a file that we can see when it’s completed. We can use the good old green screen RUNSQL command and tell it to create a table to contain the results.

In this example I am putting the results in my work library (NLITTEN/FNDDLTRCD):

SBMJOB CMD(RUNSQL SQL('create table NLITTEN/FNDDLTRCD 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 100 rows only) with data') COMMIT(*NONE) NAMING(*SYS))                                 
JOB(FNDDLTRCD)                                        
JOBQ(QPGMR)                                           

Submit this at the end of your day, and make sure it goes to a jobq that will not hold other jobs up. ( obviously changing the output library/filename #duh )

A few hours later you will have a file in your library that has a list of the top 100 fattest files on your machine.

You can reorganize them manually, or programmatically with ease.