Robert's Blog

Tuesday, April 1, 2008

Need a DB2 for LUW DBA? Check out this one from Toronto

The DBA I have in mind is DB2 itself - Version 9.5 for Linux, UNIX, and Windows, to be specific (and for those of you who don't know, IBM's Toronto Lab is the home of DB2 for LUW).

Yes, it's April 1. No, I'm not foolin'.

Autonomics, which in a DB2 context refers to the ability of the DBMS to manage itself, has gone to a whole new level with DB2 9.5. For starters, there are seven more configuration parameters that can be set to AUTOMATIC (meaning, let DB2 take care of it), brining the the total number of such parameters to twenty-four (if my counting is accurate). How much memory should be set aside for database system monitor data (mon_heap_sz)? Let DB2 figure that out. Ditto the number of buffers used for internal messaging (fcm_num_buffers). And the memory allocated to the lock list (locklist). And the memory used to cache sections of SQL and XQuery statements (pckcachesz). And the default prefetch size (dft_prefetch_sz). You can read more about these and all of the AUTOMATIC-settable configuration parameters in the DB2 9.5 Data Servers, Databases, and Database Objects Guide (see Chapter 20), one of several DB2 manuals that I have memorized verbatim (OK, that last bit is an April Fool's joke).

More configuration parameters that can be set to AUTOMATIC are just part of the DB2 9.5 automation proclamation. Other new-and-improved capabilities include real-time database statistics gathering (the more accurate this information is, the better the query optimizer can do its job), automatic compression dictionary creation (Deep Compression rocks), automatic recovery object removal (let DB2 get rid of old backup and recovery files that are no longer needed), and automated backup (which behaves according to a policy that's under your control). Much additional information is available in the DB2 9.5 What's New manual.

Now, I do DB2 consulting work, and given that, you might expect me to be less than keen about these autonomic advances. ["With DB2 getting so good at taking care of itself (sniff), who's going to want me?" (please, no comments from the cheap seats)]. In actuality, I'm a big fan of IBM's work in this area. Why? Because I believe that what's best for all DB2 people is a growing worldwide DB2 community, and a more self-managing DB2 helps to fuel that growth. With respect to swelling the ranks of DB2 users with folks who've worked previously with other DBMSs, DB2 9.5 Express-C is one of the best things ever to come along. It's a full-function DB2 that is free to download and deploy. Full-function means that it's not a watered-down version of the product - DB2 9.5 Express-C provides all the great autonomic features I've mentioned in this post, and more. Imagine if you use DB2 9.5 Express-C as your entree into the DB2 world, and you're doing this with a background of work with a different DBMS, either commercial or open-source. Would you like to spend your time trolling through the doc and the Web looking for "right" answers to configuration or administration or tuning questions, or would you like to leverage DB2's self-management capabilities to the hilt and get down to the business of seeing if the DB2 dog'll hunt (to use a phrase oft heard in the American South). I think that you'd go with the latter option. Dig into pureXML and advanced workload management and Deep Compression - not application heap size! And while you're at it, give Data Studio a whirl, and try your hand at creating some Web services for DB2 data access.

If you're an experienced DB2 person, and you're a little worried about the increasing ability of DB2 to manage itself, I have some advice for you. Chill out, and see this technology change for what it is: an opportunity for you to increase the value that you deliver to your employer (or your clients, if you're a consultant). See, you do more good by helping to get DB2-based applications built and managing information through its lifecycle and ensuring information-related regulatory compliance than you do by deciding when a database should be backed up or how large a sort pool should be. Do you have the ability to do that more advanced information management work? Though I may not even know you, I'd be inclined to say, "Yes, you have the ability to that." And now, thanks to advanced DB2 autonomics, you have the time to do it, as well.

Quick note for some Linux users: the self-tuning memory management capabilities of DB2 9.5 work in a very complete sense with Red Hat Enterprise Linux 5 (or later) and SUSE Linux 10 Service Pack 1 (or later). These are the two Linux builds that are certified with respect to support for a specification of AUTOMATIC for the instance_memory DB2 database manager configuration parameter. For other Linux builds, a specification of AUTOMATIC for instance_memory will cause the database configuration parameter database_memory to return a value of COMPUTED, even when the default value of AUTOMATIC was selected for database_memory by the user. This is so because most Linux operating system builds do not (at present) support the giving back to the OS image memory used but subsequently not needed by a subsystem such as DB2. Returning COMPUTED for database_memory has the effect of turning off self-tuning memory management.

That said, there is a very straightforward way around this restriction, if you're using a Linux other than the Red hat and SUSE versions previously mentioned: instead of specifying the default value of AUTOMATIC for the instance_memory database manager configuration parameter, provide a specific value (I'm personally comfortable with DB2 using 70-75% of the memory of a dedicated server, so I'd back a specification of 3 GB - expressed as 750,000 pages of 4 KB each - for a Linux server with 4 GB of memory). Then the default value of AUTOMATIC for the database configuration parameter database_memory is valid, and self-tuning memory management can be activated.

Quick plug: I'll be teaching a one-day seminar, "SOA in the Real DB2 World," at the 2008 IDUG North American Conference in Dallas this May. If you register for the conference, I hope that you'll register for my seminar, as well (you can also register ONLY for a one-day seminar, without registering for the rest of the conference).


Post a Comment

Subscribe to Post Comments [Atom]

<< Home