A Note on DB2 for z/OS Page-Fixed Buffer Pools

I've recently had the occasion to give DB2 for z/OS page-fixed buffers a spin, and I've been pleased by the results I've seen. In this post I want to give you some information about this relatively new mainframe DB2 feature.

The ability to page-fix DB2 buffer pools was introduced with DB2 for z/OS V8, via the PGFIX option of the -ALTER BUFFERPOOL command. The default value of the parameter is NO, which gives you the business-as-usual situation with regard to the way in which z/OS treats real storage page frames occupied by DB2 buffers. [Page frames are like cubbyholes within the real storage resource managed by z/OS on a mainframe or a mainframe logical partition (LPAR). Real storage, in turn, is just a term for mainframe server memory -- so called to distinguish it from virtual storage.] Generally speaking, z/OS retains the right to move something out of a real storage page frame and into auxiliary storage (the latter likely being some files in the disk subsystem). z/OS does this so that it can support an amount of virtual storage, spread across multiple address spaces, that exceeds the real storage available on the system. Because they are usually referenced quite frequently, DB2 buffers in real storage page frames are not likely to be shipped off to auxiliary storage (z/OS tends to steal page frames occupied by pages that have gone a while without being referenced. That said, it's possible that a page frame occupied by a DB2 buffer could be stolen by z/OS.

Because a DB2 buffer could be moved out of real storage by z/OS, DB2 has to do something to ensure that the buffer will NOT be stolen in the midst of an I/O operation (i.e., while DB2 is in the process of reading a page into the buffer from the disk subsystem, or externalizing a previously updated page in the buffer to disk). DB2 does this by asking z/OS to fix (in other words, make non-stealable) the page frame holding the buffer, until the related I/O operation has completed. DB2 subsequently tells z/OS that the page frame can revert back to stealable status, and the previously fixed buffer becomes unfixed from a real storage perspective.

z/OS is very good at assigning to tasks (meaning, dispatchable pieces of work in the system) the costs associated with services that it performs on behalf of said tasks, and so it is that the CPU cost of DB2 buffer page fix and unfix requests is assigned to the DB2 database services address space (aka DBM1) for asynchronous (i.e., prefetch) read I/Os and database writes, and to allied address spaces (through which SQL statements get to DB2 -- examples include the DB2 DDF address space, CICS application-owning regions, and batch initiators) for synchronous (single-page) read I/Os. The CPU cost of fixing and unfixing a page frame is pretty small, but when disk I/O operations related to a buffer pool number in the hundreds per second, the cumulative cost adds up.

Enter buffer page-fixing. When PGFIX(YES) is specified for a buffer pool, the page frames that hold the pool's buffers will be fixed in real storage (this is sometimes referred to as V=R, which is short for virtual storage = real storage). Because these pages can't be stolen, there is no need for DB2 to issue fix and unfix requests, and CPU consumption is reduced (again, for both the DB2 DBM1 address space and allied address spaces associated with SQL statements that target objects assigned to the buffer pool in question).

Sounds good, eh? You might wonder why the option to page-fix buffer pools wasn't delivered before DB2 for z/OS V8. My thinking is that the capability would not have been very useful prior to DB2's exploitation of 64-bit virtual and real storage addressing, something else delivered with V8. When the maximum amount of real storage addressable by z/OS was 2GB, you probably wouldn't have wanted to page-fix DB2 buffer pools for fear of putting too much stress on other users of mainframe memory (CICS, batch, TSO, etc.). Now that people are running DB2 V8 and V9 on system Z servers with scores of gigabytes of real storage, using PGFIX(YES) to fix a chunk for the purpose of reducing the CPU consumption of a DB2 workload seems like a very reasonable proposition.

Now, do you want to page-fix all of your DB2 buffer pools? Probably not. I'd lean towards using it for pools that have the most associated I/O activity (you can check this out using your favorite DB2 monitor, or by way of the good old DB2 -DISPLAY BUFFERPOOL command (what I like to do is issue the command - DISPLAY BUFFERPOOL(ACTIVE) DETAIL(INTERVAL), and then issue the same command 60 minutes later to get an hour's worth of activity). Also, make sure that the z/OS LPAR on which the DB2 subsystem is running has a sufficiency of real storage.

One other thing: page-fixing a buffer pool has nothing to do with fixing the contents of the buffers. DB2 manages the moving of pages into buffers, and the replacement of pages already in buffers with other pages (buffer stealing), for a page-fixed buffer pool as it would for a non-fixed pool. Fixing a pool's buffers in mainframe memory and "pinning" an object, in its entirety, in a buffer pool are two very different things. Page-fixing a buffer pool isn't about keeping more data and index pages cached in server memory -- it's about caching the same amount of stuff in a more CPU-efficient manner.

So, take a look at your buffer pool configuration, and give serious consideration to page-fixing the pools that have the most associated I/O activity.

DB2 for z/OS People: Don't be Memory Misers

When I first started working with DB2 on the mainframe platform, back in the mid-1980s, plenty of people were still running a version of the operating system called MVS/370. That OS limited the size of an address space to 16 MB, and the amount that a subsystem such as DB2 actually had to work with was less than that, since some megabytes were occupied by system code and by common storage areas (i.e., areas of virtual storage that were shared across address spaces). Since server memory was tight, people were frugal in their use of it. Early on, it was common to find organizations running DB2 in production with a total buffer pool configuration of 1000 4K buffers (4 MB of virtual storage) -- often allocated to a single pool, BP0.

MVS/370 was supplanted in the late 1980s by MVS/XA, which upped the maximum address space size to a whopping 2 GB (the XA in the OS name stood for eXtended Addressing). Though the amount of memory available to a DB2 subsystem had expanded by more than two orders of magnitude, many mainframe systems programmers were hesitant to significantly increase the size of DB2 buffer pools. I vividly recall seeing eyes widen when I told people that a DB2 subsystem running under MVS/XA should have 10,000 buffers in the pool at a bare minimum. 10,000 buffers! That's 40 MB! True, but 40 MB was peanuts in a 2 GB address space. On top of that, DB2 was designed to utilize large server memory resources to maximum advantage: the more virtual storage you gave it, the better it performed. Still, widespread acceptance of big buffer pools (meaning at least several hundred megabytes) was slow in coming.

Jump ahead to the late 00s (or whatever you call our present decade), and you see a repeat of that old treat-mainframe-memory-like-gold mindset. This time around, the big change was 64-bit hardware and virtual storage addressing on the Z platform, initially exploited by DB2 for z/OS Version 8. Organizations now have System Z servers with dozens of gigabytes of central storage, on which they have production DB2 V8 and V9 subsystems running with buffer pool configurations that use a gigabyte (or even less) of virtual storage for buffers. Come on, people! As was true when MVS/XA hit the scene, DB2 doesn't just tolerate large address spaces - it THRIVES in large address spaces. Here's a modest proposition for you: if you have a production DB2 V8 or V9 subsystem running in a z/OS LPAR (logical partition) that has a central storage resource of X gigabytes, think in terms of using at least 25% of X for DB2 buffers. Recall that folks routinely used 70-80% of a 2GB address space for DB2 buffers. Twenty-five percent is conservative. So, if your DB2-hosting z/OS LPAR has 40 GB of central storage, go for a 10 GB buffer pool configuration for starters, and be ready to expand further from there.

If you're planning on growing your DB2 buffer pool configuration to leverage 64-bit mainframe storage technology, should you do this by just enlarging all of your existing pools (assuming that you are using multiple pools -- and you should be -- for different categories of database objects)? No! Be smart about buffer pool expansion, and add buffers to pools in a way that will give you the biggest bang for your central storage buck. Your principal aim in bulking up buffer pools should be to reduce the rate of DB2 disk I/O operations. If you have two pools of equal size, and the rate of synchronous read I/Os (and/or the rate of prefetch I/Os -- both are important) is 100 per second for pool A and 5 per second for pool B, add buffers to pool A (and if you decide to make both pools bigger, add more buffers to A than to B). The rationale for focusing on disk I/O is simple: doing so gives you a double-barreled benefit: fewer disk I/Os means reduced elapsed times for DB2 queries and DB2-accessing programs (these are typically I/O-bound) AND reduced CPU consumption for a given DB2 workload (it doesn't take much CPU time to drive a disk I/O operation, but a small amount of CPU processing resource expended a zillion times can really add up). You can get I/O rates for DB2 buffer pools from several sources, including DB2 monitor products and the good old DB2 DISPLAY BUFFERPOOL command.

Something else to keep in mind: mainframe memory tends to cost less than mainframe MIPS. If you want to boost DB2 throughput, leverage the central storage resource at hand before adding engines.

Something else to keep in mind: fewer I/Os does NOT mean fewer GETPAGEs (a GETPAGE is a DB2 request to view a table or index page). Fewer I/O operations translates into CPU savings, but if you want to take a really big bite out of the CPU cost of a DB2 query or program or workload, you need to achieve a big reduction in GETPAGEs. That is basically a matter of access path analysis and associated query and/or database design changes. And that is a subject for another post. Till then, don't be shy about using more mainframe central storage for DB2 (V8 and above) buffer pools.

For Mainframers Interested in DB2 for Linux/UNIX/Windows...

Like many a veteran IT person, I knew a lot about DB2 for z/OS before I ever touched DB2 for Linux, UNIX, and Windows (DB2 for LUW was several years from existence when I started working with DB2 for the mainframe platform in 1986). By the late 1990s, DB2 was getting to be pretty interesting from my perspective, due to its growing sophistication and robustness (and due also to the enthusiasm of young colleagues of mine on IBM's DB2 National Technical Support team, such as Monty Wright and Michael Logan -- both still DB2 gurus in IBM's employ).

When I joined CheckFree Corporation (now part of FiServ) in 2000 (thereby moving over to the user side of the DB2 community), opportunities for gaining DB2 for LUW knowledge expanded: shortly after I came on board, CheckFree decided to implement its enterprise data warehouse on a DB2 for AIX (with the Data Partitioning Feature) platform, and not long after that the Company's CRM application was migrated to PeopleSoft with a DB2 for Solaris database. Both of these databases went relatively quickly into multi-terabyte territory, and I had a lot of fun learning about the technology (annual CheckFree visits to the Executive Briefing Center at IBM's Toronto Lab were highlights).

When I finally scratched my entrepreneurial itch by starting my own company last summer, I continued to work with DB2 for LUW as well as DB2 for z/OS. Take it from me: if you are a mainframe DB2 person, you can become productive as a DB2 for LUW person if you want to. In this post I'll share with you a few of the things I learned in the course of becoming a cross-platform DB2 practitioner; to wit:

• Run it on your own system. Not all of us have our own mainframes. One thing that I really like about DB2 for LUW is having it on my laptop, in two flavors, to boot: under Windows, and also under Linux in a virtual machine. For me, DB2 9.5 Express-C is the ideal choice for my PC. The free version has just about everything with the exception of HADR (High Availability Disaster Recovery, an advanced failover clustering technology) and built-in data replication capability (these features are available with the DB2 Express-C paid subscription option).
• Get a compact Linux helper guide (if you decide to run DB2 under Linux). There are several of these little books from different publishers. You don't have to be a Linux jock to use DB2 for Linux, but a small-footprint reference is nice to have when you need to look up a command to invoke an editor or create a directory or display the contents of a file.
• Get some freeware for access to Linux from Windows (again, if you run DB2 under Linux). PuTTY is a nifty open-source program that enables you to securely log in to a remote Linux server (such as one on which DB2 is installed) from a Windows system. WinSCP, another piece of open-source software, makes it easy to move files between Windows and Linux systems. If you want to be able to use, on your local Windows system, a server-side DB2 GUI (such as the Setup Wizard that can be used to install DB2) that runs on a remote Linux system, a free program called Cygwin/X is your ticket. [This is NOT needed to use a client-side DB2 GUI such as the Control Center, which runs on YOUR system and enables you to interact with a remote DB2.]
• Get comfortable with the file system. As far as I'm concerned, the biggest difference between DB2 for z/OS and DB2 for LUW, from an administrative perspective, is the need to know more about the file system when you're using the latter. At one time, mainframe DB2 DBAs had to know something about Access Method Services, the operating system component used to create the VSAM files used by DB2. Nowadays, just about all mainframe DB2 installations use DB2-defined objects via STOGROUPs, and people let DB2 handle the Access Method Services stuff. Not only that, but it's now common practice to have z/OS System Managed Storage take care of placement (within the disk subsystem) for DB2 tablespace and index files through the use of an asterisk as the volume designator on CREATE STOGROUP statements. While DB2 for LUW has made great strides in reducing one's need to deal with file-related plumbing (especially with the automatic storage feature introduced with DB2 9.1 for LUW), you still have to know more about files on this platform versus System Z. Don't be intimidated. You can pretty quickly learn enough about drive letters in Windows and file paths in Linux environments to get you by.
• FixPacks versus PUT tapes. DB2 maintenance is packaged differently on LUW systems. One thing I learned early on: installing a DB2 for LUW FixPack is a two-step process. Know about the installFixPack and db2iupdt commands.
• Parameters here, parameters there. On System Z, you have DB2 ZPARMs, and the technical term "database" is not much beyond a designation for a set of tables. On LUW systems, you have instance-level DB2 parameters and database-level parameters. An example of the former is the agent pool size (don't worry -- it can be set to AUTOMATIC), and an example of the latter is the log file size (each database -- and there can be several under one DB2 for LUW instance -- has its own transaction log files).
• Google, baby! Need to untar a file? Need to know what "gzip" means? Need to know how to change the behavior of SELinux? The answers are out there, my friends, and Google gets you to them PDQ. No kidding: when in doubt, use the Great Search Engine, and chances are you'll see that the same questions have been asked by others -- and answered!

There's more, of course, but I mostly want you to know that what you need to learn CAN be learned, and learned readily. Also, know that the great bulk of your DB2 for z/OS knowledge is directly applicable to the DB2 for LUW world. SQL programming is virtually identical on Z and LUW. In both places you have tablespaces, and bufferpools, and archive logs, and catalog tables (actually, catalog views on LUW), and clustering indexes, and range-partitioned tables, and online REORGs, and RUNSTATs, and prefetch, and query parallelism, and materialized query tables, and so on and so on. They really are much more alike than they are different. So, broaden your DB2 horizons, and get to know DB2 for LUW. It's fun to do, and who knows? It could open some new career doors for you.

"What is DB2 data sharing?"

When data sharing was delivered with DB2 for z/OS (OS/390 at the time) Version 4, more than ten years ago, I was part of IBM's DB2 National Technical Support team at the Dallas Systems Center. I was tasked at the time with learning all that I could about data sharing, so that I could help IBM customers (and IBMers) understand, implement, and successfully use the technology. That was a really fun time in my IT career because people had lots of questions about DB2 data sharing, and I very much enjoyed answering those questions (sometimes in-person at locations in Asia, Australia, Europe, and throughout the USA).

Over time, as DB2 data sharing knowledge became more widely diffused, and as people such as my good friend Bryce Krohn shouldered more of the knowledge-transfer load, there were fewer questions for me to answer -- thus my delight in fielding a query a couple of weeks ago. The questioner was the general manager of a company that provides IT training services, and he and I were talking about a request from one of his clients for data sharing education. "So," asked Mr. General Manager, "what is DB2 data sharing?"

[Before I tell you what I told the GM, I'll provide a brief technical response to the question. Data sharing is a shared-disk scale-out solution for DB2 running on an IBM mainframe cluster configuration called a parallel sysplex. In a data sharing group, multiple DB2 subsystems co-own and have concurrent read/write access to a database (there is also a single catalog -- DB2's metadata repository -- that is shared by the DB2 subsystems in the group). Specialized devices called coupling facilities -- existing as standalone boxes and/or as logical partitions within mainframe servers -- provide the shared memory resources in which group buffepools (for data coherency protection) and the global lock structure reside (the latter provides concurrency control and data integrity preservation).]

I thought for a second or two, and told the GM: "DB2 data sharing is the most highly available, highly scalable data-serving platform on the market."

Now, it's been more than eight years since I last worked for IBM, so that wasn't a sales line. It is, I think, a factually correct answer. Consider those two qualities, availability and scalability. The thing about data sharing is, you start with the highest-availability standalone data-serving platform -- an IBM mainframe running the z/OS operating system -- and you make it even MORE highly available by clustering it in a shared-disk configuration. Does anyone dispute my contention that DB2 on a mainframe is the alpha dog when it comes to uptime in a non-clustered environment? Seems to me that fans of competing platforms speak of "mainframe-like" availability. The mainframe is still the gold standard with respect to rock-solid reliability -- the platform against which others are measure d. It's legendary for its ability to stay up under the most extreme loads, able to cruise along for extended periods of time at utilization levels well over 90%, with mixed and widely-varying workloads, keeping a huge number of balls in the air and not dropping a single one. You put several of those babies in a shared-disk cluster, and now you have a system that lets you apply maintenance (hardware or software) without a maintenance window. It also limits the scope of component failures to a remarkable extent, and recovers so quickly from same that a DB2 subsystem or a z/OS image or a mainframe server can crash (and keep in mind that such events are quite rare) and the situation can be resolved (typically in an automated fashion -- the system restores itself in most cases) with users never being aware that a problem occurred (and I'm talking about the real world -- I have first-hand experience in this area).

How about scalability? You might think that the overhead of DB2 data sharing would reach unacceptable levels once you get to four or five servers in a parallel sysplex, but you'd be wrong there. The CPU cost of data sharing is often around 10% or so in a 2-way data sharing group (meaning that an SQL statement running in a DB2 data sharing group might consume 10% more CPU time than the same statement running in a standalone DB2 environment). Add a third DB2 to the group, and you might add another half percentage point of overhead. Add a fourth member, and again overhead is likely to increase by less than one percent. So it goes, with a fifth DB2 member, a sixth, a seventh, an eighth, a ninth -- the increase in overall system capacity is almost linear with respect to the addition of an extra server's cycles. Again, I'm talking about the real world, in which organizations treat a data sharing group like a single-image system and let the parallel sysplex itself distribute work across servers in a load-balancing way, with no attempt made to establish "affinities" between certain servers and certain parts of the shared database in order to reduce overhead. Intense inter-DB2 read/write interest in database objects (tables and indexes) is the norm in data sharing systems I've worked on, and that's so because DB2 (and z/OS and Coupling Facility Control Code) handles that kind of environment very well. DB2 and parallel sysplex architecture allow for the coupling of up to 32 mainframes in one data-sharing cluster, and no one has come close to needing that amount of compute power to handle a data-serving workload (keep in mind that the capacity of individual mainframes continues to grow at an impressive clip).

Hey, there are plenty of good data-serving platforms out there, but when it comes to the ultimate in availability and scalability, the king of the hill is a DB2 for z/OS data sharing group on a parallel sysplex.

Any questions?

"Know the Data"

I received the other day a very interesting e-mail from Vijay Sitaram, a friend of mine who is a DB2 DBA (or not -- more on this later) at an organization that operates (no pun intended) in the health-care industry. Vijay's note contained some illuminating insights regarding the role of database professionals within a modern, strategically focused enterprise, and via this post I want to share some of these insights with you (my thanks to Vijay for permission to do so).

Recently, Vijay had found that traditional DB2 DBA work was becoming less satisfying for him, for reasons well-known to many DB2 people:

1. DB2 is becoming, more and more, a self-managing DBMS. This is a cross-platform truism. DB2 for z/OS people have for years benefited from advanced file-management features available on that platform (DB2-managed objects are the norm in mainframe environments), and DB2-managed storage capabilities have advanced in a major way via the "Viper" releases of DB2 (Versions 9.1 and 9.5) for Linux, UNIX, and Windows (aka LUW) servers. Similarly, self-tuning memory management -- a technology found now in DB2 9.1 and 9.5 for LUW, providing automated (and dynamic) sizing for things such as the database monitor heap, the agent pool, the package cache, the sort heap, and buffer pools -- is starting to show up in DB2 for z/OS (auto-adjustment of buffer pool sizes is an option available in DB2 9 for z/OS, and I expect to see more developments in this area in future releases).
2. While no vendor is giving server hardware away, the continuing decline in the price of compute power (and server memory) has more and more organizations asking DB2 DBAs to spend proportionally more time on work that is NOT directly related to boosting the CPU- and memory-efficiency of a DB2 workload. Sure, performance tuning is still important, but the value of saving X% of a server's CPU capacity, or reducing DB2's virtual storage footprint by Y megabytes, is not what it was a few years ago.
3. A lot of the DBA work related to DB2 system performance is front-loaded with respect to the life of a system. In other words, once a DB2 system has been effectively tuned and stabilized, over time it tends to require less DBA effort to keep it running well.

So, Vijay was thinking about how he could deliver greater value to his organization as a database professional. His manager provided the opportunity to do just that with this simple directive (paraphrasing a bit here, but this is the gist of what the manager said): "Know the data." What Vijay's manager wanted him to do was deepen his understanding of the data in the DB2 database that he administered (in this case, we're talking about medical billing data). The manager was NOT asking Vijay to know things like the number of rows in a table, or the length of a row in a table, or the cardinality of a column value. That's data about data. The manager was asking Vijay to know the data itself, and to appreciate "the value [that] is in the data." Vijay had already done a lot for his company in looking after an important database. Now he was being told that he could do more for the company if he could answer this question: what is the nature of the data in the database, and why and in what ways is it important to the organization?

Not being one to drop a ball, Vijay took this one and ran with it. He immersed himself in learning about the data elements in the database and the relationships between these elements. He built up his knowledge of how medical billing works (no easy task that -- I've worked with medical billing data, and it is very complex stuff), getting a lot of help from the senior programmer who developed the company's medical billing application. Vijay found that his deeper understanding of the data had some immediate payoffs, in that he was able to improve the performance of the ETL (extract/transform/load) processes used to update the database. He also found that his data knowledge made it much easier for him to successfully address database design issues.

Now, Vijay is helping his company with the implementation and deployment of data integration and data cleansing tools, and he's about to get into advanced data analysis software in a big way, focusing on cubing capabilities that will enable data users to drill into data structures to uncover actionable intelligence. In short, knowing how his organization values the data he administers, Vijay is working to grow that value by making it easier for users to access and analyze the data to improve decision making. In doing that, Vijay is increasing his value to his company. A side benefit: he's having more fun on the job.

Is Vijay still a DBA? He's not so sure that the "DBA" title reflects the kind of work he's doing now (and it's not as though he no longer does traditional DBA work -- it's more like "DBA-plus" these days). Vijay has asked that his job title be changed to Architect. If that decision were mine to make, I'd give it a thumbs-up. Drive on, Vijay.

When Things Old Are New Again

Due to my having been very busy with a client engagement, I've let more time than usual go by since the last post to my blog. The aforementioned client job provided me with an interesting experience in the form of a Q&A session between one of the organization's DB2 DBAs and a group of database-focused developers. Executives at this company had decided not long ago that DB2 would be the data-serving platform of choice going forward, and the developers in the room had long worked with a different DBMS used by the firm. Among the questions asked were some aimed at understanding the reasons for the company's strategic shift with regard to database technology. Why DB2 versus the other DBMS that they knew so well? The DB2 DBA did a great job in responding to these questions, and I found his answers to be quite interesting.

Often, when those of us who've worked with DB2 for a long time think about the product and what makes it particularly attractive in today's market, we call to mind recently announced features that score well in terms of the "cool" factor. How would the DBA reply to the "Why DB2?" question? Would he talk about the pureXML technology that makes DB2 the best-of-breed product for managing both XML documents and traditional relational data? Would he point to the Deep Compression feature that can slash disk space requirements for a database at a surprisingly low CPU cost? Maybe multi-dimensional clustering, a capability that can deliver breakthrough performance results for data that aligns naturally with several different dimensions (e.g., geography, time period, and product category)? Or online, in-place REORG, which gets data order back right with no need for "shadow" tables? No, no, no, and nope (the last of these a nod to my Texas roots).

With all that way cool DB2 stuff available to jazz up his response, the DBA chose instead to emphasize a few of DB2's bedrock qualities that some of us take for granted: a fantastic SQL statement optimizer, tremendous vertical scalability, and manageability. In speaking of these key advantages, the DBA made a compelling case for DB2. Hereinafter, I'll summarize his message to his application development colleagues.

First, the optimizer. Pity the IBM teams in Toronto and San Jose who develop this key component of DB2. They are probably accustomed to hearing from DB2 users who are upset over one of those rare occasions when the optimizer actually chooses a demonstrably suboptimal data access path (and quite often THAT ends up being due to inaccurate or incomplete database statistics in the DB2 catalog). People don't talk about the DB2 optimizer when it works as it's supposed to, so they end up hardly talking about it at all because it just about always does the right thing. To the question-answering DBA, however, DB2's world-class optimizer meant that he and his colleagues could spend lots more time with matters such as database design and deployment, and lots less time trying to tweak queries to get good performance. That the SQL optimizer would be touted as one of DB2's greatest strengths should come as no surprise to those who know the history product. Dr. Pat Selinger and her team at IBM's Almaden Research Center pioneered the cost-based optimization of SQL statements back in the late 1970s, when few people in the larger IT community had even heard the term "relational database." With a lead like that, it shouldn't be too hard to remain the front-runner, but IBM of course never relaxes when it comes to making the DB2 optimizer better and better. The DBA in the Q&A session actually did spend some time talking about one of the more recent optimizer-related features to have come out of DB2 development: materialized query tables, aka MQTs. MQTs can be used to hold the precomputed results of complex queries, and the DB2 optimizer can choose to use one - sometimes with order-of-magnitude query runtime improvement - without a query-submitting end user even having to know of the MQT's existence. As disc jockeys used to say in days of yore, the hits just keep on coming.

On now to vertical scalability. DB2 certainly has - in the form of DB2 for z/OS data sharing and the data partitioning feature of DB2 for Linux, UNIX, and Windows (LUW) - multi-server clustering solutions that offer unmatched horizontal scalability, and that's great, but often what an organization wants for a particular database deployment is a solution that can drive higher and higher levels of throughput as the resources of a single server are boosted through the addition of CPUs and/or server memory. Does this sound simple to you, delivering greater throughput as a server's compute power is increased? It's not. In particular, as microprocessor speed and capacity (multi-core, anyone?) continue to break through already amazing levels, the software engineering challenges associated with preventing data processing bottlenecks from hobbling system performance become more and more daunting. Overcoming these challenges has long been a major focus of the DB2 development organization, and it shows. Data server capacity planning becomes much less of a guessing game when you can count on a DBMS to scale with the servers on which it runs.

Finally, manageability. That may not be a scintillating topic of conversation, but it's hugely important with respect to another kind of DB2 scalability that I call human scalability. This term refers to the ability of an IT organization to expand the deployment of a data-serving platform WITHOUT having to boost the ranks of technology support personnel in a proportional manner. To the organization for which my highlighted DBA works, this is huge. They have a few hundred DB2 databases deployed now, with more to come. Some of these databases contain multiple terabytes of data, and lots of others are sized in the hundreds of gigabytes. A solution that can be set up, maintained, and managed in a people-efficient way is a must under these circumstances, and DB2 flat-out delivers the goods. It's always been very strong from a monitoring perspective, delivering rich performance and availability data via CPU-efficient traces. It continues to allow for more and more database changes and maintenance operations to be accomplished online, with no need for a "window" of inaccessibility from the user perspective. And it is becoming, more and more, a self-managing solution, with the self-tuning memory management (STMM) feature of the DB2 "Viper" release being a particularly noteworthy innovation (STMM was made possible by the extension of the threaded model of internal operation - already there for DB2 on Windows servers - to the UNIX and Linux platforms).

The late, great, Johnny Cash once sang of an encounter from which he "come away [sic] with a different point of view." Thus I came away from listening to a DBA - someone focused on helping his employing organization accomplish necessary work efficiently and effectively - explain to a group of developers why the organization had hitched its data-serving wagon to a horse called DB2. Sometimes the things that make DB2 a winner in the marketplace are the strengths that have been their all along (and which get better all the time). So, keep up with all the new and cool stuff, but don't forget about the solid rock on which that cool stuff stands.

Talking DB2 and SOA in Big D

Howdy, folks. I'm posting today from Dallas, Texas, site of the 2008 IDUG North American Conference (IDUG is the International DB2 Users Group). I've had the opportunity here to participate in some extensive discussions about Service-Oriented Architecture (SOA), one of my favorite subjects (on Sunday I taught a day-long seminar titled "SOA in the Real DB2 World," and yesterday I moderated a Special Interest Group session on "Mainframes, COBOL, and SOA"). Following are capsules of some of the more interesting (to me) exchanges that proceeded from the aforementioned discussions. I'll probably elaborate on several of these in future posts.

• SOA is way more than Web services. The topic of Web services often dominates conversations about SOA, to the extent that some people might have the impression that use of the former suffices as an implementation of the latter. Such, of course, is not the case. Web services - essentially, application services that are described by a standardized form of XML called Web Services Description Language, aka WSDL - are indeed important, because they can be understood by service-consuming programs and can be invoked in ways that are not dependent upon technical details of service-providing program implementation (i.e., a person coding a service-consuming program can use a Web services call to utilize the functionality of a service-providing program, and he doesn't have to know about things like the hardware platform and operating system on which and under which the service-providing program runs). Abstraction of the "plumbing" of an application system, as provided via Web services, is a key characteristic of an SOA (good for programmer productivity and for application change isolation), but so is service reuse, and the exposure of application functional components as Web services does not necessarily lead to a situation in which those components will be reused to speed up application functionality extension efforts. If functional components are not being reused - if wheels are being reinvented to extend application functionality - then you don't have an SOA, regardless of whether or not you're utilizing Web services.

• An Enterprise Service Bus (ESB) is not just about inter-application information exchanges. Quite often, an ESB is a foundational part of an organization's SOA, and an ESB is frequently justified as way to facilitate communication between different application systems (often running on different server platforms and implemented using different programming languages and database management systems) for purposes of information exchange (in other words, an ESB make sit easier for data and functionality to be shared across an organization's application systems). There's nothing wrong with this justification (it's a good one), but it suggests that an ESB is just about inter-system data sharing. In fact, another key benefit of an ESB has to do with service reuse (which, as mentioned in the preceding bullet item, is not at all guaranteed through the use of Web services). You see, if application service components are going to be reused, people have to be aware of them, and it really helps people with respect to their awareness of existing application services if those services are registered somewhere. How can you get developers of service components to register same? One way is to make an Enterprise Service Bus THE interface to an organization's application services, and to restrict the services available via the ESB to those cataloged in a registry associated with the ESB (as one discussion participant put it, "if an application service isn't in our ESB registry, it doesn't exist"). An ESB can therefore be of significant value as a means of establishing discipline in an application development environment (e.g., services WILL be accessed via this interface, and they WILL be registered in this repository), and that kind of discipline is important because SOA success often depends as much on process as it does on technology.

• Code reuse is good, but so is code elimination. If you're going to write code, it's good to take steps that will maximize opportunities to reuse that code, and SOA is certainly about that. Here's another potential benefit of an SOA: code elimination. I'm thinking particularly about rules engines, which are often implemented as part of an Enterprise Service Bus (ESB) solution. The concept here is pretty simple: the ESB is the entry point through which requesters gain access to an organization's portfolio of application services. But how will requests be properly routed to application systems accessible via the ESB? In the case of a relatively complex transaction that will require access to the services of multiple different application systems, and in a certain sequence, to boot, how will the workflow be properly orchestrated? Enter the rules engine, and associated ESB functionality that could be formally referred to as a process engine but which users might call "the traffic cop" or "the control tower." Organizations can use this technology to insert into the process flow rules that specify the way in which various transactions will be handled upon interfacing with the ESB. The ESB can act upon these rules to make sure that work gets done the right way and on the right application systems, and the beauty of it shows when a rule has to be changed (perhaps because of an application system modification or because an extra processing step has been added for a complex transaction): the rules engine is updated accordingly, and that's that. No user code has to be written or altered in order to effect the processing change. You simply inform an intelligent piece of software that the workflow orchestration rules have been updated, and the process engine does the rest. This non-reliance on user-written code to alter transaction processing flows has both an agility advantage (it can be done more quickly versus the user-coded way) and a quality-of-service advantage (user-written code tends to increase the odds of a programming error getting introduced into the production environment). The point is not, of course, to take work away from programmers - it's to get talented programmers working where they deliver the greatest value to the organization: in the development of code modules that deliver functionality to serve the needs of customers and clients. Let process and rules engines take on the work of connecting service requests with service-providing components as needed.

• Absolutely, COBOL is a good language to use in an SOA. Two different people, in two different discussions, expressed concerns related to the use of COBOL in an SOA. One mentioned that because he almost always heard of object-oriented (OO) programming languages (e.g., Java, C#) in talk about SOA, he'd pretty much concluded that SOA was incompatible with procedural languages such as COBOL. This is, of course, not at all the case. COBOL - a very CPU-efficient language that is familiar to a large number of application developers - can be used very effectively in an SOA. I'm particularly keen about the use of COBOL for programs that are part of an application's data-access layer (logically distinct and loosely coupled presentation, business, and data layers are characteristic of an SOA). On mainframes running DB2, you might be talking about CICS or IMS transactions - or DB2 stored procedures - written in COBOL and containing embedded SQL statements (note that CICS and IMS transactions, and DB2 stored procedures can easily be exposed as Web services - the latter via IBM's Data Studio product). And hey, keep in mind that today's COBOL is not your dad's (or your mom's) COBOL. If you haven't checked out the enhanced XML and Unicode support (and other SOA-friendly goodies) in IBM's Enterprise COBOL for z/OS V4.1, you might want to do so. The other person with the COBOL concern was worried about young people coming put of college without COBOL skills. Would his organization be able to bring in new COBOL programmers as people retired or otherwise left the business, and if not, should they start cutting back on their use of COBOL? I liked the answer provided by one of the other people in the room: "We hire college grads and train them in-house to become proficient with COBOL. In three months they're ready to go." Message: relax.

That's it for now. As previously mentioned, I'll probably expand on at least some of these items in future posts. Thanks for visiting my blog. Y'all come back.