Have you ever wished you could turn logging off to avoid the overhead? Well now with DB2 V9 for z/OS you can. You can use the ALTER table space NOT LOGGED statement.
IBM recommends suspending logging for tables that are used in materialized query tables, summary tables, tables where data is propagated and temporary tables that are populated with the result set from a query as an intermediate step in an application.
I was thinking that in a testing environment where I refresh my database with load jobs that it's a waste of resources to perform logging. I would rather run my test without logging and if the database gets corrupted then refresh it with the load files. So I set out to run some performance tests inserting 100,000 rows into an empty table to see how much CPU savings I would get by turning logging off.
The first figure below shows job CSBPION; performed 100,000 inserts with logging turned on. The average CPU time was .003 with a max time of .694, and a total CPU usage of 306.963.
The second figure below shows job CSBPIOFF; performed the same task with logging turned off. I was surprised to see that the average CPU time was the same .003. However, the overall CPU was lower at 296.277. This was a CPU savings of 3.4 percent. This is a nice savings but you're really getting more savings than just what you see on a given SQL statement.
I don't have a subsystem-performance monitor to show the overall savings. But I can show you some messages logged to my DB2 subsystem showing all of the log records I’m generating, and the log archiving I’m causing. DB2 is spending a lot of resources in the background to manage and take care of the active and archive logs. By suspending the logging I’m improving the overall scalability, especially when large volumes of inserts are being performed.
Notice in the figure below that I generated 313,000 log records, which filled up the active logs and caused archive logs processing.
It may seem that a 3-percent savings to your job isn't much but you also need to consider the savings on logging DASD and reduction of tapes along with all the other CPU resources saved in the subsystem. You may want to consider turning logging off in your testing environment. Please let me know what your experiences are with the ALTER tablespace NOT LOGGED statement.
Hey Troy, nice blog you've got going here. I'm going to be sure to check in and regularly read what you've got to say about DB2!
Cheers,
Craig
Posted by: Craig S. Mullins | August 22, 2007 at 11:48 AM
Hi Troy,
Does this option exist for DB2 9.5 for Windows?
Mike
Posted by: Mike | October 24, 2009 at 05:42 AM
Hi Mike,
I'm not actively working with DB2 for LUW these days. I did a search in IBM Information Center http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp
I searched for "NOT LOGGED" and didn't find a hit.
Posted by: Troy Coleman | October 26, 2009 at 09:36 PM