IBM i Global

IBM i 

A space for professionals working with IBM’s integrated OS for Power systems to exchange ideas, ask questions, and share expertise on topics like RPG and COBOL development, application modernization, open source integration, system administration, and business continuity.


#Power


#IBMi
 View Only
Expand all | Collapse all

Logical file showing offline size too much greater than actual size

  • 1.  Logical file showing offline size too much greater than actual size

    Posted Thu January 18, 2024 12:11 PM

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


  • 2.  RE: Logical file showing offline size too much greater than actual size

    Posted Thu January 18, 2024 12:20 PM

    Hello,

    We experienced a similar issue that we resolved with an RGZPFM. The indexes had a significant volume because they were pointing to a physical file that had undergone a substantial number of DELETE operations within an RPG program.



    ------------------------------
    Laurent ROUILLOT
    ------------------------------



  • 3.  RE: Logical file showing offline size too much greater than actual size

    Posted Thu January 18, 2024 01:12 PM

    Thank you for the response Laurent !

    We performed RGZPFM as well last night. It is still showing the same. 



    ------------------------------
    Ujwala Kavathekar
    ------------------------------



  • 4.  RE: Logical file showing offline size too much greater than actual size

    Posted Fri January 19, 2024 04:23 AM

    Hi,

    The OFFLINE size of an object is the size of the object when it was last saved.

    It is described in the help text for the offline storage line on the DSPOBJD display.

    After your next backup, the numbers will have changed.

    Best regards,

    Christian



    ------------------------------
    Christian Jorgensen
    IT System Administrator
    Network of Music Partners A/S
    ------------------------------



  • 5.  RE: Logical file showing offline size too much greater than actual size

    Posted Fri January 19, 2024 05:04 AM

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



  • 6.  RE: Logical file showing offline size too much greater than actual size

    Posted Wed January 24, 2024 12:06 PM

    Thank you so much!



    ------------------------------
    Ujwala Kavathekar
    ------------------------------



  • 7.  RE: Logical file showing offline size too much greater than actual size

    Posted Thu January 25, 2024 10:49 AM
    Edited by Steven Riedmueller Thu January 25, 2024 11:26 AM

    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 DESC5 DESC
        FETCH FIRST 50 ROWS ONLY;



    ------------------------------
    Steven Riedmueller
    Certified IBM i Admin
    Speaker, Mentor, and Advocate
    ------------------------------



  • 8.  RE: Logical file showing offline size too much greater than actual size

    Posted Fri January 26, 2024 07:34 AM
    Edited by ac Fri January 26, 2024 07:35 AM

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



  • 9.  RE: Logical file showing offline size too much greater than actual size

    Posted Fri January 26, 2024 08:37 AM

    Fantastic.  I love the "multiply by 1.0" to force the precision.  I didn't know about that.  I would have been messing around with CAST and INTERPRET.  Thanks for sharing, I learned something!

    I wish I could get the OK to setup a daily reorg like that.  We have processes running around the clock including web traffic.  Setting up a nightly reorg is an amazing luxury!



    ------------------------------
    Steven Riedmueller
    Certified IBM i Admin
    Speaker, Mentor, and Advocate
    ------------------------------



  • 10.  RE: Logical file showing offline size too much greater than actual size

    Posted Fri January 26, 2024 09:34 PM

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



  • 11.  RE: Logical file showing offline size too much greater than actual size

    Posted Sat January 27, 2024 10:08 AM
    Edited by Steven Riedmueller Sat January 27, 2024 10:12 AM

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



  • 12.  RE: Logical file showing offline size too much greater than actual size

    Posted Sat January 27, 2024 08:02 PM

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



  • 13.  RE: Logical file showing offline size too much greater than actual size

    Posted Mon January 29, 2024 04:51 AM
    Edited by ac Mon January 29, 2024 04:54 AM

    Yes, a "side effect producing" scalar function like QCMDEXC must be used of course with a "grain of salt" : )

    It is a technique super useful for especially one shot script doing mass actions, manual supervised things, batch things like mass copies of things, spools etc.

    it doesn't automatically lends itself to super robust online production software because of the way it traps exceptions and the control it gives... but of course horses for courses.... anyone one can register a new user scalar function to do what required and use it as it wishes with result sets..

    BTW nowadays if you use journaling you can do a sort of RGZ "while active", it does comes with some caveat though...



    ------------------------------
    --ft
    ------------------------------



  • 14.  RE: Logical file showing offline size too much greater than actual size

    Posted Mon January 29, 2024 06:50 AM
    Edited by Simon Hutchinson Mon January 29, 2024 07:02 AM

    @Satid Singkorapoom thank you for the kind words.

    Blogger dropped its subscription by email some time ago. People who want the same type of service have switched to Feedly.



    ------------------------------
    Simon Hutchinson

    https://www.rpgpgm.com
    ------------------------------



  • 15.  RE: Logical file showing offline size too much greater than actual size

    Posted Mon January 29, 2024 06:58 AM
    Edited by Simon Hutchinson Mon January 29, 2024 07:02 AM

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



  • 16.  RE: Logical file showing offline size too much greater than actual size

    Posted Mon January 29, 2024 08:38 AM

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



  • 17.  RE: Logical file showing offline size too much greater than actual size

    Posted Mon January 29, 2024 12:30 PM
    Edited by ac Mon January 29, 2024 12:30 PM

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



  • 18.  RE: Logical file showing offline size too much greater than actual size

    Posted Mon January 29, 2024 12:49 PM

    What I said was in general terms.

    If you come to my presentation during COMMON Focus, February 13,  I will show how do this in far more detail than what I said before. I build a DDL View to have all the information I need to do this, including which files & libraries to ignore.

    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.



    ------------------------------
    Simon Hutchinson
    https://www.rpgpgm.com
    ------------------------------



  • 19.  RE: Logical file showing offline size too much greater than actual size

    Posted Tue January 30, 2024 09:06 AM

    As others have already replied on QCMDEXC, I'll add that this entire SELECT is likely not intended for all IBM i operations. You can and should use it for one-off instances or small shops where you have total control. But in general, a simply RPG IV program with embedded SQL that runs the SELECT and then evokes the RGZPFM command via a CALL to either a CL program or QCMDEXC API is likely a good long-term solution. 

    But again, for a one-off, this SELECT with an embedded QCMDEXC function is pretty awesome. 

    Another option, besides the RPG IV approach, is to use some other SQL-friendly language, like SQL iQuery Script, or SQL/PL. That way you can craft the logic the way you want it--reading one row at a time and then after additional conditional logic, perform the RGZPFM conditionally, trap any errors and respond accordingly. 



    ------------------------------
    Robert Cozzi
    ------------------------------



  • 20.  RE: Logical file showing offline size too much greater than actual size

    Posted Tue January 30, 2024 11:42 AM

    Expanding on the theme of "one-off utility SQL queries", where one find some calculation repetition it is sometimes useful to introduce symbols to avoid errors, can be useful for complex queries and the intent of the filter is maybe clearer..

    I use a CROSS JOIN LATERAL.

    I don't know if there is a different way (keeping the same unmovable ; ) "single SQL statement" spirit), maybe someone can chime in.

    So the query becomes (one can add QCMDEXC call to COMMAND field)...

    SELECT FILE,    
           DATA_SIZE,                                                           
           RATIO,                                                  
           'RGZPFM ' || FILE AS COMMAND
           FROM QSYS2.SYSTABLESTAT
           CROSS JOIN LATERAL
           (VALUES (TRIM(SYSTEM_TABLE_SCHEMA) || '/' || TRIM(SYSTEM_TABLE_NAME),
                   1.0*NUMBER_DELETED_ROWS / (NUMBER_ROWS + NUMBER_DELETED_ROWS))) as
                   vars(FILE, RATIO)
        WHERE
        NUMBER_ROWS > 0 AND NUMBER_DELETED_ROWS > 0                       
        AND RATIO > 0.20
        AND SYSTEM_TABLE_SCHEMA IN ('LIB2', 'LIB1')                                
        ORDER BY 3 DESC, 2 DESC                                                 
        FETCH FIRST 15 ROWS ONLY



    ------------------------------
    --ft
    ------------------------------