I've recently visited several customers who still have or are using V8 in their distributed environments. I thought I'd post this very short note as a reminder that DB2 V8 will be going out of support in April (09). You can view the details and announcement here:
http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&appname=iSource&supplier=897&letternum=ENUS907-125
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.
http://www-01.ibm.com/support/docview.wss?rs=73&uid=swg21200005
This Chat with the Lab webcast on migration can also assist you:
http://www.eseminarslive.com/c/a/Database/IBM091908/
So, take a look at the material and prepare to upgrade if you haven't already started.
Saturday, November 1, 2008
Tuesday, July 15, 2008
Wii Fit
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.
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
DB2 LUW Hints - Optimizer selection
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.tablea
on all columns <--- gathers basic column information
and columns (( col1, col2) <-- gathers column group information.
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.
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0005664.htm
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 profile
3) 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.txt
There 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:
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0612chen/index.html
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/t0024532.htm
Optimization profiles can be used in 8.2 and above. If you are still using DB2 LUW 8.1 where the optimization profile is not available, you may want to investigate the use of the "SELECTIVITY" clause. You can find more information about using selectivity clauses on SQL statements at the following developer works article:
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 :
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.tablea
on all columns <--- gathers basic column information
and columns (( col1, col2) <-- gathers column group information.
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.
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0005664.htm
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 profile
3) 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.
db2 connect to
db2 set current optimization profile="
db2 set current explain mode explain;
db2 -tvf query.txt
There 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:
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0612chen/index.html
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/t0024532.htm
Optimization profiles can be used in 8.2 and above. If you are still using DB2 LUW 8.1 where the optimization profile is not available, you may want to investigate the use of the "SELECTIVITY" clause. You can find more information about using selectivity clauses on SQL statements at the following developer works article:
http://www.ibm.com/developerworks/db2/library/tips/dm-0312yip/index.html
Thursday, May 1, 2008
DB2 LUW Lock Profile Parameters
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_SKIPDELETED and
DB2_EVALUNCOMMITTED
These 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_SKIPINSERTED was 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 wait
will 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.
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_SKIPDELETED and
DB2_EVALUNCOMMITTED
These 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_SKIPINSERTED was 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 wait
will 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
Work in Germany
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 SteelEye LifeKeeper for SAP NetWeaver on SUSE Linux Enterprise Server'.
This was a wonderful experience, and, I recommend everyone get involved with IBM redbooks if 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.
The book will be in draft format sometime this month.
Saturday, March 1, 2008
Linux on Z
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:
http://www.ibm.com/developerworks/linux/linux390/perf/tuning_rec_database.html
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:
http://www.ibm.com/developerworks/linux/linux390/perf/tuning_rec_database.html
Subscribe to:
Posts (Atom)