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.
