For Ultra-Fast DB2 Recovery, Don't Recover

Over the past decade or so, DB2 disaster recovery (DR) has come a long way, especially where the mainframe platform is concerned. At least the DB2 for Linux/UNIX/Windows (LUW) folks had log shipping, which enabled organizations to get the backup data server at the DR site up and ready to handle application requests in less than an hour following a primary-site disaster event (and without too much data loss, to boot - maybe 30 minutes or less, depending on the frequency of inactive log file backup operations).

For the mainframe DB2 crowd, it was a different story. Up until around the mid-1990s, people would take their most recent image copy and archive log files and send them, maybe once or twice each day, to the DR site. [It was common for these files to be transported to the DR site in TAPE form in a TRUCK - keep in mind that the volume of data to be transported could be quite large, and the capacity of a "high bandwidth" T1 line was almost two orders of magnitude less than what you get with an OC-3 circuit today, so electronic transmission was not always feasible.] Cue the primary-site disaster, and what happened next? The DB2 subsystem had to be brought up on the DR site mainframe via a conditional restart process (lots of fun), and then all the tablespace image copies had to be restored and then all the subsequent table updates (i.e., subsequent to the image copy times) had to be applied from the logs and then all the indexes had to be rebuilt (this was before one could image copy an index). I mean, you were often looking at several DAYS of work to get the database ready for the resumption of application service, and on top of that you might be without the last 24 or more hours of database updates made prior to the disaster event.

What happened in the mid-1990s? Disk array-based replication came on the scene. This made mainframe DB2 people very happy, because it VASTLY simplified and accelerated the DB2 DR process, and it slashed the amount of data loss with which one had to deal in the event of a primary-site disaster situation. [When disk array-based replication is operating in synchronous mode (feasible if the primary and backup data center sites are within about 20 straight-line miles of each other), there will ZERO loss of committed database changes if a disaster incapacitates the primary-site data center. If asynchronous mode is in effect (the right choice for long-distance data change replication), data loss will likely be measured in seconds.] This goodness is due to the fact that disk array-based replication (also known as remote disk mirroring) mirrors ALL data sets on associated disk volumes - not just those corresponding to DB2 tablespaces and indexes. That means the DB2 active log data sets are mirrored, and THAT means that the DB2 DR process amounts to little more than entering -START DB2 on a console at the DR site. The subsystem restarts as it would if you restarted DB2 in-place following a power failure at the primary site, and there you are, ready to get application work going again.

In recent years, array-based replication has become an option for DB2 for LUW users (the disk subsystems equipped with remote mirroring capability - from various vendors including IBM, EMC, and HDS - initially supported only mainframe attachment). Another big step forward in DB2 for LUW DR was the introduction of the HADR (High Availability Disaster Recovery) feature with DB2 for LUW Version 8.2. HADR is conceptually like log shipping taken to the extreme, with log records pertaining to data change operations being transmitted to and applied on a secondary DB2 instance in real time as they are generated on the primary instance. Like remote disk mirroring, HADR can make DB2 for LUW DR a quick and simple process, with zero or just a few seconds of data loss (depending on whether HADR is running in one of its two synchronous modes or in asynchronous mode).

So, whether the DR-accelerating technology of choice is remote disk mirroring (DB2 on any platform) or HADR (DB2 for LUW), DB2 users can get their DR time down to a pretty impressive figure. What kind of DR time am I talking about here? Well, as I pointed out in a previous post to my blog, I believe that a really good group of systems people could get a DB2 for z/OS database and application system online and work-ready at a DR site within 20-30 minutes of a primary-site disaster. If the OS platform is Linux, UNIX, or Windows, and if DB2 is running in an HADR cluster, I believe that the DR time could be taken down to maybe 10-15 minutes (less than the best-in-class DB2 for z/OS time because HADR eliminates the need for the roll-forward phase of DB2 for LUW restart and dramatically speeds the rollback phase).

There is only one problem with these impressive DR times: for some organizations, they are not fast enough. Suppose you need a DR time that is so short that from an application user's perspective the system is NEVER down, even if, behind the scenes, one of your data centers is incapacitated because of a disaster event? Pulling that off would probably require that you get your DR time down to a minute or less (and keep in mind that I'm not just talking about getting the database ready for work - I mean getting the application and all the network-related stuff taken care of, too). I doubt seriously that this could be pulled off if you were to go about DR in the traditional way. The solution, then, is to go the non-traditional route, and by that I mean, simply: don't recover.

How do you do DR with no DB2 recovery in the traditional sense? It starts with you getting the notion of primary and backup data centers out of your mind. Instead, you run with two or more "peer" data centers, each running a COMPLETE instance of the database and the application system and each handling a portion of the overall application workload (e.g., transactions initiated by users in one geography could be routed to data center A, while other transactions are routed to data center B). Bi-directional database change propagation implemented through replication software tools (available from vendors such as Informatica, GoldenGate, and DataMirror - the latter now a part of IBM) keeps the different copies of the database located at the different data centers in near-synch with each other. If site A is incapacitated because of a disaster event, you don't try to recover the site A system at site B; rather, you move the work that had been routed to site A over to site B, and you just keep on trucking. At a later time, when you can get site A back online, you re-establish the copy of the database at that site, re-synch it with the copy at site B, and go back to the workload split that you had going before the site A disaster event.

I've just used one paragraph to describe an ultra-high availability strategy that is relatively simple in concept and quite challenging with respect to design and implementation. I'll be explaining things more fully in a presentation - "Ultra-Availability - How Far Can You Go?" - that I will be delivering at the International DB2 Users Group (IDUG) North American Conference in May, and again at the IDUG European Conference in October. I hope to see you in Dallas or Warsaw (or both). In the meantime, don't listen to people who tell you that the kind of ultra-high availability I'm talking about can't be achieved. It can. You just have to think outside of the box.

A DB2 Perspective on Database Separation

First, an explanation of what I mean by "database separation."

One of the key concepts of SOA (service-oriented architecture) is something known as "loose coupling." Basically, the idea is to reduce the number of dependencies between functional components of an application system. The "why" of loose coupling is primarily about application change isolation. If the major parts of an application system are tightly coupled (the term "monolithic" is sometimes used to describe the architecture of an application with very tightly coupled components), a change made to one part can necessitate changes to other parts - and if these co-requisite changes (and you're usually talking about program code changes) are not made, chances are that something's gonna break. [Matt Humphrey, a friend of mine and an application architecture expert, calls this the "Jenga effect," after the popular board game - will the tower tumble when the next block is removed?]

I think of loose coupling in two ways: what I call "horizontal loose coupling," by which I refer to the reduction of dependencies between functional layers of an application (i.e., presentation, business logic, and data access - often represented pictorially as differently-colored blocks separated by horizontal lines), and its cousin, "vertical loose coupling," the latter referring to loosening, in a dependency sense, different application subsystems within an enterprise (e.g., order management and inventory management application subsystems, which might be shown in a picture as being "next to" each other, separated by a vertical line). Discussions of vertical loose coupling often get into the importance of applications "owning" their data (example: if a program that's part of the order management application needs access to data that "belongs" to the inventory management application, it doesn't go directly to that data - instead, it accesses the data by way of an interface to the inventory management application), and here some folks (especially those with a "systems" bent) can become rather uncomfortable with the thrust of the conversation.

Why the discomfort? Well, application ownership of associated data implies a separateness of databases (if databases are involved), and a lot of people who are less familiar with DB2 than they are with other relational database management systems jump to the conclusion that said separateness has to be physical in nature (i.e., what might today be one database used by two applications will have to become two physically separate databases in an SOA environment). As another of my application architect friends, Eric Drudge, once told me, "I think that there is a fear of a lot of... small [databases] popping up all over the place that logically are related, thus making referential integrity a challenge." The RI challenge that Eric mentioned has to do with the fact that DBMS-defined RI constraints can't cross database lines. Another potential problem when one shared database becomes two (or more) physically separate databases is related to recovery/restart. Think about a data-changing logical unit of work that now spans two databases following a data separation action performed in the course of loosening the coupling between two application subsystems. As a result if the database separation there are now two commit scopes within the one logical unit of work (i.e., the application updates database A and commits that update, then updates database B and commits that update). What happens if the system crashes after the database A update has been committed, and before the database B update has been committed? Here's what will happen: following system restart, there will be a data integrity problem because databases A and B will be out of synch, reflecting a partially done piece of work. There are ways of dealing with this, including the use of distributed two-phase commit (if provided by the DBMS) and the coding of idempotent database update logic (logic that if re-executed with the same inputs will not generate an error and will not violate data integrity - useful if your approach is to just re-drive the whole logical unit of work following restart, without worrying about where it was when the failure occurred), but there are costs associated with both of these work-arounds (distributed two-phase commit tends to increase CPU consumption, and making database update operations idempotent could require a lot of code re-write).

So, the concerns of IT systems people about database separation are legitimate, but I mentioned that these concerns are more likely to surface amongst people who are not familiar with the DB2 database management system. What does DB2 have to do with this? Well, the aforementioned RI and recovery/restart issues go away if the databases in question are only LOGICALLY separated (i.e., if tables "belonging" to two different applications are divided into two different schemas within one physical database), and this kind of logical-within-physical separation is more easily accomplished with DB2 than it would be in some non-DB2 environments (note to DB2 for z/OS people: I'm using the term "database" to refer to what you might call a DB2 subsystem). DB2 facilitates logical database separation within one physical database because:

1. When you create a DB2 table, it’s easy to provide a qualifying name (the schema name) that has NOTHING to do with table “ownership,” and
2. Even with multiple schemas (collections of related tables) within a single DB2 database, it is easy to use unqualified table names in SQL statements: if the SQL is static, you supply the schema name via the QUALIFIER option of BIND, and if the SQL is dynamic, you provide the schema name by way of a SET SCHEMA statement.

People who have worked exclusively with DB2 tend to take these things for granted. Folks, believe me when I tell you that logical database separation without physical database separation is NOT such an easy thing in some non-DB2 environments. I’ve worked with people who’ve gotten into DB2 after working previously with some other DBMSs, and I've seen in those situations a tendency to physically separate databases when that is not necessary. Sometimes, some explanation is required to help these experienced database people who are new to DB2 to understand that a logical-only separation of databases is a perfectly viable - and often preferable - alternative to physical database separation.

Here's the bottom line: logical database separation along application subsystem lines can be an important step in the implementation of a service-oriented architecture, helping to achieve the goal of loose coupling, which itself is aimed at reducing the impact that changes made to one part of an application system have on other parts of that system. At the same time, placing various applications' tables in one physical database is great if you want to leverage DBMS-defined referential integrity, and if you don't want to worry about partially-done units of work following a system failure event. DB2 lets you have it both ways.

Small 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 the one-day seminar, without registering for the rest of the conference).

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).