Performance Features in DB2 V9 for z/OS – Part 5

As I talked about in the previous weeks, DB2 Version 9 for z/OS has over 50+ great new performance features. Many of these features can really benefit your standard DBA operations and improve application performance and overall availability of your systems and data.

 

Another one of the new features that probably will not be mentioned a lot but will provide improved performance throughout the lifetime of your system is the enhancements around database logging. Here are a couple of little logging related features within DB2 Version 9 in DB2 Version 9 for z/OS but all of these enhancements are helping improve logging and logging related operations.

 

The first and most notable DB2 Version 9 for z/OS logging enhancement is the new capability of turning logging off for a tablespace and its tables/indexes within your database system. Turning logging off can be a great asset when doing those special processes that come up occasionally. For example, when all the rows need to be modified for their yearly rate changes or when massive amounts of rows need to be inserted because of the company merger. All of these activities generate a large amount of logging and the overhead is probably not necessary because you were going to take a backup before anyway.

 

Turning off logging is as simple as an ALTER TABLESPACE database.tablespace-name NOT LOGGED statement. The recovery implications of not logging can be terrible so make sure you have a recovery strategy before altering your tablespace. If you don’t make a backup your recovery point will be to the point when you ALTERed the logging off. Recovering to that last point-in-time could involve a large number of tablespace image copies and a large amount of actives logs which will cause your system to be unavailable and endure the performance impact of a major recovery processing nightmare.

 

The next great logging related feature in Version 9 is the TRUNCATE TABLE SQL statement. Using the TRUNCATE TABLE SQL is a great way to clean up tables and their relate disk storage. This truncate statement empties the table data, has the option to drop its storage and ignore any DELETE TRIGGERs that might be encountered. There are several options on the TRUNCATE SQL statement but it is straightforward to use and is especially handy for cleaning up test environment tables.

 

Logging is only a minor performance overhead, usually less than 5% so make sure to have a backup or recovery strategy if you turn it off using these great new DB2 version 9 for z/OS features.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>