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.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

2 comments

Leave a Reply

Your email address will not be published. Required fields are marked *

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