It seems that date is arriving much sooner and faster than some expect. But getting to V9 or V9.5 is easier than you think. There is alot of good information available to help you, and the migration portal is a great place to start.

Tuesday, July 15, 2008

I realize I stated this Blog would be all about DB2 stuff, but, I can't help myself. I have to say a few lines about the Wii Fit. I bought my Wii Fit in June and have only been using it a short while, but it's awesome.

As an IBM employee, I found myself doing heavy travel for the last 8 years. My typical week entailed getting on a plane Sunday or Monday morning and returning on Friday. After a long week , at sometimes a city across several timezones, I found it very easy to fall into the excuse of not exercising. It was either, I'm too tired or I'm too busy.

Well, when I returned from my Redbook Residency in Germany I decided to take a new position with IBM. My travel is now limited to a local area, and so, I no longer could fall prey to the 'no time - too tired" excuses. That said, I somehow was still in that old pattern of very little exercise.

I'm not exactly sure when I entered this era of my life. When I was younger, both in high school and college I was an avid runner. Exercising was one, if not, the main focus in my life. Well, exit that era and enter a 20plus some of IT and work. Being in the shape I was, it didn't hit immediately, but at some point I must have stopped looking into the mirror with a realistic view. I'm overweight and over forty, ...ugh.. And, although I'd never like to admit it - I'd rather have a beer than go out for a run.

So, in an effort to inspire myself I purchased the Wii Fit. When you start with the Wii Fit it politely calculates your BMI (while hiding your weight if you'd like) and then gets you going an a set of exercises. The Fit has 4 categories: Yoga, Strength, Aerobics and Balance. It is designed to start you off slow, but not too slow, and as you work with the Wii Fit it opens up new games ( or exercises) for you to play. See, I already called them games ...see the point... now exercising is fun again. I may actually get my nerdy out of shape body a bit back to the image it should be and not the one I see in the mirror.

If your having a tough time starting, I think the Wii Fit is kinda fun and I don't think it's because I'm kinda a linux girl that I like the Wii...it's just that cool. Don't look for it to be a substitute for outdoor activities, like running or biking - but use it as a supplement and an instrument to get you going ...and you might find you like it. I did.

And, I like it so much I was willing to write about it. I'll post another short update in six or eight months, to let you know if the "novelty" of the Wii has worn off and it's just collecting dust. I hope not, cause if so ..then I'll probably not have achieved my goal of lowering my BMI and weight. .... wish me luck.

Monday, June 16, 2008

Many Oracle DBA's, myself included, are familiar and may even use optimizer hints quite frequently. In a DB2 LUW environment, it is recommended you try other approaches to influence the optimizer before implementing hints. In DB2 LUW terminology, a hint is an optimizer profile.

DB2 relies on a cost-based optimizer to choose the optimal access path. There are many aspects and factors that influence or affect the optimizer decision. System configuration, such as buffer pool sizes, sortheaps, cpu parallelism, i/o characteristics - as well as, indexes and constraints to name just a few. Catalog statistics play a major role in influencing the optimizer, so it's no surprise that the lack of statistics can be detrimental.

So here are a few things to examine before going down the path of hints or optimizer profiles.

I would examine both the type and level of runstats being executed and review the registry variables that may affect optimization.

There are several flavors of statistics that DB2 uses in optimization selection. There are:basic statistics : number of rows, pages, and active blocks in tables, number of data values and length of data values for a column, data range information.non-uniform statistics : most frequently used value, quantiles ; used in equality predicates, used in range predicates.column group statistics : number of distinct values in a group of columns ; important for correlation.detailed index statistics: clustering details ; used to estimate data fetch pages and model I/O vs buffer sizes.user-defined statistics: UDF statistics to specify I/O and CPU costs.

These statistics are stored in the system catalog and can be updated if necessary using the db2look utility. One reason you may want to update statistics is to mirror or replicate production statistics in a non-production environment. The db2look utility can easily do this using the -m (mirror) option to extract statistics. This article covers the use of that utility in more detail :http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0508kapoor/

So, statistics and the type or level of statistics play a major role in optimization. Thus, the lack of statistics can often be detrimental to an application.

One reason DB2 sometimes does not choose the most optimal path is simply we have not supplied DB2 with enough information, or level of statistics, for DB2 to make the appropriate optimization selection.

There are some newer options available on the runstats utility to improve performance by providing more specific information. Let's take a look at one I've found to be helpful.

Column group statistics. Column group statistics provide information used to detect correlation.Without this information DB2 may underestimate the join cardinality. Column group statistics can be particularly useful in equi-join predicates. For example:

where tablea.col1 = tableb.col1 and tablea.col2 = tableb.col2

An example runstat might be:

Runstats on usera.tableaon all columns and columns (( col1, col2)

Column group information is stored in SYSCOLGROUPS and SYSCOLGROUPSCOLS.

Now let's take a look at some of the registry variables that can affect optimization. You can find detailed information on these registry variables at the information center.

The query optimization configuration parameter (DFT_QUERYOPT), as well as, several registry variables affect the access plan chosen. DB2_REDUCED_OPTIMIZATION=UNIQUEINDEX is a registry variable that will be default starting in V9.5. It instructs the optimizer to use the unique index that is fully qualified to access a table.

So, you feel you have exhausted all other options and are wondering how to implement a hint.

1) Set the registry variable DB2_OPTPROFILE=YES (this is needed in V8.2 and is default in V9)2) Create the SYSTOOLS.OPT_PROFILE table to house the profile3) Import the profile into the table. For example:IMPORT FROM file1.del OF DEL MODIFIED BY LOBSINFILE INSERT INTO SYSTOOLS.OPT_PROFILE;4) Test your troubled SQL and ensure it is know taking the access path you would like it to traverse. .PROF1 is the name of the xml you imported from file1.del above.

db2 connect to ;db2 set current optimization profile=".PROF1"db2 set current explain mode explain;db2 -tvf query.txtThere are a couple good places to get detailed information about optimization profiles and statistical views. If you need more detail I'd suggest the following:

Thursday, May 1, 2008

After returning from my residency in Germany, I decided to take a new role within IBM. In my new position I will be helping clients with our Data Management software solutions running on open systems.

I've decided that this blog may serve as a good place to put commonly asked questions I see from customers. Hopefully, if you've reached this blog via a search - then I've managed to hit a topic that you are also interested in.

My first topic, covers a few registry variables in DB2 related to locking. Locking is often a topic of concern for many customers. Some, migrating from other DBMS platforms are just trying to understand the differences in platforms, others, are looking for solutions to help alleviate lock wait and or lock contention.

The first two parameters that were introduced to allow you to control the affects of how DB2 is managing concurrency were :DB2_SKIPDELETEDandDB2_EVALUNCOMMITTEDThese are commonly referred to as lock deferral parameters. If your application can tolerate evaluation of predicates against uncommitted data, then these parameters can improve application concurrency. Setting these parameters to YES or ON will tell DB2 to defer locks on uncommitted deletes and updates. There are some restrictions in using these variables, and, one of them is that they are only in affect if the isolation level is CS or RS (cursor stability or read stability).

So, how can you tell what isolation level your application is using ? It's using dynamic sql and you aren't sure ?One way, and I think one of the easiest ways, is to use the db2pd tool.db2pd with the -dynamic flag , will let you view both the sql statement currently executing and the ISO level.

Much like the previous two parameters, DB2_SKIPINSERTEDwas introduced in 8.2.2 (aka 8.1 fixpack 9). This parameter would tell DB2 to skip (not lock) uncommitted inserts for applications using ISO CS or RS. Additionally, in 8.2.2 the DB2_EVALUNCOMMITTED was extended to include plans with ISCAN fetching.The setting to extend this is :DB2_EVALUNCOMMITTED=YES_DEFERISCANFETCH

These locking parameters are set automatically in SAP for customers who deploy DB2 using the SAP DB2 Workload optimization. I think many other applications can benefit from these same settings because by default during row locking for DB2, DB2 locks all rows scanned. In DB2 V9 , setting EVALUNCOMMITTED to yes or on, will operate the same as YES_DEFERISCANFETCH.

You can find a detailed example of how this works in the redbook titled "SAP Solutions on IBM DB2 UDB V8.2.2 Handbook" , in chapter 8 page 438-439.

Here is the link to that redbook :http://www.redbooks.ibm.com/abstracts/sg246765.html

If you haven't figured it out by now, I'm a big advocate of redbooks. They contain alot of good information and can often be referenced as a source to provide resolutions to inquiries.

So we stated that db2pd can be used to check the isolation level, but it also is a very handy tool to quickly check the status of lock waits on your database. Using db2pd with -locks wait , for example:db2pd -db sample -locks waitwill show you each of the locks in a wait status and their associated waiter. This parameter of db2pd is available with V8 FP9 and beyond.

Another feature to help in diagnosing and debugging locks, is the lock timeout report. The lock timeout report was introduced in DB2 V9.5 but is being ported back to 9.1FP4 and 8.2FP16.This feature is enabled by setting another registry variable DB2_CAPTURE_LOCKTIMEOUT. The lock timeout report is generated by the agent receiving the lock timeout error.

Tuesday, April 1, 2008

I spent 5 weeks, mid-February to late march, in Walldorf, Germany. A team of mostly IBM'rs working on a redbook titled 'Building High Availability with SteelEyeLifeKeeper for SAP NetWeaver on SUSE Linux Enterprise Server'.

This was a wonderful experience, and, I recommend everyone get involved with IBM redbooksif they are interested. Germany is a wonderful place..and I say this not because of my last name. The beer is good, the culture is nice and it's nice to broaden and break away from our normal routine by experiencing other environments.

Saturday, March 1, 2008

For the last two years, the majority of my work had been focused on helping customers deploy linux on system z. System z, or the mainframe, has had virtualization capabilities since the 60's. So it's a natural leader and great platform for virtualization. I'm often asked how to tune DB2 on Linux for System z. Much of the tuning techniques used in DB2 LUW apply across all platforms, so many of the same techniques apply. I've documented in this post, the top 5 tips to look at in a Linux on z environment. Of course, these are only five. There are other considerations for tuning. But, rather than make this blog and entire paper on tuning. I've picked out 5 to look for. Thanks for reading.

1) The number one issue when running under z/VM is that customers over configure linux memory for the guest. Steven Wehr has put together some nice tips in this paper. Although the topic discusses Websphere, the concepts again apply for DB2.http://www-03.ibm.com/systems/z/os/linux/pdf/avmlinux.pdf

2) Disable the linux on demand timer .All distrubtions for Linux on z have the jiffy timer patch, ensure that it this is enabled by verifying kernel.hz_timer.

3) Check on and modify the linux i/o scheduler. There are several options: as,noop,cfq and deadline. The as (anticipatory scheduler) is not a good option.

4) Use DIO and AIO .

– Configure maximum number of channel paths– Spread disks over different ranks within a storage server– Use logical volumes with striping– Consider exploiting PAV

5) Of course, this is somewhat application dependent - but generally it's best to turn off read ahead . On LVM disable this with lvchange, on block devices set the value with the blockdev command. At the DB2 tablespace level, prefetchsize to zero.

For more information on tuning databases for Linux on System z, check out this website:

About Me

I joined IBM in 2000 and I am currently a Senior IT Specialist in the IBM System and Technology Group (STG). I have over twenty years experience in the Information Technology field with a primary focus on database management.
You can reach me at jan.ibm@gmail.com

Subscribe To

Get DB2 for FREE - Click Below

Free DB2 - Try it you'll like it

Disclaimer:

The comments on this blog are my own personal opinions and do not necessarily reflect the positions or opinions of my employer (IBM) or their affiliates. If you feel a blog posting is inappropriate or inaccurate, please contact the author. The use of the information contained in this blog is the responsibility of the user. While the author's intent is to provide accurate information, there is no guarantee provided.