ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

PF, Data Organization, and Reorganization

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • PF, Data Organization, and Reorganization

    I am new to iSeries, so I apologize if these question are trivial.

    I have read that data is written to physical files in arrival sequence order, even if it is a keyed PF. My question is does new data continue to be added to the end of the file in arrival sequence order even after the file has compressed been reorganized on the key or based on a keyed LF?

    I have read that the idea of data ordered by a 'clustering' index/key isn't necessary on iseries because the data is spread across many disks. I believe data is read and written in pages, so this doesn't make sense to me. If 95% of the access to the PF is through one keyed logical file, it would seem the data should be organized by the key. The next row to be returned by the index should be on the same page, or next page whichever disk it may be on. Is my thinking wrong here?

    I appreciate your insight, and thank you in advance!!

    Scott

  • #2
    Hello,

    Data is not necessarily written in arrival sequence. It is written wherever there is space to write it. So if your PF has deleted records in it and you've defined your file to re-use those deleted records, it will write to the first available spot. If not, it is written at the end of the data. Rest assured that when you retrieve it in the proper order, and very efficiently. The only reason it'd retrieve in arrival sequence would be if you explicitly told it to not use the key (in native i/o -- I don't think this is even possible in SQL)

    That is correct that a clustering key is not needed. This system is built for database from the ground up. It does not do paging the way Windows/Unix systems do it -- it takes a completely different approach, even at the low (hardware driver) level... The physical data sequence on disk has very little impact on how quickly it can retrieve rows in the right sequence. (Though, a missing index -- aka logical file -- would kill performance -- as it would on any system.)

    There is not normally a need to reorganize ("compress") the data, unless you just have a multitude of deleted rows that you want to clean up. The RGZPFM command does have the option to reorganize the data into a particular key sequence, but it's really not necessary from a performance standpoint. Though, if you look at the data in it's physical disk sequence frequently, you may find it convenient to be in order. ;-) But, hopefully there's no need for that?

    But anyway... welcome to IBM i (which 10 years ago was known as iSeries... I really wish people would stop using the old names!)

    I think you'll be impressed at how much easier this is to administer than other databases because it's designed for it from the ground up, so a lot of the stuff that you'd have a DB admin do, like compressing and cluster keys, etc just isn't needed. If you love databases, you've picked the right OS! :-)

    Comment

    Working...
    X