I was happy to see the new workload registry variable in Commerce 7/DB2 9. Mostly out of laziness – it requires fewer variables to be set manually, but it also ensures that nothing major is missed (I imagine they may come up with more that won’t be added to the workload registry variable in real time). I had a whole argument with whichever part of IBM a client brought in to do a load-test performance review because I had set one of the paramters to “ON” instead of “YES”. I recently ran across this statement in the Info Center:
Note: If a registry variable requires Boolean values as arguments, the values YES, 1, and ON are all equivalent and the values NO, 0, and OFF are also equivalent. For any variable, you can specify any of the appropriate equivalent values.
I will surely be quoting this and linking it in any future such disagreements.
If I look at what is set, it is mostly what we set by hand before, with a few additions:
[i] DB2_OPT_MAX_TEMP_SIZE=10240 [O] [i] DB2_WORKLOAD=WC [i] DB2_SKIPINSERTED=YES [O] [i] DB2_OPTPROFILE=YES [O] [i] DB2_USE_ALTERNATE_PAGE_CLEANING=ON [i] DB2_INLIST_TO_NLJN=YES [O] [i] DB2_MINIMIZE_LISTPREFETCH=YES [O] [i] DB2_REDUCED_OPTIMIZATION=INDEX,JOIN,NO_SORT_MGJOIN,JULIE [O] [i] DB2_EVALUNCOMMITTED=YES_DEFERISCANFETCH [O] [i] DB2_ANTIJOIN=EXTEND [O] [i] DB2_SKIPDELETED=YES [O]
I would like to hear the story behind DB2_REDUCED_OPTIMIZATION being set to “Julie” – what, was that someone’s girlfriend? Actually, that’s the parameter that has me most interested out of all of them overall(and most worried too).
I’m also interested in diggging further into the use of optimization profiles and how Commerce 7 is using them. I do wory a bit that they may be locking in access methods that may not be appropriate for every database size/distribution.
I would like to find a complete reference on Commerce’s thoughts on each variable and why they work for Commerce. I’m just not a “trust me, it works” kind of person when it comes to these things.