When IBM i RGZPFM Doesn’t Want To Rebuild Access Paths
July 30, 2014 Hey, Joe
|
I’m trying to run this Reorganize Physical File (RGZPFM) command to reorganize one of my files while it’s active: RGZPFM FILE(lib_name/file_name) MBR(member_name) RBDACCPTH(*OPTIMIZE) ALWCANCEL(*YES) LOCK(*SHRUPD) But I keep getting this CPF3135 error: CPF3135 - Access path for member &2 already in use. This is not what I anticipated. Am I doing something wrong? –Alan My guess is that your RGZPFM command will complete correctly if you run the command using these parameters: RGZPFM FILE(lib_name/file_name) MBR(member_name) RBDACCPTH(*NO) ALWCANCEL(*YES) LOCK(*SHRUPD) The difference between this command and your command is that the Rebuild Access Paths (RBDACCPTH) parameter in this command is set to *NO, while your RBDACCPTH parameter is set to *OPTIMIZE. To explain why this works, here are the three values that RBDACCPTH can be set to and how they affect RGZPFM statements. 1. If you set RBDACCPTH to *YES, the operating system will rebuild your file’s access path synchronously at the end of your RGZPFM operation. This requires an exclusive file lock for the access path to be rebuilt this way. There are several sources that say setting RBDACCPTH to *YES can result in the reorganization operation failing with a CPF3135 error, if someone else is using the file. 2. If you set RBDACCPTH to *NO, the file’s access path will be automatically maintained while the RGZPFM operation is running. This should circumvent the CPF3135 message and allow the access path to be rebuilt as RGZPFM is running. Setting RBDACCPTH to *NO also requires that you set your Allow Cancel parameter to *YES, which is no big deal in your case. 3. If you set RBDACCPTH to *OPTIMIZE as you did, the RGZPFM statement itself will set its RBCACCPTH parameter to either *YES or *NO, depending on which method will result in the access paths being rebuilt the fastest. I’m guessing that when you selected RBDACCPTH to *OPTIMIZE, the OS decided to rebuild the file’s access path synchronously at the end of the RGZPFM (RBDACCPTH = *YES) and that’s what caused the CPF3135 message to appear and your reorg to fail. Based on this information, I think there’s a good chance that changing RBDACCPTH to *NO will enable you to run the statement, reorganize your file, and rebuild your access paths while active. Unfortunately, reorganizing your file while active doesn’t necessarily get you out of the woods, in terms of shrinking your file size. This RBDACCPTH = *NO parameter may allow you to reorganize your file for performance, but it may not remove the deleted records from your file. Here’s some additional info on how using RBDACCPTH works that affects whether deleted records are removed from a reorganized file when RBDACCPTH = *NO. A RGZPFM command goes through these four phases when reorganizing a file:
It’s the fourth phase that you need to be aware of. If you’re running a reorg while active, the RGZPFM can only deallocate the deleted records space if it can get an exclusive lock on the file. What often happens is that RGZPFM will be able to move all the file’s deleted records to the deleted records space, rebuild the access path, and then stop because it can’t get an exclusive lock on the physical file (something else is using the file). When that happens, you may see the following CPF5729 informational message appear for your job. CPF5729 - Not able to allocate object &1 If you’re not able to get an exclusive lock on your physical file/member, then the system will still complete the first three phases. The RGZPFM command will end cleanly but it won’t be able to remove (truncate) the deleted records space from the file. It will only be able to do that if you run your RGZPFM statement when the file is not in use. The file will shrink because of compression and your deleted records will move to the end of the file, but you may or may not be able to remove your deleted records until you run the RGZPFM command again, when the file is not in use by other processes. It pays to be aware of this limitation. Please let me know if this information works for you. HTH –Joe Joe Hertvik is an IBM i subject matter expert (SME) and the owner of Hertvik Business Services, a service company that provides written marketing content and presentation services for the computer industry, including white papers, case studies, and other marketing material. Email Joe for a free quote for any upcoming projects. He also runs a data center for two companies outside Chicago, featuring multiple IBM i ERP systems. Joe is a contributing editor for IT Jungle and has written the Admin Alert column since 2002. Check out his blog where he features practical information for tech users at joehertvik.com. RELATED STORIES Admin Alert: Reorganizing IBM i Files To Improve Disk Performance, Part 2 Admin Alert: Reorganizing IBM i Files To Improve Disk Performance, Part 1
|
