Part 1  |  Part 2  |  Part 3

Recently, DBAzine had the opportunity to talk with several of the leading experts working with DB2 today. This group of DB2 professionals has a wide range of different experiences with DB2 and unique perspectives on the ever-changing DB2 technical environment. Our conversation was directed by several questions — questions about DB2's past, present, and future.

This distinguished group was made up of the following experts:

Roger Miller — Roger is the lead DB2 for z/OS strategist, but he started in DB2 as a programmer. He can be reached at millerrl@us.ibm.com.

Betty Gray — Betty is an independent DB2 educator and consultant. She can be reached at maiilto:wolf_gray@msn.com.

Alex Robb — Alex is the Director of DB2 Curriculum for Themis Inc. He can be reached at k_arobb@mindspring.com.

Linda Ball — Linda was the Corporate Architect for DB2 for OS/390 and z/OS Product Development at BMC Software.

Randy Ebersole — Randy is a Senior IT Specialist for DB2 UDB for z/OS at IBM. He can be reached at ebersole@us.ibm.com.

Craig S. Mullins — Craig is Director of Technology Planning and a data management strategist with BMC Software. He can be reached at www.CraigSMullins.com.

Now that you've seen the lineup, let's jump right into the questions and answers.

DBAzine: First, choose one feature of DB2 that you feel is the least understood (or most misunderstood) and tell me why you think this is so? Are there benefits that could be achieved by developers or DBAs if this feature were more widely implemented?

Roger Miller: I'd say that the most misunderstood feature is probably locking. Locking is an extremely complex area that is changing with every version of DB2. It has changed substantially since its implementation. Many customers know how it "used to work." If customers can get a better understanding of how locking works now, most shops could expect the following:

• Drop CPU time by 30 percent
• Decrease run times, perhaps up to as much as five times faster in some unusual cases
• Reduce outages

Also, utilities would be able to run with less disruption to other work.

This example is less about locking than about long running jobs with lots of updates, but it is related. For example, a customer was having problems with storage and with extremely long run units. Like the Energizer Bunny, they kept going and going and going, and going. The result was that eventually DB2 was using more than two GB of memory and abended, causing two hours of down time on a key part of the customer's web site.

As far as an underutilized feature, I think that has to be Referential Integrity. Some people think they can out perform it on their own. Sometimes it's true. But if they don't do all the checking, they can get into big problems. If shops used RI more often, we'd have better data integrity, more consistent information, and more usable data for a spectrum of uses.

Alex Robb: I think the most misunderstood feature is "Optimize for n Rows." People think it is a Yes/No option. The big benefit this feature offers is the way remote data is transmitted and synchronized. I still run into students who think "Optimize for n Rows" means they are calculating the number of rows they're going to retrieve, when it really controls the number of rows they are transmitting.

The most misused feature has to be Data Sharing. It's misuse greatly affects the locking and synchronization of remote systems.

Randy Ebersole: In my opinion, the most misunderstood feature is dynamic SQL. The reason is that there is still a belief that it requires a lot of overhead, can cause performance issues, and is hard to work with. Since its introduction, DB2 has grown tremendously, and dynamic SQL has become much friendlier. If managed properly, there is very little difference between dynamic SQL and static SQL. Why would a shop want to use it? Well, it provides another capability/option for development. In addition, it can also deliver more flexibility in some cases than static SQL. Many of the customers I visit won't look at it again because they feel it's old news. Often they are just not aware of the new options. If you want to learn more about these options, there is a white paper on the IBM site called "ODBC Applications and Callable Interfaces." Also, a new red book came out in August 2002 with an article called "Squeezing the Most out of Dynamic SQL."

Betty Gray: In my experience it has to be lock avoidance. Development managers have got to learn to encourage their programmers to eliminate all ambiguous cursors. By simply doing this, they can realize enormous performance results. For example, a client of mine had a program running every night that took eight hours to complete. The only change they made in the program was to declare all the cursors explicitly FOR READ ONLY. The program then ran in under an hour — simple change-big results.

Craig S. Mullins: Do I have to pick just one? There are so many misunderstood and misused aspects of DB2 that it would be hard to choose only one. I mean, just look at the wide variety of responses given by Roger, Alex, Randy, and Betty! So, I think I'm going to have to say that the biggest problem in DB2-land is an overall pervasive lack of knowledge of what DB2 can do. That is not an indictment of the technicians using DB2 — this situation has come about because DBMSs are becoming large and unwieldy as they suck up functionality that previously was performed outside of the database environment. And new versions of DB2 typically add a myriad of features while removing none (or just a few as was the case for DB2 V6). There is simply too much functionality in DB2 for anyone to be an expert on all of it (well, with this possible exception of Roger).

Just look at all of the new SQL functionality introduced over the past two or three versions of DB2. Features like scrollable cursors, row expressions, and external SAVEPOINTs are new with DB2 V7 and it is to be expected that they be underutilized because of their relative newness. But outer joins, CASE expressions, stored procedures, triggers, and user-defined functions have been around a lot longer and many do not use them - worse, many do not even know that DB2 supports these features.

Another common confusion seems to be a lack of understanding around DB2 dates and date arithmetic. I answer DB2 questions for the search390.com and searchdatabase.com web sites. An inordinate number of questions there are related to DB2 date usage. See my recent article (http://www.dbazine.com/mullins_qa.html) that summarizes some of these misunderstandings for more insight.

DBAzine: Next, LOBs have been available since Version 6 of DB2 but they seem to be underutilized. Is that a fair assessment? If so, when do you think they will be more widely utilized and what are the barriers to their acceptance? If not, what are people using them for and in which applications and types of businesses are they use most prevalently? Do you know of any efforts being made (by IBM or ISVs) to make them easier to manage and administer?

Roger Miller: Yes, Lobs do seem underutilized. For them to be more widely utilized, some of the applications would need to be rewritten. (A recent IBM Red Book is about Lobs, titled Large Objects with DB2 for z/OS and OS/390, SG24-6571). In my opinion, the main barrier to their use is education and application. Traditional programmers don't have a lot of requirements for Lobs In order for a programmer to need to use Lobs, the object needs to be big and needs to be shared. Most programmers aren't going to be putting up their latest vacation pictures into a database-and remember I said "most"-there are always some exceptions to the rule. Sharing on the web in files is fine. But really, most Lobs are really SLOBS - that is, Small, Large Objects — from 100 kilobytes to a few megabytes. Until the business requirements change to needing real Lobs, we'll probably see Lobs as underutilized. Recent Redbooks that cover this topic include:

• Practical Guide to DB2 Data Replication V8, SG24-6828
• Squeezing the Most Out of Dynamic SQL, SG24-6418
• Large Objects with DB2 for z/OS and OS/390, SG24-6571
• Version 7 Selected Performance Topics, SG24-6894
• Version 7 Performance Topics, SG24-6129
• Version 7 Using the Utilities Suite, SG24-6289
• Version 7 Presentation Guide, SG24-6121
• Performance Management Tools, SG24-6508
• Application Programming Topics, SG24-6300

Alex Robb: I agree with Roger that Lobs have been underutilized. But what I see is a time in the near future-maybe about four years out-where they will be much more widely utilized. The reasons I think this are twofold:

1. Hardware capability limitation. IBM is just coming out with the Z900 machines. If you look at the current machine Z800, you can see where the current hardware problem is. Lobs can go up to two gigabytes. There is a limit in VSAM — you can only have a 64-gig file, up to 254 files to make a table. Worst case scenario — could only have 8096 objects. Severe limit for customers with millions and millions of rows, like mainframe customers. The new machine will remove limits, and DASD will advance accordingly.
2. Specific use. What do shops need this capability for today — not much. But I can see soon the possibility of using Lobs for identification purposes. For example, companies may want to store photo ID, fingerprints, and maybe even DNA sequences. My clients in the insurance industry tell me this is already becoming a big issue for them. These companies can now run DNA sequencing of their members and determine if someone is predisposed for a certain disease. Here's the big question — just because they can, should they? And if they know someone could develop a life threatening disease in the future, should they increase their rates, like they do for smokers, the obese, diabetes today? The need to answer big questions like these will drive the need for Lobs in the database. But there are no easy answers.

Randy Ebersole: I also agree they have been underutilized. I think the main barrier is finding the application in which to use Lobs I can foresee using them in applications that need to store non-uniform data. For example, insurance companies want and need to keep documents you fill out. It would be a great advantage to them to be able to keep these documents online. With the right interface, companies could keep these forms online and still access the data on them. This would hold true with any documents you fill out — insurance policies, medical forms, mortgage applications, any contract — the list is endless. I think this area is just waiting to explode in its exploitation.

This is possible with the functionality in DB2 that now allows the documents to be searched. In order to use this functionality, a shop will have to develop a "different" mindset. They will have to change the way they are currently doing business. Currently, the information from many forms is re-keyed into a database. This would no longer be necessary.

Betty Gray: The only application I've seen my clients using Lobs for are Web enablement applications. I am aware of several shops that have tried Lobs just for grins, but currently they are only using them in test applications.

Linda Ball: I think Lobs were implemented in a basic fashion. Since the primary reason for large chunks of data is to hold graphics or images of documents, there will need to be more conversions and easier ways to load, change and access them than the current structure allows. The implementation allows these objects to be extensions of traditional rows and the access to the other columns continues with almost no performance impact. This allows a flexibility that is good but more support for application development with Lobs will enhance their use. Currently, compression is also assumed to take place elsewhere (or example, in JPEG or GIF compression). For large text objects, some customers may want DB2 to create compression dictionaries. BMC Software is working with individual customers on their specific needs for management of these objects. This depends on their planned 'life cycle' for these columns. Some key questions that will need to be addressed include:

• Will they need to LOAD or UNLOAD them?
• Will they need to COPY and RECOVER them?
• Will they be "read only" once loaded?
• Will they be accessed in only some rare situations?

I have seen one company present a technique for using Lobs as an overflow technique so that they can allow documents of any size. Those that fit in 8K pages are handled normally, but any size can be handled by having the application access a LOB column.

Craig S. Mullins: I agree with everything said by colleagues and would like to add one more driving factor for the future expansion of LOB usage — and that is XML. Organizations are increasingly using XML as a means to transfer data among and between business units and other organizations. As this data finds its way into DB2, some companies will choose to store these XML documents as CLOB columns. Storing an XML document in tact — that is, not breaking it up and storing it in multiple columns — will likely require CLOBs instead of VARCHAR columns due to the large size of most XML documents.

Interviews conducted by Elizabeth A. Mullins.

Part 2: the panel of experts discusses DB2's viability as an Internet database platform, whether ERP and CRM application vendors get special consideration in developing new DB2 versions, and more.