developerWorks: This is the developerWorks podcast. I'm Scott Laningham. Today, three winners of the most recent IDUG contest will join us to talk about their essays on using DB2.

(Editor: To discover what new resources are available this week in developerWorks and My developerWorks, jump to the end of this interview.)

The International DB2 Users Group, or IDUG, recently announced winners of their "Do You DB2?" contest that was held over the summer in North America. The contestants wrote short essays on their use of DB2 products and the contest was open to all DB2 users whether a student, researcher, or database professional. The three winners are with us on the line now.

Michael Krafick and Sigen Chen are both senior database administrators at their respective companies; and Nadir Doctor is a DB2 consultant and IBM® information champion.

Nadir, let's start with you. Your runner-up essay earned you an iPad as a prize, right? Pretty cool. And you wrote about using DB2 9.7 self-tuning memory manager (STMM) for OLTP and data warehouse environments. Talk about your essay, Nadir; what was your DB2 story?

Doctor: What can be useful in large and complex environments is to use the self-tuning memory manager for varying workloads, especially when you have peak activity so that you get better performance.

What I had done was I kept the anticipated workloads in a table so that we had some history and I made sure that before we turned on STMM we tried to optimize the physical design as much as possible — for example, defining clustered indexes and market-dimension clustered indexes so that we can avoid SOX as much as possible. So when we do this, then the values which will be obtained by STMM would not be very divergent or would not oscillate a lot.

So that was the basic theme and once we get the anticipated workload timed, then we turned on STMM and use the log parcer script to obtain the values for the various settings, create a summarized record, and load that in the table. And then, write SQL to correlate the anticipated workloads with the summarized values which we obtained, and then turn STMM off. Do not leave it running all the time, turn it off and use those summarized values for your peak workloads.

developerWorks: That's great Nadir, thank you for that explanation.

Now, Sigen, let's turn to you. You were the other runner-up iPad winner; let's talk about your essay on achieving big savings using DB2 9 deep compression. What's your story there?

Chen: Well, I had a warehouse database which experienced a severe memory shortage, heavy I/O wait because of the allotted data size and the small amount of the memory. So it was my hope that DB2 version 9 compression feature might help me to reduce the data for the input and therefore, could help me to alleviate the memory contention bottleneck.

So I did some tests, showed some improvements in results. So I went ahead and moved to a production system, compressed about 40 to 50 big tables. By saying big tables, I mean the tables that have above a million rows.

So after activating the deep compression feature, table space usage was reduced about two-thirds which is very impressive. And most of the queries also run faster, about 38 percent or above faster. Maintenance time is reduced significantly.

And most importantly, the memory constraint problem is eliminated which is desperately needed in my system because a memory shortage was the bottleneck. And CPU usage went up about six percent after the compression feature is activated. But the memory saved is about 80 percent, so that's a good tradeoff because my system was not a CPU bottleneck.

developerWorks: That's great, Sigen, and I appreciate that description. That's an amazing and impressive improvement there. I'm sure you were thrilled to have that.

Michael, I know you were the grand prize winner (although every one here is a winner clearly), but your prize, a slightly larger screen, it sounds like a 55-inch HDTV, it guess its kind of hard sticking that in a backpack but it's great at the home, right?

Krafick: Yes, it was a fantastic prize. As a matter of fact, when I saw and set it up for the first time, I realized I didn't have a table large enough to set it up on. [LAUGHTER]

developerWorks: That would be my problem too. It would require furniture, so it's a prize with a price included.

Krafick: It was just icing on the cake for me winning the 55-inch TV, because it was a matter of, you know, my story was about how DB2's balanced warehouse methodology, specifically pertaining to BCU units kind of made me and my team look really good to upper management when we were put in the spotlight for performance problems.

Basically what had happened when the new team was formed actually with this company and we were presented a problem where we had a 15TB database over multiple nodes that was just performing like a dog.

And the more that we looked at it, the more that we analyzed it, we realized that we were built on balance warehouse methodology — balancing our load over multiple nodes via DPF — and what we were seeing was the database resources were just getting completely chewed up, that the server was kind of on its knees due to workload, growth.

And when we ultimately looked at it, you saw the intent was there to build it property, but like any other company it was at the time built it, make it work with what you have. So when we presented to management what the problem was, that we were on too small of a box, we didn't have enough nodes to handle this warehouse, we went back with IBM and sat down and kind of put the problem down on paper that ultimately we needed to add on a new BCU unit.

And IBM touts with DB2 how easy this is. It really is plug and play: Build the server, plop DB2 on it, build out the architecture, and just redistribute. And we all hoped it would be that easy, and ultimately, it was.

After adding on a third node to our warehouse, we were able to drop our CPU usage by 50 percent and increase our workload by 20 percent. So ultimately when it was done, we were able to do this, what seemed like a monumental task, in very little time and got huge performance based off the methodology that IBM and DB2 promote.

developerWorks: That's a great story, Michael, and even though this makes us sound a little bit like an infomercial since this is an IBM podcast, I know that story is, and all of your stories are, music to the ears of my DB2 related colleagues here at IBM.

Krafick: Oh, definitely. The one thing that I'm really happy with DB2 is that it does what it says. If you follow what's promoted and what's tested, it always works. Whether it be through self-tuning memory management or compression like the other contestants mentioned or in something as complicated as adding on a new node, DB2 does what it says and it does it efficiently.

developerWorks: Sigen, turning back to you, what are your thoughts about your DB2 experience versus maybe other databases you've used? And how would you describe it to somebody that was asking for your opinion on things?

Chen: Well, based on my knowledge, DB2 on LUW platform [Linux, UNIX, Windows] has made some noticeable improvements in the past several years. Version 8 is a common client, reduces the code path and boosts the performance. Version 9's deep compression helps the user to save disk space and further improve the performance, just like in my case. And this is particularly true for a warehouse database application system.

DB2 version 9 also changed to a thread-based model which made memory management more efficient and made memory-tuning automation possible compared to the process-based model in earlier releases.

And another feature is that DB2 introduced the HADR since version 8 and later releases which could be used to improve availability for mission-critical applications.

Now, all those changes have made DB2 LUW platform products more competitive in the relational database management world. All RMDBs are essentially doing the same thing, but DB2 costs are a lot less in many aspects compared to Oracle.

developerWorks: Nadir, general thoughts from you on DB2?

Doctor: I think one thing which I really like about DB2 is that IBM has been releasing these releases and the newer versions very quickly, like on an average every two or three years. And a lot of good enhancements in each of these, especially with DB2 9.7 ... the time-spent metrics can really help users to be able to do root cause analysis especially when they get performs bottlenecks. So that's one exciting feature I'm looking forward to really using.

developerWorks: Thank you for that, Nadir. And Michael, let's come back to you just for a quick closing thought on the value of the International DB2 Users Group which sponsored the contest that you all participated in.

Krafick: I guess you can say IDUG, especially for me being a newcomer or only discovering it two or three years ago, I didn't realize how much of an untapped resource it was.

I mean, you've got DB2 Listserv which has a connection to thousands of other DBAs like you. You've got the conferences where you're going to be able to learn about cutting-edge technology, you're going to hear speakers talk about what they did and how they worked around real problems, not problems written about in a book or a lab but practical issues that everybody runs into.

You have access to experts, being able to walk up at a conference and meet the author of the book you just bought or you trained for. You've got other experts to get to, you have certification. And just the methods that they communicate, whether it be through a regional group or Facebook or Twitter. I mean, it's just a huge untapped resource and any DBA needs to be linked into it.

developerWorks: Great endorsement for the International DB2 Users Group, thank you, Michael. And again, congratulations to all three of you for your winning essays in the "Do You DB2?" Contest that was sponsored by IDUG this summer. Michael Krafick, Sigen Chen, and Nadir Doctor. Thanks to all three of you.

Krafick: Thank you Scott.

Chen: Thank you very much.

developerWorks: For more information about the International DB2 Users Group visit idug.org, I-D-U-G.org. This has been the developerWorks podcast. I'm Scott Laningham. Talk to you next time.

What's new in the developerWorks community?

developerWorks: John Swanson has this week's newsletter feature. John, how are you doing?

Swanson: Hey, Scott, how's it going?

developerWorks: Very good. Very good. What's up this week?

Swanson: Well, the developerWorks newsletter is, as always, highlighting all the great stuff on the site. This week we're highlighting the Business Process Management Journal which is out of the WebSphere® zone, the BPM Journal, which, of course, Business Process Management which helps folks optimize their business processes. And of course, IBM offers its own BPM software and services. So the BPM Journal helps folks get to know BPM and IBM's BPM offerings.

This issue has, as usual, lots of articles, columns and tips including three articles on WebSphere Business Compass. In addition, this week Ted Neward is wrapping up — at least for the time being, wrapping up — his five things you didn't know about Java™ series with five things you didn't know about everyday Java tools. And I'm getting ready to go to the beach next week.

developerWorks: Sweet, that sounds like a great place to go, actually. You mentioned Ted Neward; there is, you probably have heard some of the podcasts in the Andy Glover technical Java series that we've been running. And Andy has a conversation with Ted coming up soon so, and Ted can talk too, man. He can go and go and go. [LAUGHTER]

So, but that will be a good one, so everybody should keep an eye out for that in the coming weeks as well.

Swanson: Yes, outstanding podcast series. Kudos to you, Scott.

developerWorks: developerWorks newsletters editor, John Swanson. Thanks, John.

Swanson: Take it easy, Scott.

developerWorks: Also new on developerWorks this week:

• From the XML zone, Schema scope: Primer and best practices: Understand a crucial aspect of schema design.
• From AIX and UNIX, Speaking UNIX: Managing multitudes of machines the mild-mannered way.
• From Information Management, Deploy IBM DB2 pureScale feature on IBM Power Systems.
• From the Java technology zone, 5 things you didn't know about ... everyday Java tools.
• And from Rational, Create and test IBM iWidgets using Rational Application Developer.

All of that and more at ibm.com/developerworks.

Resources

• Podcast blog
  
  

About the author

Scott Laningham, host of developerWorks podcasts, was previously editor of developerWorks newsletters. Prior to IBM, he was an award-winning reporter and director for news programming featured on Public Radio International, a freelance writer for the American Communications Foundation and CBS Radio, and a songwriter/musician.

Rate this article

Comments

Back to top

Table of contents

• What's new in the developerWorks community?
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise