Heads Up! Logical File Default Setting Change in 7.5

Now that baseball season is off and running in the United States, there’s a good chance you’ll hear the phrase, Heads Up, if you’re near a baseball field.  This phrase is often used during practices to warn players and coaches that they should keep their heads up and eyes open because baseballs are flying through the air. If you’ve ever experienced the pain of getting hit by a baseball, this phrase is a welcomed reminder to be alert and on the lookout for a flying baseball.

The item to be alert and prepared for with the IBM i 7.5 release is a change to the default logical page size setting for logical files. So, you’re probably wondering what does the logical page size setting affect?  When a program or query uses an index to search for a key value or retrieve the data in key order, the logical page size dictates the block size of the keys that will be brought into memory by Db2..

Thus, each time keyed access is requested from a logical file with a logical page size of 8K, Db2 will bring 8K worth of key values into memory.  If the logical file’s logical page size is 32K, then 32K worth of keys values will be brought into memory at a time. Assuming there’s enough memory in the pool, usually retrieving bigger blocks of key values is better for performance – especially if every key value in the logical file is being read from beginning to end.

For logical files (LFs), the logical page size is set based on the length of the key definition. Prior to the 7.5 release, this key length-based calculation most of the time resulted in an 8k logical page size.  With the IBM i 7.5 release, the logical page size is still based on key length. However, the minimum logical page size used is 64K instead of the old 8K minimum.

The reason that the minimum page size was changed for LFs was because Db2 for i in the IBM i 7.5 release enhanced the maximum size limit for LFs (and indexes).  The new maximum size limits range from 4 TB to 16 TB – the reason there are multiple limits is that the size limit depends on the logical page size. The largest size limit of 16 TB only applies to LFs that have a logical page size greater than or equal to 32K. 

So far, you may have noticed there has been no mention of SQL indexes in the discussion of logical page sizes.  That’s due to the fact that IBM changed the default logical page size for SQL indexes to 64K way back with the V4R2 release in 1998.  This change was done to improve query performance because that’s the primary purpose of creating an SQL index. The 64K logical page size helped the Db2 engine search and process more key values in a single request.

IBM didn’t change the default logical page size for LFs at the same time because there was the potential for negatively impacting the performance of existing RPG and COBOL programs using LFs. If the RPG or COBOL program was running in a memory-starved pool, then bringing larger blocks of keys into memory may not be the best idea. Thus, IBM decided to play it safe. You must remember that 25 years ago there was a much higher chance of memory being constrained on a server due to cost and/or hardware limitations.

Yet, that’s the reason for this entry to raise your awareness. The performance signature of applications using native record-level access could change starting with the IBM i 7.5 release – positively or negatively. This logical page size change in 7.5 only applies to newly created LFs or existing LFs that are recreated with a Page Size parameter of *KEYLEN.  The existence of the Page Size parameter does mean that you can easily go back to pre-7.5 behavior by replacing the *KEYLEN value with 8 (or whatever the calculated page size was on prior releases).

The easiest way to see the calculated logical page size for a LF chosen by Db2 are to use the Description task using the Schemas tool provided by IBM i Access Client Solutions (ACS) or to query the logical_page_size column in the SYSPARTITIONINDEXSTAT view in QSYS2. In the ACS output below, the first highlighted value represents the value of the LF’s Page Size parameter (i.e., key length based) and the second value is the calculated length value of 8K.

Given that this logical page size default only applies to newly created logical files, this default setting change will not have a big performance impact when you upgrade to IBM i 7.5. However, it’s never a bad idea to be aware of and prepared for Db2 for i settings that could change application performance going forward.