Re: SQL "SYS" files with deleted records

On 05-Apr-2012 12:16 , Mike Cunningham wrote:

Why would the SQL "SYS" files have deleted records? And LOTS of
deleted records

Member ... Records Deleted Records
SYSCOLUMNS 897969 406294
SYSCST
SYSCSTCOL
SYSCSTDEP
SYSINDEXES

And others also have lots of deleted records (as a % of the total
records)

Is it safe/advisable to do a RGZPFM on these?

If any of the actively maintained DBXREF files [over which SQL catalog VIEWs provide file\relation\column information] should be reorganized such that deleted records should be compressed or discarded perhaps to regain storage space from those unused rows, then instead of RGZPFM, best to issue the following request while in restricted state:
RCLSTG SELECT(*DBXREF)

Be aware those are Logical Files [LF, Logical SQL VIEW database file] for which no capability to Reorganize Physical File Member [RGZPFM] exists; any Reorg would need to be performed on the underlying Physical File [PF] data members. While it used to be possible and may even still be supported to do so, as I have warned in the past, best to exclude files QADB* in QSYS from any such activity. The potential negative impacts are not pleasant, and because RGZPFM rebuilds indexes serially instead of concurrently, actually exacerbates any negative symptoms. If actually done despite my warning, best to limit such requests to restricted state if possible, or otherwise, only in a dire emergency where RCLSTG SELECT(*DBXREF) can not be scheduled instead.

Consider that the deleted rows in QADBIFLD represent column\field names that once existed on the system, but those database *FILE objects have since been deleted. Thus if those since-gone files are expected to be restored or created again eventually, then the deleted rows are effectively innocuous; i.e. the creation of new files will add new column metadata as row data in the QADBIFLD physical file in QSYS. Most of the System Database Cross-Reference files QADB* in QSYS will be affected similarly by create\delete of database file objects on the system. Many of the SYS* logical VIEW files are going to be SQL Catalog VIEWs either specific to a library name or the database-wide VIEWs in the QSYS2 or SYSIBM libraries. Active and deleted row counts presented for each VIEW are merely a reflection of those counts from the "based on" physical file member(s); i.e. logical views of data would not typically be referenced, to review for deleted row counts or percentages.

Regards, Chuck