RGZPFM - What are you AS400 admins doing for your DASD?

We've had our AS400 for 6 years now and we've never performed an RGZPFM on our production database.

I'm under the impression that the running RGZPFM against my production DB library with no other parameters will remove deleted records from all members and place the members/files into contiguous sectors of my disks (thus making sequential I/O more likely). Is this correct?

Now, I'm curious about the keyed file parameter. 90% of my DBA activities are spent in the MS SQL Server arena so it sounds as if AS400 tables/files are by default heaps...and that the "keyed" parameter imitates clustered indexes in the MS SQL world. Is this about right?

If so, there are definite benefits to being able to specify the keyed file. Although, if I really had to speed up sort orders, I might just incorporate covering indexes sorted appropriately. At the same time, I'm most interested in the most current records being close together to each other on disk - which is basically happening with the heap according to batch insert order.

Anyway, with all my ramblings what I'm working toward here is the question:

"What is everyone else out there doing to reduce fragmentation, reduce non-sequential I/O, and protect the general health of their DASD?"

Thanks in advance.

Ryan

I also meant to ask...

If RGZPFM cannot be run against a library and/or it does not move the entire file into contiguous sectors, could I cheat by ending all subsystem (prevent user data entry), then perform a CPYLIB against my production database to say, PROD_COPY. Then, delete the PRODDTA library and recreate it using CPYLIB again with PROD_COPY as the source.

It seems this would certainly help with the contiguous sequential IO goal. Thoughts?

TIA

Ryan

Ryan,

Exactly how your data is distributed across your DASD is managed using STRASPBAL. RGZPFM removes deleted records from one member of a physical file and will optionally sequence the records if the KEYFILE parameter is specified. However, it will not move your data into contiguous sectors.

You can use STRASPBAL to balance your data across your disks according to usage, so that the high-use and low-use data on each unit in the ASP is redistributed to balance the arm utilization of each unit within the specified ASP. You can also balance putting your high use data on faster DASD and low use data on slower DASD (assuming you have a mix of fast and slow DASD). You can find more info here: http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/cl/straspba.htm

I'm sure you understand that using RGZPFM with the KEYFILE parameter is not likely to improve performance, as JDE rarely reads data in sequential order.

Your stated goal of "contiguous sequential IO" is good in concept, but in practice works better on a PC than an AS/400. This is especially true if your AS/400 is using parity protected DASD.

Bill, thanks for the response. Yes, my comments related to the keyed file were related more to our enterprise reporting system than JDE (hence my comment on sorted covering indexes).

After hours of searching for information on RGZPFM, RCLSTG, and the like, I found VERY little reference to fragmentation and contiguous usage of disk on an AS400 -- as your comments seem to support.

However, I did find one document I saved on my laptop back in 2001 with an interesting tidbit. It essentially says that disk defragmenting is continually occurring on an AS400, thus preventing fragmentation from being an issue while DASD utilization is below 80%. Above 80%, contiguous space may be hard to locate. It also mentions the STDDSKSTG command for systems that exceeded that 80% threshhold and required more DASD. The document was:

"Understanding IBM iSeries 400 and AS/400 Disk (DASD)
Including: RAID-5, 10K Disks, ESS, Pricing Information, and Sizing for Performance and Capacity"

February 2001 Update
BY: Brian Podrow

So, I guess I won't worry about it. I'm so used to rebuilding indexes, clustered indexes and heaps in the MS SQL world to decrease page, extent, and disk level fragmentation that I figured the AS400 needed at least a little attention.

Maybe not.

Thanks again for the response.

Ryan

[ QUOTE ]
So, I guess I won't worry about it. I'm so used to rebuilding indexes, clustered indexes and heaps in the MS SQL world to decrease page, extent, and disk level fragmentation that I figured the AS400 needed at least a little attention.

Maybe not.


[/ QUOTE ]

There's a reason them black boxes are soo dang-gummed expensive!!!