Be a Hero the Easy Way : DB2 10.5 – Part II
June 13, 2013 Leave a comment
Bill Cole – Competitive Sales Specialist,Information Management, IBM
I admit it. I’m a DBA. I’ve been a production DBA answering calls at 2:00AM where some voice on the other end of the phone announces “there’s a problem in Production” or being awakened by a pager. Why do I tell you this? It’s because those bruises and scars inform what I write here. (Not to mention that my wife has also been Production technology, you have a better insight into what the products and features mean to your organization.
In the last entry, I talked about the BLU Acceleration features that make your DataWarehousing world better. Maybe even great. But not every application uses DB2 for glamorous things like data warehousing or business intelligence. Lots of your applications simply support the day-to-day business operations, the stuff that keeps the doors open and the lights on. Did DB2 10.5 leave out functionality for those apps? Not at all. There’s really good stuff in the new release for those applications, too.
The first point is that DB2 10.5 is simply faster in almost every case. That’s my kind of upgrade. Install it and reap the benefits of your weekend investment. No changes to your applications. No fighting with your developers or system admins. It’s just faster. We’ve done the benchmarks and we’re satisfied it’s faster. As those old adverts used to say, your mileage may vary…. Let us know if you experience something different. We want to know. We keep improving and adjusting the kernel to make your lives easier.
There’s these things we’re forced to do to make up for peculiarities in the data like creating extra columns with the names of people or streets or cities converted to UPPER. Or you have to add two numeric values to make a column.Why? To make searching easier/faster.
Well, that extra column requires extra maintenance in the application code, right? Not to mention the duplication of data so we’re using up disk real estate when we really shouldn’t be. So DB2 10.5 includes the ability to create an index on a function, thus, achieving the same thing as that extra column without the ugly side effects and performance problems.
CREATE INDEX funcindex1 ON customer (UPPER(city))
CREATE INDEX funcindex2 ON item (onhand + onorder)
Cool stuff, eh? You still have the index but you lose that extra column and all those cycles used to maintain it. In other lives, I’ve seen this used temporarily to improve performance for queries that are used only sporadically. It’s easy to create and drop these indexes and save the time/space associated with maintaining them. You can choose to manage them to suit the application and user’s needs.
Don’t you love VARCHAR columns? Defining them is always a challenge. The values in the column are typically less than 1024 characters but every once in a while we see values exceeding 4096 characters. But that impacts the page size because the row has to fit on one page. Hmmm. So we settle on the 1024 maximum size and hope we don’t lose any valuable data because of the design decision. Yet we know that the first time anyone stuffs data into this column it’ll be 2k worth of characters and we’re up the stream explaining how we lost information.
But wait! DB2 10.5 has a better answer – extended row size. That’s an interesting collection of words. What does it mean? Well, you can define the sizes of the columns in a row such that they would exceed the page size if they were all full.
In the example above, we’re using an 8k page (it’s the shop standard) and we need to create a table with two VARCHAR columns, “summary” and “example”. Both columns are free-form text that a user keys in during a transaction. Since we need to get all the information in one pass, we’ll limit the data to 4096 characters in each column. So now we’re exceeded the limits of an 8k page. Argh! However, with Extended Row Size, this isn’t a problem because the overflow data will be stored in a hidden LOB column. Okay, there may be some performance penalty for the rows that use this feature but you’re not limited by page size which means you don’t have to limit VARCHAR columns severely or design around an arbitrary page size.
This leads us to Oracle compatibility. We know many of you (okay, most of you) have Oracle databases lurking about. As you can see, we’ve added features that improve the portability of Oracle code. And we’re improving the reasons for you to move your applications to DB2 10.5. Our tests indicate we’ve reached 98+% compatibility. There are some specific places where we’re no compatible and those are fairly esoteric for the most part. But we’re working on those, too. It’s actual behavior versus published behavior.
So why convert? We have presentations and charts and spreadsheets that prove DB2 10.5 is less expensive to own and operate. My perception is a bit more personal because I’ve been there. DB2 doesn’t require constant care and tweaking and patching and fixing. DB2 is faster, too. You don’t have to spend your weekends and holidays at the office. You can go home before midnight. You can get your life back. And be a hero to the business. And your family!
Finally, in my first job decades ago, my IBM SE was working with me on a beautiful Saturday morning in Omaha. I started the re-IPL procedure and he put his hand over the keyboard and said “What in the world are you doing?” I told him that I was re-starting my System/370. “Really?” he said. “I’ve never done that.” A lot of things wandered through my head at that moment. And that incident has colored my relations with every customer. You know your world and how IBM’s products serve you better than we can. We supply the tools. Tell us what you’re doing with the tools.
Bill Cole on Twitter : @billcole_ibm
DB2 10.5 with BLU Acceleration is here! Download the Trial Code today!