Referential Integrity: Application-Based or DB2-Based?
This is a debate that has interested me for years. I've heard very good arguments on both sides, but before I get into that I'll state that what I don't like to see is the use of both DB2-based and application-based methods used to define and enforce the same referential integrity (RI) constraints. In other words, for a particular parent-child table relationship, use either a DB2-enforced constraint or an application-enforced constraint, but not both (to use both would, in my opinion, be a waste of system resources).
OK, I'll start with a look at application-enforced RI (i.e., the situation in which user-written code ensures that a row in a dependent table will not be inserted if a key-matching row does not exist in the associated parent table). The way I see it, there is at least one case in which application-based RI is your only choice, and that's when you want to referentially link two objects that are in different DB2 catalogs (in other words, in different DB2 databases if you're talking about DB2 for Linux, UNIX, and Windows; and in different subsystems in a DB2 for z/OS environment). I will point out that the need to referentially relate two objects in different databases/subsystems may not be so great when you're using DB2 (as opposed to some other relational database management systems), because DB2 can make it less necessary to place different tables in different databases/subsystems. With DB2, you can use unqualified table names in your programs' SQL statements (in fact, it's a good idea to do so) and still access different schemas (you might have different table-name qualifiers in development and production, or for sets of structurally identical tables holding, for example, data for a company's different sales regions) with no problem thanks to packages (for which the appropriate table-name qualifier can be supplied at bind time), collections, and the SET CURRENT PACKAGESET special register. With some other relational DBMSs, the use of fully-qualified table names in application SQL statements is considered a best practice, and separating different sets of related tables into different schemas can require the use of different databases.
So, how about other situations, for which the use of application-based RI is a choice and not a requirement? Some folks go the application-based RI route for performance reasons. Typically, this is done when one of the more challenging performance objectives faced by an organization is getting a massive amount of batch processing done in a limited time frame. In such cases, using application-based RI instead of DB2-based RI can reduce CPU and elapsed time for application processes. Here's a classic example: suppose that records from a file are to be inserted into a table in a DB2 database. Assume that the target table is a dependent (RI-wise) of the CLIENT table, and assume further that 1000 of the records in the input file have the same value in the Client ID field. If the RI constraint referencing the CLIENT table is DB2-defined then DB2 will check for the existence of the parent table row for each of the 1000 insert operations it performs to get the file rows into the dependent table. If the application handles RI enforcement, it can check for the existence of the parent table row when inserting the first of the 1000 rows with identical client IDs and then continue inserting the other 999 rows without performing a parent-table check.
The other classic example of situation in which application-based RI can deliver performance benefits has to do with large-scale DB2 delete operations. Suppose that you need to delete 1000 rows from a parent table, along with all the related rows in a dependent table. Assume that your program will do these deletes in a "bottom-up" fashion, so that all dependent-table rows will be deleted before the parent table rows are deleted. If DB2-based RI is used, for each delete of a parent table row DB2 will check for the existence of key-matching rows in the dependent table, even though those rows have already been deleted (DB2 does not know that your program is doing a "bottom-up" delete of rows in the tables). If the RI constraint is application-enforced, parent table deletes can proceed with no dependent-table checks because the application knows that all of the related rows in the dependent table have already been deleted.
The advantages of DB2-based RI are closely related to the disadvantages of application-based RI. When you go with application-based RI, maintenance of referential integrity becomes a programmer responsibility. Suppose the coding that enforces a fairly complex RI relationship was done by someone who had a lot of domain knowledge and who recently left the company. How well did he document his work? There's never a problem when you need information about DB2-defined RI constraints - just look in the catalog tables that contain RI information.
Another disadvantage of application-based RI is that it requires a lot of discipline, process, and structure with respect to data-change operations. You can't have someone just write a data-insert program if it lacks the requisite RI logic. What do you do in such cases? Do you have one great big routine that handles all data-change operations for a set of referentially-related tables (the bigger they are, the more difficult application maintenance becomes)? Do you instead go for smaller and more specific routines, and replicate associated RI logic (keeping that logic, spread across several programs, consistent over time could be a challenge)? If you define the RI constraints via DB2, programmers can code leaner data-change logic, and that's generally a good thing.
You can also realize performance benefits through the use of DB2-based RI. Just as application-based RI can improve CPU and elapsed time for certain large-scale, batch-oriented insert and delete processes, DB2-based RI can deliver improved performance in a transactional environment. Why? Because when DB2-based RI is in effect, a program that (for example) inserts one row into a dependent table can perform the row insert and the requisite RI check with one trip back and forth across the application-DB2 interface. In an application-based RI environment the RI check and the insert require two application-DB2 round trips. Those trips back and forth across the interface require the execution of CPU instructions, so the fewer of those you have to do, the better for a transaction's CPU efficiency (and elapsed time may be a little better, as well).
Oh, and something else: if you use DB2-based RI and you want to verify that the data in a table is in fact OK from an RI perspective, just use the DB2 for z/OS CHECK DATA utility or the integrity-checking capability available through the DB2 for LUW Control Center. Want to do the same thing when RI constraints are application-based? If so, be prepared to write your own RI-checking code.
What's my own preference? I'd have to say it's DB2-based RI. For one thing, performance concerns over the past 10 years or so have been shifting from batch run-times to transaction throughput, and that makes DB2-based RI a good choice. Of greater importance to me is the fact that DB2-based RI takes a big issue off of programmers' plates, and today's need for quick-response application coding and flexible systems makes that a big deal. So, let DB2 do the RI work for you.
Aggressive DB2 Disaster Recovery
Back in the mid-1990s, when I was on the DB2 for z/OS National Technical support team at IBM's Dallas Systems Center, I did some consulting work for a major insurance company. For most of one day during an on-site working session, we reviewed the company's documented disaster recovery (DR) procedure for their very large, very high-volume, DB2-based core application system. This organization had put together one of the most comprehensive and sophisticated DR action plans I'd ever seen, characterized by extensive use of automation (a given step in the DR procedure would call for the submission of job X, which would submit for execution jobs A, B, and C - each of which would complete several tasks related to an aspect of the overall DR process). By following their procedure, the systems staff of the insurance company were pretty confident that, were the primary data center to be incapacitated by a disaster-scope event, they could get the mainframe DB2 database and associated core application system restored and ready for work at the backup site within two to three days.
Two to three days! And that was really good if you used what was then about the only DR method available to mainframe DB2 users, called the "pickup truck" method by my Dallas Systems Center colleague Judy Ruby-Brown: once or twice a day, you had your DB2 image copy and archive log tapes loaded onto a truck and transported to your DR site. To recover the database at the backup location, you had to restore all those image copies to disk and then recover all of the tablespaces to as current a state as possible using the archive log tapes available to you. And after that you had to rebuild all the indexes. And before all that you had to do a conditional restart of the DB2 subsystem. And before that you had to get the z/OS system (called OS/390 back then) restored so that you could start DB2 (and that could take several hours). Consider all this in light of the relatively slow (by today's standards) disk storage systems and mainframe engines in use a dozen years ago and you can see why DR was a very time-consuming endeavor (the aforementioned 2-3 days looks great when you consider that some companies estimated that it could take a week to get their big mainframe DB2 databases and applications ready for work after a disaster event).
Times have changed, technology has changed, users' expectations have changed, and now many organizations would be in a world of hurt if their core applications were to be out of commission for several days. So much business is conducted online - between individual consumers and companies, and between companies and other companies (e.g., between an automobile manufacturer and its suppliers). Fortunately, modern computer hardware and software offerings have enabled organizations to slash the time required to recover from a disaster-scope event impacting a data center (as well as greatly reducing the amount of data lost as a result of such calamities). Of great importance in this area was the emergence a few years ago of remote mirroring features in enterprise-class disk storage subsystems. Given various names by different vendors (from IBM, Metro Mirror and Global Mirror; from EMC, SRDF and SRDF-A; and from Hitachi Data Systems, TrueCopy), these features let a company maintain, at a backup data center site, a copy of disk-stored data (all data - not just a database such as DB2) that is either exactly in synch (if the backup site is within, say 25 or so fiber-link miles from the primary site) or very nearly in synch (if the backup site is far from the primary site) with respect to the disk-stored data that supports the production application system. Because DB2 for z/OS active log data sets can be mirrored in this way, along with the DB2 catalog and directory and all of the application DB2 database objects, remote-site recovery of a knocked-out mainframe DB2 system is not much different from an in-place recovery/restart following a temporary loss of power at the main site: you basically get the z/OS system back up and then issue the -START DB2 command.
When an organization implements a remote disk mirroring solution for DR purposes, how quickly can it get a mainframe DB2-based application system up and ready for work at a DR site following a primary-site disaster? This is, to me, a very interesting question. I don't know what the record is (and I'm talking about terabyte-plus-sized databases here, with peak-time workload volumes of at least a few hundred transactions per second), but I'll put this on the table: I think that if a company's systems people are good - really good - they can get a big and busy 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. What do you think? Feel free to post a comment to this effect.
The DB2 for LUW people may have a DR speed advantage because of the High Availability Disaster Recovery (HADR) feature introduced with DB2 V8.2. HADR is, conceptually, like log shipping taken to the extreme, with data change-related log records sent in real time to a remote DB2 for LUW system and "played" there, so that the buffer pool of the secondary DB2 database is kept "warm" with respect to that of the primary DB2, insofar as data and index pages affected by updates (and deletes and inserts) are concerned. This being the case, the time required by the secondary DB2 system to take over for the primary DB2 is really small: generally speaking, less than 30 seconds (the "warm" buffer pool on the secondary DB2 makes roll-forward recovery unnecessary, and slashes the elapsed time of in-flight UR backout processing). Now, having the database ready to go in a half-minute or less isn't the whole story (there are application and Web servers to deal with, and network stuff such as rerouting transactions that would normally flow to the primary site), but I'm willing to put another declaration on the table: given a DB2 HADR configuration, I believe that a crack IT crew could get a large and busy DB2 for LUW database and application up and work-ready at a DR site within 10-15 minutes of a primary-site disaster. Do any of you feel likewise? Even better, have any of you done this, either for real or in a full-scale "drill" exercise? I'd welcome your comments.
In a few years, will DB2 people shake their heads at the recollection that companies used to feel that a 30-minute DR recovery time objective was world-class? How far will technology take us? I think it'll be a fun ride.
When DB2 for z/OS Can't Account for in-DB2 Time
DB2 for z/OS is very good (through data provided in accounting trace classes 2 and 3) at showing how time is spent when SQL statements are executed (this is often referred to as in-DB2 time). That said, it is often the case that one will see, in a DB2 monitor accounting report or an online monitor display of DB2 accounting data, a non-zero value in a field labeled "not account." This label is short for "not-accounted-for time," and it refers to time that is in-DB2 (i.e., not "in-application-but-outside-DB2" time) that was accumulated for reasons unknown to DB2. The calculation for this figure is pretty straightforward: take the in-DB2 elapsed time and subtract from that value the in-DB2 CPU time and the total DB2 class 3 suspension time (the latter value is the sum of the wait times of which DB2 is aware, such as the time spent waiting for synchronous I/O operations to complete).
Usually this value is quite small, and if it's less than 10% of in-DB2 elapsed time I don't pay much attention to it. If, on the other hand, the not-accounted-for time is well in excess of 10% of in-DB2 elapsed time, I'm going to want to check into that because it could be indicative of a system performance problem (I've seen not-accounted-for values as high as 50% of in-DB2 elapsed time).
So, what can cause overly large not-accounted-for time values? First, I need to point out that there is one case in which a relatively high not-accounted-for figure is not necessarily a red flag: that of a local application issuing one or more requests through the DB2 subsystem that will be executed on one or more remote DRDA servers (in this case DB2, through its Distributed Data Facility, acts as a DRDA requester). When that situation occurs, the not-accounted-for value will include the time the requesting process waited for responses from the remote data server. Depending on the nature of the request for remote data services, that wait time could be considerable.
OK, so DRDA requester situations aside, what could lead to a large not-accounted-for time value? In my experience, it tends to be a symptom of a DB2 dispatching problem. That is to say, either the mainframe server is absolutely maxed out, or it's merely very busy and DB2 has a too-low priority in the z/OS system. Either way, you have a situation in which DB2 itself is not being readily dispatched by the operating system when it needs some CPU time. Since DB2 can't get an engine (i.e., a CPU or microprocessor) when it needs one, and it can't know what's going on when it doesn't have an engine, it realizes when it finally does get dispatched that time has elapsed in the process of executing an SQL statement, but it can't account for WHY that time has elapsed.
What to do if in-DB2 not-accounted-for time is overly large? First, check DB2's priority in the system. If it's too low, up it. What's too low? Well, at IBM's 2006 Information on Demand event in Anaheim I attended an excellent session presented by Glenn Anderson, an IBM WLM expert, and he said that an important started task such as DB2 should be assigned to a started task service class with a velocity of around 50 or 60 and Importance 1 (referring to the DB2 DBM1 and MSTR address spaces - the IRLM address space should be assigned to the SYSSTC service class).
If your production DB2 address spaces have an appropriately high priority within the z/OS system, and you still see high in-DB2 not-accounted-for time values, you may have an overloaded mainframe server. In that case, you have a couple of options. The first is to add processing capacity to the server (more and/or faster engines). The second is to reduce CPU consumption, perhaps through DB2 subsystem and application tuning.
So, if you have a really busy server running DB2 for z/OS, keep an eye on that in-DB2 not-accounted-for time. If it's a single-digit value (as a percent of in-DB2 elapsed time), relax. If it's high, do some investigating.