Figured I’d start off the new year with this tip. Unbeknowst to many, IBM included range partitioned tables in the WSE edition in 10.5. Additionally, you get up to 128GB per instance, up from 64GB. Range partitioned tables offer improved performance via partition elimination and availability improvements via reorg of partitioned indexes only. No need to reorg all indexes or none as is the case with non-partitioned tables. Stay tuned!
Well, just when you think everything is running fine, I bet you can make it better by following this tip. First you’ll want to make sure you are at DB2 9.7 FP 3A. In 9.7 the LASTUSED column was added to SYSIBM.SYSINDEXES. This column will record the date the index was last used or accessed. Make sure you are on FP3A as this fixed a problem with the column bot being updated under certain conditions. Select this column for your table or tables of interest and start getting rid of unused indexes. Of course, conduct a sanity check before dropping them. Stay tuned! Happy New Year!
Every now and again I come across a problem query that involves case sensivity. I noticed a steady increase in CPU usage of about 5% since this past Thursday. Suspecting a code change was introduced unbeknownst to me, I had one of my guys take a look. We found a query running with a LOWER function in the where-clause. This was an often executed query and the optimizer had chosen a table scan. So knowing how to fix this from years of experience, we created a generated column on the column in question and voila, index only access! Problem fixed. This is 9.7, in 10.5 we can create a function-based index also. This is a link to a nice Developer works article by a friend from the lab. http://www.ibm.com/developerworks/data/library/techarticle/0203adamache/0203adamache.html
Related to my previous post about using the stored procedure to create and migrate explain table information, the SYSPROC.SYSINSTALLOBJECTS is the preferred method so instead of using the old way, which was a favorite for creating EXPLAIN tables (db2 -tvf EXPLAIN.DDL) use the stored procedure as future enhancements will be around using it…..
After upgrading your release of DB2, don’t forget to migrate your explain tables or your explains or use of Design Advisor may fail. Although still supported in DB2 10.5 FP4, there is a new stored proc to use……
9.7
10.5
CALL SYSPROC.SYSINSTALLOBJECTS (‘EXPLAIN’, ‘M’, CAST
(NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))
For more info refer to the Knowledge Center at
I wanted to make my followers aware of the IBM DB2 RFE process and link. Many folks think this is just for DB2 on z/OS but it is for many IBM products, including IBM Data Studio and DB2 for Linux, UNIX and Windows. So here is the link, be sure to bookmark it.
Contained in DB2 10.5 FP4 also known as the “Cancun” Release are a bunch of improvements and features. I’ll be posting quite a bit about this in the days and weeks ahead. Stay tuned!
I thought I would post a link to this good article on DB2 Developer works regarding selecting the right DB2 edition for your business. I get asked frequently by clients to assist them in selecting the right DB2 edition for them or in determining if they need to move to a different edition. This article addresses up to DB2 10.5 with BLU Acceleration.
I get a lot of questions from customers regarding HADR, TSAMP and PureScale licensing. Here is a good article from my friends in the IBM Toronto Lab: http://www.ibm.com/developerworks/data/library/techarticle/dm-1205db2halicensing/index.html
Get the “Cancun” release to get faster in the moment answers, high availability at a moments notice, and improved SQL and DDL capabilities plus up to 82x faster queries (internal IBM test) on the fabulous Power8 processers!!! Get moving with DB2 FP4 “Cancun” release and read more about it here:
Gunning Technology Solutions, LLC. THE WORLD LEADER IN Db2 LUW CONSULTING! 