When an application on a member needs to make a change to a page which is currently held exclusively by another member, the CF can request the p-lock is released early. This process of releasing a page early is called page reclaiming.

This help eliminating the page lock waits across members, but these reclaims are expensive and this can be monitored via SYSPROC.MON_GET_PAGE_ACCESS_INFO () table function.

SELECT
  VARCHAR(TABNAME,30) AS NAME,
  VARCHAR(OBJTYPE,10) AS TYPE,
  PAGE_RECLAIMS_X AS PGRCX,
  PAGE_RECLAIMS_S AS PGRCS,
  SPACEMAPPAGE_PAGE_RECLAIMS_X AS SMPPGRCX,
  SPACEMAPPAGE_PAGE_RECLAIMS_S AS SMPPGRCS,
  RECLAIM_WAIT_TIME
FROM TABLE( MON_GET_PAGE_ACCESS_INFO('<SCHEMA>','<TABLE>',NULL)  ) AS RECLAIMMETRICS 
ORDER BY RECLAIM_WAIT_TIME DESC;

How to Reduce the Page Reclaims?

  • Smaller page size reduces false sharing conflicts and help reduces reclaims on tables and indexes
  • Increase PCTFREE for tiny hot tables
  • Adding CURRENT MEMBER or CURRENT NODE default column to the table and partition it on period and member.