August 13, 2007

DB2 V9 Reordered Row Format–What Does It Mean to Me?

When designing tables for DB2 on z/OS the DBA needs to consider the order of columns on the table. It’s important to put the most common updated columns next to each other and always put variable-length columns at the end of the table. This is because DB2 logs from the beginning of the first column updated to the end of the last column updated unless a variable-length column is updated.  With variable-length columns, DB2 will log from the updated column and all columns after it to the end of the row. When you’re dealing with packages you don’t always have control over the order of the columns on a table.

With DB2 V9 reordered row format (RRF), you no longer need to worry about the order of the variable-length columns. RRF not only helps with reducing logging updated variable-length columns, but also with reading variable-length columns. Prior to DB2 V9 a SELECT of a variable-length column required DB2 to read the first variable-length column to figure out were the next variable-length column is until it gets to the column included in the SELECT statement. The more variable-length columns you have the more time it takes.

With RRF, the physical column layout won’t match the column order as defined in the DB2 catalog. The fixed-length columns will be placed at the beginning of the row, followed by the offsets to the varying length columns, followed by the values of the varying length columns. DB2 uses the offset to perform a direct read to the data instead of traversing through all of the columns searching for the correct data. The more variable-length columns you have on a table the more performance benefit you can expect to see from this feature. 

This results in better performance and reduced CPU without changing your application or your table design. After you upgrade to DB2 V9 you’ll want to run a REORG or LOAD REPLACE on existing table spaces to convert them to RRF. If the table has an EDITPROC or VALIDPROC, then it will remain in basic row format. See the administration guide for instructions on how to convert tables containing validation procs.

Some people like to use DSN1COPY to copy data from one subsystem to another. If you do so, make sure both source and target table spaces have been converted to RRF. Use the following query against the DB2 catalog to determine the format: 

       WHEN ‘R’ THEN ‘Reordered Row Format’
       WHEN ‘ ‘ THEN ‘Basic Row Format’
   END format
WHERE ( DBNAME = ’source-database-name’
      AND TSNAME=’source-table-space-name’ ) 
               (  DBNAME = ’target-database-name’
      AND TSNAME=’target-table-space-name’ )