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:
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.