When inserting a huge number of rows in a table (150,000 or more), DB2 slows enormously. To speed up the insertions,...
we must create a CLUSTER INDEX WITH PCTFREE. This will make DB2 prepare room for the new rows to be inserted. Another advantage is that the index doesn't need to be reorganized frequently. Here's how to do it:
CREATE INDEX indexname ON TABKLE tablename ... (options) ... CLUSTER PCTFREE 10
L.B. writes: "If I was a newbie DBA I might think: Wow! This guy's a genius! But, because I've been around the block a few years, I know that his advice has a few caveats:
1. You need to understand how PCTFREE and FREEPAGE work in DB2 (which is amazingly similar to IMS FREESPACE). PCTFREE says leave this much space empty at the end of each page. FREEPAGE says leave every Nth page empty. For example, PCTFREE 10 on a 4k page will leave 410 bytes of each page free. If your row is longer than 410 bytes, this freespace will never be used. Putting PCTFREE on an index may not run into this problem (your columns in your index are that big?), but it certainly is something to consider.
2. You need to know where in the table your inserts are going to be placed. For instance, If your clustering index is on a date/time value, and all the records being added are for the current date, then all your data may be placed at the end of the table. In this case, having PCTFREE or FREEPAGE values other than 0 just adds alot of empty, unusable space into the middle of your database.
3. This example doesn't indicate whether there are any unique indexes. If there are not, I have found it is fastest to drop all indexes prior to a large insert, then recreate the indexes and run a reorg. This can take hours off of a mass insert, because DB2 doesn't have to do any index maintenance.
4. Experience in our shop has shown that if a table is updated *only* thru batch loads, it is best to leave both PCTFREE and FREEPAGE as 0 on both the table and its indexes. We then make sure that a reorg is done after every load. For tables that are updated interactively, we determine what freespace is needed based on the clustering index. If the inserts are random, then freespace will definitely help, as long as the freespace is large enough to hold a row (see #1 above). If inserts end up at the end of the table anyway, because of the clustering index values, then we leave freespace at 0 and over-allocate the dataset to prevent dataset extensions.
So, knowing what I know, I would rate this tip very poorly, because it doesn't take all the things I've mentioned into consideration.
For More Information
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best IBM DB2 Web Links: tips, tutorials, scripts, and more.
- Have a DB2 tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical DB2 questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our DB2 gurus are waiting to answer your technical questions.