DB2 Performance Maintenance - Avoid Database Reorgs

Adjust Your FREEPAGE and PCTFREE for Growth

The proper free space settings along with the appropriate allocations detailed last week can help you avoid any DB2 reorgs and improve DB2 performance for a long time while your database grows. Properly setting your free space is done through the FREEPAGE and PCTFREE table space and index space settings.  These settings should be set up according to the table’s projected growth and its compressed or uncompressed stored row or index key size.

The queries 1 and 2 below allow you to examine your current settings and your preparations for growth with the FREEPAGE and PCTFREE settings within your objects.  First within the table space the FREEPAGE and PCTFREE settings should be set to provide enough extra room for growth and the table’s ultimate size.  A common issue is that everyone has the same settings for all their database tables when it should be different for every table and index to improve DB2 performance.

These FREEPAGE and PCTFREE settings should be different because each table row or index key length are different sizes and the PCTFREE setting should be set to provide enough room on an individual page for additional entries.  Too often the table or index size is bigger than the space reserved through the PCTFREE setting.  For example, if the table PCTFREE is set to 10 percent for a 4k page that reserves 400 bytes.  For a table space supporting rows that are 450 bytes this is not enough free space and is not effective for leaving room for an additional row on that page.  This incorrect setting will result in the table space going into secondary extents once all the primary space is utilized causing poor DB2 performance.

Also, the FREEPAGE is another table space and index space parameter for helping put free space into the table or index.  The FREEPAGE setting is the number of pages that are loaded before a page is left empty as free space within the object.  Most vendor tools that calculate table and index sizing default the FREEPAGE setting to 0, not utilizing the parameter effectively and leaving no space for growth within the object.

The FREEPAGE parameter should be used to provide for object growth.  Examining your growth projections from your application architect, if they are provided, the table space and index setting should again be different for every object because of the rows and index keys per page, their sizes and their corresponding growth rates are different.  Use the FREEPAGE setting to provide the appropriate empty pages for growth up to a full year to help avoid table and index reorgs for all your heavily referenced objects and poor DB2 performance.

–Query 1

SELECT SUBSTR(DBNAME,1,8) AS DBNAME,
SUBSTR(TSNAME,1,8) AS TSNAME,
PARTITION,
FREEPAGE, PCTFREE, AVGROWLEN,
PQTY, SQTY, SPACE
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME LIKE ‘database criteria%’
WITH UR;

–Query 2 –

SELECT
SUBSTR(IX.TBNAME,1,18) AS TBNAME,
SUBSTR(IX.NAME, 1, 18) AS IXNAME,
PARTITION,
IXP.PCTFREE, IXP.FREEPAGE, IX.AVGKEYLEN,
IX.NLEAF, IX.NLEVELS,
IX.CLUSTERRATIO, IX.FIRSTKEYCARDF, IX.FULLKEYCARDF
FROM SYSIBM.SYSINDEXES IX,
SYSIBM.SYSINDEXPART IXP
WHERE DBNAME LIKE ‘database criteria%’
AND IXNAME       = IX.NAME
AND IXCREATOR    = IX.CREATOR
ORDER BY 1,2,3
WITH UR;

Saving Resources and CPU with the Correct Freespace Settings

These queries are only a view into what is defined for your tables and indexes and is only the start to perfecting your settings for your database activity and growth and improved DB2 performance.  By getting these space allocation and free space parameters properly set for the appropriate row and index sizes, your database performance will minimize I/Os, save storage secondary extents and unnecessary CPU spent looking for room to store new entries.  So the next time you think about a table or index reorg, first use these queries to examine and adjust your object settings.  It might be the last time you need to analyze them and the last time you need to schedule off-hours support for reorgs.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>