Skip to content

Personal tools
You are here: Home » DB2 » DB2 Mainframe Articles Archive » DB2 Questions for the Experts - Part 3
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3559

DB2 Questions for the Experts - Part 3

by Elizabeth A. Mullins

Part 1  |  Part 2  |  Part 3

Third and final installment of a conversation between DBAzine and 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 (view Part 1 and Part 2).

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

Betty Gray — Betty is an independent DB2 educator and consultant. She can be reached at

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

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

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

DBAzine: Enforced logging of database changes has been the subject of several DB2 List Server threads recently. IBM's position seems to be that it is folly to bypass logging. Do you agree with this? Can you envision any types of activities that would benefit from being able to turn off DB2 logging? What would the impact be to desirable capabilities such as recoverability, auditing, and migration capabilities enabled by DB2 logging?

Roger Miller: "No logging" is essentially an implementation. (see "Enforced Logging," IDUG Solution Journal article, May 2001). But as far as I know, it makes little sense to bypass logging. IBM has greatly improved the logging rates. Since the ESS model 800, there are more than 20 megabytes/second for dual level logging with striping more than 30 m/s. With performance at these levels, the risks associated with bypassing logging far outweigh any possible benefits.

IBM now has 5 mechanisms in place to reduce logging rates — LOAD, work files, created global temporaries, declared global temporaries and LOBs. If my customer wants to have tables without logging, my first question is, "Why?" Another way of phrasing the question is to tell me the objective, rather than a specific technique. If the objective is to save space on the log and archiving, there are a number of techniques for doing that. One example is compressing the archive log with a tool from IBM or another vendor. If the objective is to improve overall performance or elapsed time by 10%, turning off logging generally cannot do that. It does make recovery more troublesome and does affect the concurrency. DB2 for z/OS and OS/390 has options to turn off logging when the lack of logging doesn't compromise data and subsystem integrity. They include:

      • LOAD utility: LOG NO does not turn off all logging, but does avoid logging the data, and ensures a good commit frequency. If the volume to be inserted is very high and concurrency is not required, then using the LOAD utility is what I'd suggest.
      • For an intermediate step in performance LOAD RESUME SHRLEVEL CHANGE provides higher performance than INSERT, but it still logs.
      • work files: No logging is needed. There are no recovery or restart issues.
      • global temporary tables - These tables have no logging, recovery or restart problems. The global temporary tables are empty at each new unit of work.
      • declared temporary tables - These tables can have indexes and updates with rollback, so undo logging is done (but not the data for inserts with redo logging). These tables are empty when the thread terminates, so recovery is not an issue.
      • LOBS: LOG NO is an applicable option, while we have the information required for restart and recovery in other places. If you are storing images and video, this is probably the technique you will use to avoid logging.

Beyond the current capabilities, there are a number of requirements that ask about turning off logging. As I understand this area, there are a number of differing objectives. Some of the objectives will not be met by turning off logging. The key question is how you expect to handle your COMMIT, ROLLBACK, transaction atomicity, statement atomicity, checkpoint, restart, and any error situations. Without a log, we lose some of the ACID (atomicity, consistency, isolation and durability) properties that make us a database. Maybe that's really what you are looking for - a flat file with a query language and little ability to share data for multiple users. There are different implementations and issues that depend upon the answers to these questions.

There are some risks and changes for turning off logging. Some are similar to cancel with no rollback. If anything goes wrong, goes wrong, goes wrong, ... your data is trash. Drop the tables and start over. DB2 uses the log for statement atomicity, index and data matching, referential integrity errors, and several other purposes. There is a substantial amount of code to be reworked or the function will have restrictions. The biggest performance problem with not logging is that we'll need to write the data more often and force all of the data and indexes to disk, waiting at commit. This is likely to result in less concurrency and longer response times for transactions that log.

The performance of some DBMSs is significantly better if logging is turned off. The DB2 high volume log writes are done asynchronously. For transactions with high commit rates, DB2 can use one set of log writes for many transactions. DB2 IO optimization generally means that the improvement for avoiding logging, even for an image application, is 5% or less. If there are a few megabytes to insert, the log writes take a fraction of a second. There are situations where the log can become a performance constraint. The DB2 logging rate is above 100 GB per day for some customers. Benchmarks with the first Enterprise Storage Server (ESS) and dual logs were measured at about 8 MB per second or about 28 GB per hour. That's for a single member of a data sharing group, so the logging rate for a group is several times larger. VSAM striping can be used on each member to improve the logging rates by another factor. With FICON and ESS model 800, logging rates can easily exceed 100 GB per hour per member of a data sharing group. Another reason for wanting to avoid logging is to improve recovery times. We have many options that range from improved speed of log apply to using disk functions like FlashCopy to improve recovery times. There are too many options to cover in a page or two.

My guess is that we'll implement a no log option some day, and customers will discover that this function is like row level locking, useful for 1% to 4% of the situations, and very helpful there. Then we'll have lots of customers coming back to say that we gave them what they asked for, but not what they want. So please tell us what you want and what you need. Explain the situation you are finding today, and what alternatives you have examined to get past the problem. We need to understand the constraints and costs. If there are standards, please point them out. A suggested implementation is fine, and might even help, but the most important section in the requirement is the justification. We have thousands of customers, with many needs in most. The resources we have require some difficult choices for what we can and cannot do. The way you can be most effective (other than by sending money) is to provide a justification that makes your case.

Alex Robb: Yes, it's folly. There's not much more to say than that.

Randy Ebersole: I have yet to have someone "show" me any valid reasons to shut off logging. Some people may still believe logging will create performance issues. This is not the case. The benefits received from using logging so far out weigh the cost in savings that the real question is "why would you turn it off?"

Linda Ball: Certainly it is only folly to bypass logging if you want to use the log to RECOVER. In fairness to the IBM folks who might have called this folly, customers often want things both ways. They don't want the logging volume but they do want to be able to recover to any point in time. Logging is actually optional in a couple of cases. Lobs can have it turned off. Internally, the mapping tables that IBM requires for SHRLEVEL CHANGE REORG don't require it. It's just a matter of time before it will be allowed for other spaces. I can envision some applications for it. But people must realize that the logging is essential for ROLLBACK and RESTART, not just recovery. Several vendors have products that allow log records to be eliminated after transactions are committed. Customers might want to consider those. However, if logging volume is a problem, I would first look at other causes of logging volumes — things like too many checkpoints, too many commits, utilities that allow NO LOG being run with logging, updates to indexes that aren't necessary anyway. Finally, the log has really become a resource beyond restart and recovery with the advent of log tools (like BMC's LogMaster and others). In a way, using the restart and recovery logs in this way is incredibly efficient. You have all this data that supports those things and can be used for migration, auditing and transaction recovery. It is much more likely to be accurate than an application log where errors in the application might impact whether everything is logged correctly. Applications can be 'extended' with these log tools with much less programming and duplication of data. So my first concern with logging wouldn't be getting a no log feature from DB2 developers. Rather I'd concentrate on what is on the log, how I can reduce the volume safely and how I can use it to make other things work more smoothly.

Craig S. Mullins: I guess I can envision a scenario where you might want to disable logging in a test environment or perhaps, as Linda suggests, when you know you will not need to recover. But the problem with such a feature is that once you "let the cat out of the bag" he's never going back in there. As soon as a feature like this is made available it will be misused. You know the scenario: "I turned off logging for this one job but forgot to turn it back on and then I ran this other job and please I need to recover but, wait, what do you mean I can't?" I wouldn't want to be the technician at the other end of that support call. I just do not see enough tangible benefits to make this a reasonable option.

DBAzine: And finally, storage hardware has changed dramatically over the past few years. How are customers exploiting advances in intelligent storage hardware in their backup and recovery strategies? What limitations are there in terms of exploiting these devices for DB2 data?

Roger Miller: Customers are using the new faster disks for dramatically improved performance today. Many customers are using new techniques for improved backup and recovery, such as FlashCopy. The next challenge is that most customers have more than one vendor for their disks, and disks do not have identical capabilities.

Randy Ebersole: Companies are just starting to realize how this can be a benefit. This will be beneficial for all their data, not just for DB2 data. Storage hardware today can store more than mainframe data-now you can take a true "picture" for backup. IBM has added some new commands to DB2 since V6 to allow DB2 to take advantage of this new storage hardware. The functionality is there-now shops just have to use it. This now makes it possible to take a full picture of your DB2 environment with zero or minimal downtime.

Linda Ball: Many customers use the features of these new devices regularly. I think one of the most important things they provide is simply avoiding the head crash problems with a mirroring or parity scheme that allows a single failing drive to be seamlessly replaced. The schemes that allow fast copies of data sets and volumes are popular but a bit harder to manager. Some customers are mirroring large amounts of data over distances for disaster recovery. The two big limitations of these features are standardization and management. If two disk manufactures have different firmware features with different interfaces, the disk purchase loses its commodity price negotiation. Some of these devices are chosen and used and become almost like an operating system choice with a lot of resources committed to implementing with it that doesn't easily transfer to another device. Finally, managing the data and its uses is a problem. All data is not created equal. It takes knowledge of DB2 or IMS or a customer's application to truly manage the data.

Craig S. Mullins: This is a good example of why third party software vendors are vital to the continuing success of DB2. A company like BMC Software can more easily forge relationships with storage hardware companies that compete with IBM - vendors like EMC, Hitachi, and StorageTek. As Roger and Linda hinted at, most customers have a mix of storage devices from multiple vendors - and they are going to want their DB2 utilities to work with all of the advanced features of all of those devices. The third party ISVs are in a better position than IBM to make that happen. IBM is a hardware vendor first, a consulting company second, and a software vendor last (check out their revenue if you don't believe it). I don't mean to imply that IBM will never support other storage hardware - they obviously will have to. But the third parties will be there first because there is no conflict of interests to overcome.


Well, there you have it. Our thanks goes out to all the folks who spent their valuable time giving such candid and insightful answers to quite a wide range of questions. I hope you all enjoyed hearing their opinions as much as we did.

Interviews conducted by Elizabeth A. Mullins.

Contributors : Elizabeth A. Mullins, Roger Miller, Betty Gray, Alex Robb, Linda Ball, Randy Ebersole, Craig S. Mullins
Last modified 2006-01-04 03:12 PM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone