September 09, 2008

DB2 9 Log Avoidance

The purpose of logging is to record system and transaction activity history. But the trick with logging is to minimize its use to save resources, while still assuring yourself that you can recover system and application to a stable and consistent state.

IBM DB2 Version 9 provides some new ways to reduce the amount of data that needs to be written to the log files. Let's look at some existing techniques as well as new options that you can use to avoid writing to the log.

  • DDL operations--When a data definition statement is executed to create, alter or drop an object, the database descriptor (DBD) is logged. This can be significant if you have many objects in a database. In prior DB2 releases, the DBD was logged before each DDL statement. Now the DBD is logged at the beginning of a unit of work. To avoid log activity, all CREATE, ALTER and DROP operations for a database should be done at once--and before you issue a COMMIT statement.
  • REORG and LOAD utilities--By default REORG and LOAD log all data during the RELOAD phase. To avoid logging, make sure you use the LOG(NO) parameter. To learn more, see the DB2 Version 9.1 for z/OS Utility Guide and Reference.
  • SQL operations--Depending on the type of SQL statement being used and the type of table space in which the data is stored, you may incur a high amount of logging.
  • INSERT with a fullselect--It's possible to copy data from one table to another through an INSERT statement containing a fullselect from the source table. All data inserted into the table is logged. However, I advise against this option if you're copying thousands of rows. To avoid the logging, consider using the ALTER TABLESPACE NOT LOGGED option, which is new in DB2 V9. This turns off logging for all tables in the table space. Then when logging is reactivated, you'll need to create a backup using the image copy utility. Another option is to use the LOAD utility with LOG(NO) with an inline copy.
  • Mass delete--A mass delete is the execution of a DELETE statement without a where clause. All rows in the table are deleted. With the exception of segmented or universal table space, all rows are logged. You may not realize this, but in the case of segmented universal table spaces, these rows may be logged under these conditions:

1)The table is a parent of a referential constraint
2) The table is defined with DATA CAPTURE(CHANGE)
3) A delete trigger is defined on the table.

If you're considering using mass delete, make sure you create one table per segmented or universal table space. If you have delete triggers, then consider the TRUNCATE statement. Also new with DB2 V9, TRUNCATE is like a mass delete, but it avoids firing the delete triggers.

  • Reorder row format -- The amount of data logged when updating a row depends on whether the row contains all fixed length columns. If all columns are fixed length, DB2 logs from the first column update to the last column updated. So when designing the table, keep frequently updated columns together and towards the beginning of the table. On the other hand, with variable length columns, place the fixed length together, then follow with the variable length at the end of the row. Significantly, all this is done for you in DB2 V9: The new reorder row format feature automatically places all variable length columns at the end of the row. For existing table spaces, run a REORG to convert all rows to reorder row format. With newly created table spaces, all inserted rows are placed in reorder row format.