The PCTFREE Problem
"Joe the DBA" emailed us and asked:
We have (in my opinion), some poorly designed application code that is using a poorly designed table (before my time 
). By poorly designed table I mean, its not normalized, i.e. its too wide for my liking (130+ columns), has too many triggers with business logic in them, and likely too many indexes (15) for an OLTP situation. The table issues I can work on and deal with over time. The bigger problem is the application code. Its methodology is to do an insert into the table with minimal data and then come back later and to massive updates. And, of course, this would be the most heaviest used table in our system, needless to say the page overflows can get quite high, forcing reorgs more frequently than I would prefer.
My question is about PCTFREE at the table level, from the documentation it appears the default is 0% free. That %free is free in the page, and it looks like it only comes into play when reorg'ing or doing loads. It appears to me that setting the PCTFREE would not do anything for the situation I'm describing. Am I interpreting this correctly? What about PCTFREE (level 1 and 2) in the indexes, should I put more focus on these for this particular scenario? Or am I just doomed to frequent reorg's until the application logic can be changed?
I appreciate your insight. Thanks, "Joe"
(Name changed to protect the guilty)
The PCTFREE Solution
Our PCTFREE Advice
The updates to your previously inserted rows will be completed on the same page provided that the updated row still fits on its original page. If the updated row no longer fits, then a write overflow occurs, and subsequent reads of the row result in a read overflow. Any kind of overflow results in double the logical I/O and possibly twice the physical I/O.
You are correct that the default PCTFREE for a table is zero percent - and this is problematic for many applications that perform inserts and subsequent updates. You are also right that PCTFREE is only respected, honored, or considered during REORG and LOAD, but INSERTs and UPDATEs can be indirect benefactors if the prior REORG has left adequate PCTFREE space available in its wake. The downside to having too much PCTFREE is that you may have more data pages which could slow down asynchronous prefetch I/O, but hopefully you have good indexes in place and prefetch I/O isn't a problem.
So, yes, do alter this table and give it some PCTFREE --- you will still need to do REORGs, but the number of Row Overflows can be deferred, and thus you won't have to run REORG as often. And, the available PCTFREE space should help speed up your UPDATEs.
PCTFREE of ZERO is appropriate for data warehouse databases that have very few, if any, insert and update statements executed within them - this is contrary to many ETL processes.
From the IBM DB2 docs:
PCTFREE integer
Specifies the percentage of each page that is to be left as free space. The first row on each page is added without restriction. When additional rows are added to a page, at least integer percent of the page is left as free space. The value of integer can range from 0 to 99. A PCTFREE value of -1 in the system catalog (SYSCAT.TABLES) is interpreted as the default value. The default PCTFREE value for a table page is 0.
What are your row widths after the updates have occurred? And what page sizes are you using? 16K or 32K pages would probably be best for you.
Once you know the average/typical row width, what percentage of your page size is this? 5%? The integer value that you use should be a multiple of the percentage to accommodate one full row. If one row requires 5% of the page, you might specify PCTFREE 25 to provide room for 5 rows.
Free space (PCTFREE) in your indexes will benefit INSERT statements and help maintain any CLUSTER sequence that you might have in place. PCTFREE in the index of 10-20% is usually a good choice for tables that have INSERT activity.
Summary
If this little tidbit about DB2 PCTFREE has been useful to you, then this is exactly why you need to get involved in The DB2Night Show - a limited number of FREE seats are available in the "studio audience". Sign up! And FOLLOW The DB2Night Show on Twitter at twitter.com/db2nightshow!
The shameless marketing moment
A typical DBI customer lowers CPU utilization by 30-90% and measurably improves response times within just a few hours of installation. DBI's DB2 LUW Performance Suite of Tools can help your organization Stretch a buck further by maximizing the efficiency and performance of your databases. Learn more about DBI's Brother-Panther® for DB2!
Best regards,
Scott
Printed from : http://www.dbisoftware.com/blog/db2_performance.php?id=139