What I said was in general terms.
Triggers do present an issue. If you CLRPFM an empty file (no active records) I don't think a delete trigger would be fired as you have not deleted any records. You'll have to test that.
Original Message:
Sent: Mon January 29, 2024 12:29 PM
From: ace ace
Subject: Logical file showing offline size too much greater than actual size
Yes, like anything, there can be many ways to achieve the objective, taking in account context specifics.
But, RGZPFM and CLRPFM have different semantics and contracts with the user, in intent too, one specific to database internal organization, the other to clear the content (with added side effects in file internal organization).
For example, in some files one can have DELETE trigger attached, and CLRPFM won't allow the operation as far as I know. Perhaps better the SQL TRUNCATE in some cases at this point.
Checking for active records before issuing a CLR can be risky, the situation can change in busy systems, of course depends if it is a one shot overseen manual operation or not.
------------------------------
--ft
Original Message:
Sent: Mon January 29, 2024 08:38 AM
From: Steven Riedmueller
Subject: Logical file showing offline size too much greater than actual size
Another way of taking a different action dependent on the number of active records in each file would be to use a CASE statement. One could CASE on the number of active records, and when that number is zero then QCMDEXC(CLRPFM...) and otherwise QCMDEXC(RGZPFM...).
Still, wrapping the whole thing up in a CL might give finer control through MONMSG.
Good discussion to get the creative juices flowing on Monday morning!
------------------------------
Steven Riedmueller
Certified IBM i Admin
Speaker, Mentor, and Advocate
Original Message:
Sent: Mon January 29, 2024 06:58 AM
From: Simon Hutchinson
Subject: Logical file showing offline size too much greater than actual size
Having gone through an old partition doing this my "2 cents worth" are.
While you can do it using the QCMDEXC scalar function I found it better not to. My CL program used SQL to created an output file of the files to reorg, and I RCVF the records from that. It allowed me to monitor for the errors better than I could in SQL.
If the file has deleted records and zero active records don't RGZPFM, CLRPFM as it is many times faster.
And change the files to reuse deleted records. If you don't you'll be doing this again and again.
Watch out for any record address type files. If you RGZPFM those you are going to make them pretty much unusable.
------------------------------
Simon Hutchinson
https://www.rpgpgm.com
Original Message:
Sent: Sat January 27, 2024 08:01 PM
From: Satid Singkorapoom
Subject: Logical file showing offline size too much greater than actual size
Dear Steven
Thanks for your time providing me this useful new piece of knowledge that I can make use of.
Mr. Hutchinson's web site has been an abundant source of such knowledge for me. I used to try to see if his web site provides a subscription service in which I can be notified by automatic e-mail whenever he posts new articles but I could not find it and this causes me to miss a number of such enriching articles. If you happen to know how to get such automatic notification, I would be doubly grateful.
------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
Original Message:
Sent: Sat January 27, 2024 10:07 AM
From: Steven Riedmueller
Subject: Logical file showing offline size too much greater than actual size
Hi Satid,
You're in for a treat! This is the QCMDEXC() scalar function. Yes, it does actually execute the command on the server. You can build commands dynamically as Ace Ace has done, and those commands will be executed server-side. You can read about it:
IBM's doc
Simon's doc
Simon's example
It's truly awesome. The returned value will be 1 for a successfully executed command, and -1 for an error. This is super powerful. I have a gist example for it as well where I'm simply using it to execute a SNDSMTPEMM command, but you can use it to execute any command:
sriedmue79 gist
Of course it's as dangerous as it is powerful! I like to keep the QCMDEXC commented out as shown below until I'm absolutely sure that I'm building the commands properly and against the right objects:
SELECT /*QSYS2.QCMDEXC*/('RGZPFM ' || TRIM(SYSTEM_TABLE_SCHEMA)
|| '/' || TRIM(SYSTEM_TABLE_NAME) ) AS RGZRESULT
Also note that you'll only get the resulting 1 or -1 value. If you don't also SELECT some kind of identifying information, you might see a -1 but not be able to correlate that with the file that didn't get reorged, or whatever the command was intended to do. So make sure to include some other details. The simplest is to build the command twice, once to display it and once to execute it:
SELECT 'RGZPFM ' || TRIM(SYSTEM_TABLE_SCHEMA) || '/' ||
TRIM(SYSTEM_TABLE_NAME) AS RGZCOMMAND, --this is the command
QSYS2.QCMDEXC('RGZPFM ' || TRIM(SYSTEM_TABLE_SCHEMA)
|| '/' || TRIM(SYSTEM_TABLE_NAME) ) AS RGZRESULT --this is the result
------------------------------
Steven Riedmueller
Certified IBM i Admin
Speaker, Mentor, and Advocate
Original Message:
Sent: Fri January 26, 2024 09:34 PM
From: Satid Singkorapoom
Subject: Logical file showing offline size too much greater than actual size
Dear Ace Ace and Steven
I would very much appreciate if you or any one else would be so kind as to help educate me more on the SELECT part of QSYS2.QCMDEXC('RGZPFM ' || TRIM(SYSTEM_TABLE_SCHEMA) || '/' || TRIM(SYSTEM_TABLE_NAME) ) AS RGZRESULT .
Does this part actually run RGZPFM for each of the 15 tables in the result set?
How does the value of this RGZRESULT show in the result set - a zero or one or what value?
As this looks wonderfully intriguing to me, is there any URL where I can read more on referring to QCMDEXC in SELECT ?
Thanks in advance.
------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
Original Message:
Sent: Fri January 26, 2024 07:34 AM
From: ace ace
Subject: Logical file showing offline size too much greater than actual size
Thanks for the query idea, I've adapted it in a simple sql lanched via STRQMQRY job during night to reorganize a tranche of files automatically without impacting too much
SELECT TRIM(SYSTEM_TABLE_SCHEMA) || '/' || TRIM(SYSTEM_TABLE_NAME) FILE,
DATA_SIZE,
100 * NUMBER_DELETED_ROWS
/
(NUMBER_ROWS + NUMBER_DELETED_ROWS)
AS PERCENT_DELETED,
QSYS2.QCMDEXC('RGZPFM ' ||
TRIM(SYSTEM_TABLE_SCHEMA) ||
'/' ||
TRIM(SYSTEM_TABLE_NAME) ) AS RGZRESULT
FROM QSYS2.SYSTABLESTAT
WHERE NUMBER_ROWS > 0 AND NUMBER_DELETED_ROWS > 0
AND 1.0*NUMBER_DELETED_ROWS/(NUMBER_ROWS + NUMBER_DELETED_ROWS) > 0.20
AND TABLE_SCHEMA IN ('lib1', 'lib2')
ORDER BY 3 DESC, 2 DESC
FETCH FIRST 15 ROWS ONLY
------------------------------
--ft
Original Message:
Sent: Thu January 25, 2024 10:48 AM
From: Steven Riedmueller
Subject: Logical file showing offline size too much greater than actual size
Below is an adapted version of Satid's super-helpful query. This version orders by the percentage of deleted records, rather than the sheer number of deleted records. I think both version have their place.
edit: my calculation was backwards - fixed it here
--Description: files with a high PERCENTAGE of deleted records
SELECT TABLE_SCHEMA,
TABLE_NAME,
NUMBER_ROWS,
NUMBER_DELETED_ROWS,
DATA_SIZE,
100 * NUMBER_DELETED_ROWS / (NUMBER_ROWS + NUMBER_DELETED_ROWS) AS PERCENT_DELETED
FROM QSYS2.SYSTABLESTAT
WHERE NUMBER_ROWS > 0 AND NUMBER_DELETED_ROWS > 0 --avoid division by zero
ORDER BY 6 DESC, 5 DESC
FETCH FIRST 50 ROWS ONLY;
------------------------------
Steven Riedmueller
Certified IBM i Admin
Speaker, Mentor, and Advocate
Original Message:
Sent: Wed January 24, 2024 12:05 PM
From: Ujwala Kavathekar
Subject: Logical file showing offline size too much greater than actual size
Thank you so much!
------------------------------
Ujwala Kavathekar
Original Message:
Sent: Fri January 19, 2024 05:03 AM
From: Satid Singkorapoom
Subject: Logical file showing offline size too much greater than actual size
Dear Ujwala
You should run DB2i metadata query to see how many large tables in your system has a lot of deleted rows remaining in them so that it guides you which ones you run RGZPFM to reclaim more disk space back. The metadata query looks like this :
SELECT TABLE_SCHEMA, TABLE_NAME, NUMBER_ROWS, NUMBER_DELETED_ROWS, DATA_SIZE from QSYS2.SYSTABLESTAT ORDER BY NUMBER_DELETED_ROWS FETCH FIRST 50 ROWS ONLY ;
You can run it from Run SQL Scripts session if you know your server is fast. If your server is slow and you have a lot of DB objects, you may want to wrap the statement above in
CREATE TABLE LIBX.DELROWREPORT AS ( ....... ) WITH DATA ;
and save the script text file in an IFS folder and run the script from RUNSQLSTM command submitted as a batch job.
A sample report looks like this :

When looking at the report, focus on large tables first (You may want to use ORDER BY DATA_SIZE as well in another report. DATA_SIZE is shown in Bytes).
------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
Original Message:
Sent: Thu January 18, 2024 12:11 PM
From: Ujwala Kavathekar
Subject: Logical file showing offline size too much greater than actual size
Hello,
We are working on freeing up some space on system and ASP is now 85 %. When we checked the largest objects on system, it is showing 2 Logical file's offline size is about 2199022997504 and actual size is 151552, as physical files contains only 90000 records.
We are not sure why it is showing offline size high and how to resolve the issue. Please share some feedback.
Thank you!
------------------------------
Ujwala Kavathekar
------------------------------