Turning off logging

The range of logging choices is large and has expanded in recent versions. We have options for avoiding logging with

• LOAD LOG NO
• LOBs
• Work files
• Initial insert for declared temporary tables
• Not logged table and index spaces in DB2 9

You must choose among some key attributes for data:

• Logging
• Concurrency
• Availability and recovery

If you do not log, then your ability to have concurrency and recovery is compromised. Logging allows the ACID properties of a database without compromise. One of the early reactions to NOT LOGGED is that this choice is like an excavator. You can dig yourself a really deep hole. If your application abends or rolls back, then you need to recover to a prior point in time or load the table again. If others have updated the table since the last copy or load, the changes are lost.

The best use for NOT LOGGED is a scenario when many tables are being inserted in parallel, with 10 to more concurrent tasks. In this situation, the log can be the bottleneck. A single inserter is CPU bound with today's fast disks.

If the customer need is high, use LOAD LOG NO if possible, as that process will be the most efficient. An option has been provided to support a pipe for input. LOAD REPLACE means an outage, but DB2 9 also include clone tables. So now it is possible to use LOAD REPLACE LOG NO on the clone and then exchange the tables.

If a long field exists, you can change the column to a LOB with LOG NO and avoid that logging. Trying to use a LOB with no logging to improve performance might make the problem worse. See the discussions of LOB performance in the IBM® RedBooks Large Objects with DB2 for z/OS® and OS/390®, SG24-6571 and LOBs with DB2 for z/OS: Stronger and Faster, SG24-7270. LOBs are difficult to recover without a log, but that's the trade-off. You can add COMMITs in the program, but the difference with COMMIT is not significant without a log. You can't recover anyway, and because recovery isn't possible, no concurrency exists. It might be OK for a one-time event, but if you want real performance and very little logging, use LOAD. If it's not worth performing fast without logging, logging must not be much of an issue.

One common misconception is that logging is a big difference in performance. The maximum logging rate with dual logging on fast devices is 100 megabytes per second (DS8300 Turbo with FICON™ Express4). If you need faster numbers, striping can be performed. For measurements, see IBM RedBooks DB2 9 for z/OS Performance Topics (SG24-7473). If those rows are 100 bytes each, logging takes about 10 seconds of I/O. Some situations exist in which the log becomes a bottleneck (many concurrent insert streams, with slow log disks). A single inserter has than a 5% difference in most measurements.