A Few Differences When Moving From 9.7 to 10.1

I have recently had the chance to do several installs on DB2 10.1 and migrate a couple of 9.7 environments to 10.1. I may be late to the party – oddly, IBM takes longer to certify WebSphere Commerce on new versions of DB2 than many external vendors do with other products. But I know there are still many out there who may be in the same boat or going through similar upgrades.

I’ve done a fair amount of research, and I’m not going to list every difference or change here, just a few that have stood out to me and had me thinking about my strategy.

Active Log Path

I don’t believe I was working with DB2 the last time a change in the active log path came along. I’m not talking about the default location here, but just the stuff DB2 adds to the path after whatever you’ve specified for NEWLOGPATH. So for example, if I have it set like this:

db2 update db cfg for sample using NEWLOGPATH /db_logs/SAMPLE

and then recycle or activate the database, this is what I get:

db2 get db cfg for sample |grep "Path to log"
 Path to log files   = /db_logs/SAMPLE/NODE0000/LOGSTREAM0000/

The same actions back in DB2 9.7 or before would have gotten:

$ db2 get db cfg for sample |grep "Path to log"
 Path to log files   = /db_logs/SAMPLE/NODE0000/

Instead of just a node identifier being added to the path, I now have also a “LOGSTREAM” value. This change is actually to support having PureScale and regular DB2 run from the same code base, and makes sense in that context, but it is possible that you may have scripts relying on this location, so you may want to be aware of the change.

Automatic Compression of Log Files

If you have a TSM or other device where your archived log files are sent and compressed, that is great. If, like many of my midrange clients, you do not and logs are simply archived to a local disk, then you may just love archive log file compression. On 9.7 and earlier, I have a simple cron job that goes out and finds archived log files older than usually about 3 days and compresses them to save on disk space. Now, with version 10.1, I can automate that process, which saves me the administration and also some space, since DB2 can work with and uncompress the log files as it needs them without me in the middle. You can enable this feature using:

db2 update db cfg for dbname using LOGARCHCOMPR1 ON

There’s also LOGARCHCOMPR2, and these parameters correspond with LOGARCHMETH1 and LOGARCHMETH2. Obviously if your target for either of these is a device that does compression or dedup, you probably would not want to also enable compression at the db2 level. But enabling this has become a part of my standard build process now.

Use of Storage Groups

I generally have a tablespace or two that I add into my WebSphere Commerce databases to handle some administrative things like event monitors and collecting performance data. Up until now, I’ve placed those in a DMS tablespace with a fixed size to limit the tablespace’s ability to fill up my filesystems. Back on db2 9.7, I used:

create large tablespace DBA32K pagesize 32 K managed by database using (FILE '/db_data_adm/WC005D01/DBA32K.LRG' 2 G) autoresize no bufferpool BUFF32K no file system caching dropped table recovery on@

Now with DB2 10.1, I’ve changed that to:

create stogroup db_adm_stogrp on '/db_data_adm'@
create large tablespace DBA32K pagesize 32 K managed by automatic storage using stogroup db_adm_stogrp autoresize yes initialsize 100 M maxsize 2750 M bufferpool BUFF32K no file system caching dropped table recovery on@

DMS tablespaces are deprecated, and I’m working on getting with the times and properly using storage groups, even for my tiny little admin stuff. It probably doesn’t make a lot of difference at this point, but I like to play with solid new features.

Default of Sampling for Detailed Statistics

In DB2 10.1 the default for detailed statistics changes from full read to sampling. Other statistic defaults stay the same. I have chosen to allow that sampling to happen. I don’t think the difference will be significant for many of my clients, and a number of the queries to my databases are using parameter markers for the most critical workloads anyway. Use of parameter markers disables the use of distribution statistics. Most of my clients do not require sampled statistics in other areas because they can tolerate the runtime of non-sampled statistics.

Other/Miscellaneous

There are a few other things with the conversion from 9.7 to 10.1 that are small or that I’m still thinking about.

• In the upgrades that I have done, I have increased both LOGFILSIZ and LOBUFSZ by about 15% to account for additional logging in 10.1.
• I have plans to create an event monitor for change history – it sounds like a nifty new feature, and something I could use. However, the tables it writes to must be manually pruned, so I have not yet implemented this until I can get a script written/altered to do that.
• I’m also planning to create usage lists for my low-cardinality indexes, since I want to track when and how those are being used. But I need the time to further research them and properly prune their output. I’m also not sure I want them on all of my dev environments – they make more sense on my staging and production environments to me.

So, friends and readers – any experiences with moving from 9.7 to 10.1 that you’d like to share?

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

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