Logical File Page Size (PAGESIZE)

[EmmanuelW1]

We've been working with IBM support on an issue with a slow-running query - at least the first run is slow (about 3 minutes), but the second run completes in about 10 seconds. I know it's normal to have the second run faster, but not *that* much faster.

Anyway, after a lot of traces and suggestions from IBM to create new views, then create the new views again with a 64K page size, I finally asked if re-creating all of the views (DDS-defined logical files actually) with a 64K page size that are over the three physical files used by the query would make a difference. They said: give it a shot. So I did, and wow! The run time of the query improved dramatically, as did non-query workloads that use these three files. (There are roughly 80 logical files over the three files.)

So now I'm wondering if it would make sense to re-create all of our logical files, specifying a 64K page size. I asked IBM about any downsides, and support is saying: no downsides. However, Knowledge Center says (https://www.ibm.com/support/knowledg...pagesize.htm):

"Consider using the default of *KEYLEN for this parameter, except in rare circumstances. Then the page size can be determined by the system based on the total length of the keys. When the access path is used by selective queries (for example, individual key lookup), a smaller page size is typically more efficient. When the query-selected keys are grouped in the access path with many records selected, or the access path is scanned, a larger page size is more efficient."

So my question: has anyone traveled this road? Were there any downsides from increasing PAGESIZE to 64K?

Thanks,

Emmanuel

[B.Hauser]

First DDS is an outdated technology. The technology you should use is SQL.
You can use any SQL created index with native I/O like a keyed logical file. And even better, derived and sparse indexes are much more powerful than DDS described logical files.
... and the default page size for an SQL index is 64K!
I'm wondering why the support did not suggest to convert the DDS described logical file into SQL indexes.

Are you handling perhaps with joined logical files?
Sometimes also the DYNSLT DDS keyword causes performance issues.

For more information about SQL indexes and logical files and how they can be used in composition with native I/O you may read the following article.
SQL Indexes and native I/O - no contradiction
https://developer.ibm.com/articles/i-sql-indexs-and-native-io/

Birgitta

[EmmanuelW1]

Hi Birgitta,

Thanks for your response. Not to make excuses, but we're a JD Edwards World shop - these logical files are for the most part what Oracle has delivered, and yes, some are join logical files, and some use DYNSLT.

It's odd that IBM recommends using *KEYLEN (which actually means either 4K or 8K page size, apparently) while the default for SQL-created views is 64K. While I understand that DDS is outdated, if there's a huge performance advantage from larger page sizes (which appears to be the case), why not recommend using them, not only in "rare circumstances"?

Cheers,

Emmanuel

[Rocky]

Keep in mind that DDS documentation probably hasn't changed in a number of years, but the underlying technology has. The system has been modified to perform best with SQL.

The page size has to do with page faulting. You probably could've done some tuning on your system to improve performance as well. By specifiying 64K for page size it ensures that more of the index is in local memory longer for your program to access, otherwise it can be swapped in and out with your faults more often.

In short - the rules have changed a little with the advent of SQL - I'd recommend the 64K specification.