Db2 for z/OS PCTFREE FOR UPDATE for Performance

This article applies to Db2 12 for z/OS and universal table spaces. It is up to the reader to interpret the application to previous versions and table space types.

There are many choices when designing table spaces and tables for certain situations, one aspect of which is how much free space to leave for inserts and updates. One must carefully consider how the application will utilize the table activity, along with things like clustering, read versus update and insert activity, and random versus sequential access. In this article I’m going to discuss one aspect of the many design considerations and that is reserving free space for updates.

There are four table space level settings that dictate how free space is allocated and managed in Db2 for z/OS:

• FREEPAGE

FREEPAGE specifies how often Db2 leaves an empty page when loading or reorganizing a table space or a partition of a table space. The default is 0, and it should be set to 0 for nearly all table space partitions. The only time a FREEPAGE value greater than 0 should be considered is when a PCTFREE setting would be ineffective. This could happen if MAXROWS 1 is specified, meaning only 1 row allowed per page, or if the row length is greater than half a page in size. In these situations, FREEPAGE would be used as a substitute for PCTFREE when it is desired to maintain some free space to support clustering. Given that available page sizes in Db2 for z/OS are 4K, 8K, 16K, and 32K, there is a lot of flexibility in accommodated larger row sizes. Therefore, using FREEPAGE for a table space partition is only reserved for these rare instances.

• PCTFREE

PCTFREE specifies the percentage of each data page is left free when loading or reorganizing a table space or partition of a table space. This reserves some space on each data page for new rows or rows that change length. Its main purpose is to maintain a clustering sequence (rows kept in specific sequence based upon key values and supported by an index) if clustering is important for the table design and the application processes that are accessing the table. There are many reasons to maintain clustering sequence, and a future blog topic for consideration.

• PCTFREE FOR UPDATE

This was quietly introduced with Db2 11 for z/OS, and by this, I mean while it was touted in presentation, articles, and manuals, it mostly slipped past the typical Db2 DBA who routinely copied older DDL to make newer table space and tables. What PCTFREE FOR UPDATE does is it reserves a percentage of each data page for future update operations. So, when loading or reorganizing a table space or partition Db2 will reserve this space. In addition, insert operations and utilities that add data will not use this space. This is the percentage of space on a data page reserved just for updates only, and nothing else. It is effective immediately upon a CREATE or ALTER of a table space or partition. If separate PCTFREE and FOR UPDATE values are specified then the total free space reserved is the combined percentages. So, when PCTFREE 10 FOR UPDATE 10 is specified then 20% free space is reserved per page on LOAD and REORG, and 10% of that space is only for future update operations. There is a PCTFREE_UPD installation parameter that dictates the system wide default PCTFREE FOR UPDATE setting for table spaces. Also, setting the PCTFREE FOR UPDATE to -1 tells Db2 to manage this setting dynamically using real-time statistics.

• MAXROWS

MAXROWS specifies the maximum number of rows allowed per data page. This setting has been available for a very long time, but DBAs didn’t really utilize it for management of free space for updates. It was mostly used as a concurrency setting for small but highly updated tables by way of MAXROWS 1, which reserved one page per row. This technique has mostly been replaced with row level locking in these very specific situations.

The Need for PCTFREE FOR UPDATE

In a traditional sense Db2 for z/OS performs an update in place. That is a row of data that is changed via an UPDATE statement is returned to the same data page and location from which it was retrieved. This is a good thing because the Record ID (RID) of the row may exist in several indexes supporting the table, and changing those index entries could increase the cost and concurrency of the UPDATE statement. A RID is basically a pointer to a row in a table space, and consists of a page number and record number. It does not include the actual physical location of a row on a data page. The RID specifically points to a map ID on the data page. So, a row can move around on a page, but if it were to be relocated to another page the RID would have to change. This all works very well when the row size is a fixed length, but when variable length fields are introduced, or row compression is utilized, then things get a little more complicated. If an update causes a row to increase in length then it may not fit into the same location on the page from which it came. Now, it’s important to point out again that a RID does not point specifically to a row of data. What the RID is actually pointing to is a map ID, which is a fixed field at the end of the data page that points to the actual physical address of the row on the data page. This gives Db2 the opportunity to move the row to another position on the data page without impacting the RID value. If Db2 can find a contiguous amount of free space on the page for the expanded row it will move it there and update the corresponding map ID. If contiguous free space is not found on the data page then Db2 might attempt page compaction, where it attempts to shuffle data rows around in such a way that smaller “holes” of free space can be combined into one larger contiguous amount into which the expanded row may fit. Ultimately, if all attempts to place the expanded row on the data page from which it came fail, then Db2 has to locate the row on a different data page. It performs a search for available space on a different page, and when found it inserts the row on that page then going back to the original page to place a pointer to the new row location where the previous row image existed. This is called row relocation, and the resulting relocation is identified as an indirect reference.

Indirect references in small quantities may not be a bad thing, but if it happens too often it can lead to some serious performance problems. Many indirect references can cause random readers to have to read extra pages of data since when they follow a RID to a row and that row may actually be a pointer to a relocated row. It may cause a performance degradation for sequential readers if many relocated rows force the data to be unclustered (significantly out of sequence). If there are many indirect references and a RUNSTATS utility is executed then that may affect the cluster ratio and that could subsequently impact access paths. For update operations that are suffering from many row relocations due to row expansion the performance impact can be especially significant, and this could be amplified in a data sharing environment. It’s a complicated subject, but if a page set (partition) is group buffer pool dependent then every page compaction or row relocation could involve a synchronous write to DASD of the data page, as well as a synchronous log write. In these situations, update processes that take minutes can quickly turn into hours and a dire performance problem!

While the remedy for row relocations is a REORG of the table space or partition, REORGs in this age of complete availability are becoming more difficult to schedule and execute. The ultimate solution is to mitigate the need for a REORG by properly setting the free space, including PCTFREE FOR UPDATE when appropriate. This will help reduce row relocations in those situations where you have a combination of new clustered inserts and heavy updates that result in row length increases.

Monitoring and Setting Free Space Settings

There are a few tools that can be utilized to properly monitor free space allocations, and the need for a REORG. The Db2 real-time statistics tables are probably the easiest and most important of these tools. A simple query against the SYSIBM.SYSTABLESPACESTATS system catalog table can be used to view how many row relocations have occurred since a LOAD REPLACE or REORG utility was run against a table space partition, or when the partition was originally created. The columns that count these row relocations are the REORGNEARINDREF and REORGFARINDREF, and they can be used in combination with other columns in SYSIBM.SYSTABLESPACESTATS to determine if a REORG is needed, but also if an adjustment is needed to PCTFREE FOR UPDATE. I use a simple formula of total indirect references divided by total number of rows in the partition, or (((TBSP.REORGNEARINDREF+TBSP.REORGFARINDREF)*100)/TBSP.TOTALROWS). If the resulting percentage is greater than 5 that tells me the partition is ready for a REORG. If this happens too often, even when frequently REORGing, then it’s time to consider increasing PCTFREE FOR UPDATE.

Another way to monitor the need for free space for updates is to monitor the PCTFREE_UPD_CALC column in the SYSIBM.SYSTABLEPART system catalog table. This is the free space for update value that is calculated by “Db2 or utilities”. I don’t let Db2 control my free space for updates, but it still maintains this value for all table space partitions regardless of whether or not PCTFREE FOR UPDATE is set to -1 (let Db2 control). I do not find this value significantly useful in that I have seen percentages as high as 99%. I have seen it at zero, even though the partition absolutely needs it! I do use it, however, as an indication that free space for updates may be needed. Whenever it is greater than zero, I pay attention to the partition as a candidate.

An extremely valuable tool when evaluating performance is the SMF accounting report. I recommend running an accounting report for an important update application immediately after a REORG. In that way there is a baseline for update performance. When running subsequent SMF accounting reports for a similar workload shows a marked increase in buffer pool activity, log writes, and synchronous I/O’s, it may indicate that free space for updates is needed. Now, that increased activity may indicate other problems, but careful monitoring of the PCT_UPD_CALC from SYSIBM.SYSTABLEPART and indirect references in SYSIBM.SYSTABLESPACESTATS can be used to correlate a performance problem to the need for free space for updates.

When I determine that free space for updates is needed for a particular table space or partition, I do some analysis by looking at average row length, row relocations, and current free space settings, in order to come up with new free spaces settings. I try to balance inserts and updates accordingly over a projected period of time. These two queries are a starting point. The first one looks at current settings.

select TSNAME, PARTITION, PCTFREE, FREEPAGE, PCTFREE_UPD, PCTFREE_UPD_CALC
from sysibm.systablepart ts
where ts.dbname = ”
AND TS.TSNAME IN (‘<table space name’)
ORDER BY TSNAME, PARTITION;

This one attempts to check how many rows will fit in the free space, but also sacrifices some accuracy if free space settings vary across partitions.

SELECT tsname, avg(AVGROWLEN) AVGROWLEN,
int(((max(dec(pctfree,5,2)/100)*4096) – 40)/avg(AVGROWLEN + 2)) as rows_in_free,
max(pctfree) pctfree,
max(pctfree_upd) pctfree_upd, avg(pctfree_upd_calc) pctfree_upd_calc
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = ”
group by tsname;

Then I look at SYSTABLESPACESTATS for the REORGINSERTS, REORGDELETES,  REORGUPDATES, REORGUNCLUSTINS, REORGNEARINDREF, REORGFARINDREF values to make a judgement on whether to favor inserts, updates, or both. I focus on overall performance, also taking into account the value of clustering and potential data skew, if applicable. So, instead of large PCTFREE I am trying to balance PCTFREE and PCTFREE FOR UPDATE.

All of this has really paid off with improved application run times, and less frequent REORGs!

Cheers!