Registry Variables and DB2_WORKLOAD=WC

Posted by

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.

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home

2 comments

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.