If you’ve worked with DB2 for awhile, especially as a DBA, you’ve probably heard the advice to re-arrange the columns of your tables to optimize logging efficiency. Basically, the more data that DB2 has to log, the more overhead your programs will incur, and performance will degrade. DB2 will log data from the first byte changed to the last byte changed – unless the row is variable, in which case DB2 will log from the first byte changed to the end of the row – unless the change does not cause the length of the variable row to change, in which case DB2 goes back to logging from the first byte changed to the last byte changed.
So, the advice goes something like this: put you static columns (those that do not change frequently) at the beginning of the row and your dynamic columns (those that will change more frequently) at the end of the row. And put your variable columns at the end of each. This would make your row look something like this:
[Static fixed-length cols]
[Static variable cols]
[Dynamic fixed-length cols]
[Dynamic variable cols]
Make sense?
Well, DB2 9 for z/OS takes this advice to heart (sort of). In New Function Mode (NFM), for new table spaces, DB2 will automatically put the variable columns at the end of the row. This is called reordered row format (RRF); the row format we are all familiar with today is now referred to as basic row format (BRF). This is all how the row is stored – it does not mean that your DDL is changed nor does it require changes to anything external or how you access the rows.
To summarize, this means that a row in RRF will store the fixed-length columns first and the variable columns at the end. Pointers within the row will point to the beginning of the variable columns.
So far so good, right? Well, we DB2 will also convert our old table spaces to RRF over time. Once we are in DB2 9 NFM, a REORG or a LOAD REPLACE will cause a change from BRF to RRF. So run a LOAD REPLACE a table space in NFM and the row format changes. REORG a partition and the row format for that partition changes. And yes, you can have a partitioned table space with some partitions in BRF and some in RRF.
With BRF we can be sure that DB2 is putting our variable columns at the end of the row – where they belong. But it still is not helping us with placing static columns before the dynamic ones. You’ll still have to guide DB2 to do that.
News, views, and items of interest on IBM's Db2 database management system and mainframes.
It seems, the parsing of DB2 log UNDO/REDO records is problematic with the RRF.
ReplyDeleteLet's assume, there is a table including 4 columns with several varying length among them.
After insertion (SQL INSERT), a DB2log UNDO/REDO record containing these 4 columns (DATA CAPTURE CHANGES has been specified) will be created.
Let's add now one more fixed length column (ALTER TABLE ADD)
and then delete the inserted row -the new UNDO/REDO record still includes 4 columns in spite of the fact that the table has been altered.
When we do the same actions (insertion/deletion) after altering the table, the relative UNDO/REDO records DO reflect the new (altered) table format and contain all 5 columns.
There is not any indicator in log records, how much columns they include, no a number of columns, nor a length of a fixed part.
The parsing can not be done correctly when there are variable length columns and an added one is fixed (is added at the middle of the record).
This problem does not exist in BRF tables where new columns are added at the end of a row (ENDO/REDO record).
Can this "feature" be turned off?
ReplyDeleteSeems to me that any application coded with Select * will get "interesting" results, until they can correct this poor coding practice.
I feel a bit uneasy with DB2 moving columns around in a table...
It is possible, though not necessarily recommended, to switch off RRF using a hidden DSNZPARM (SPRMRRF)
ReplyDeletePlease note, though, that SELECT * will not changes the order of the columns retrieved regardless of BRF or RRF. Even though the columns change location as stored on disk, they are still returned to the program in the order of the DDL (DCLGEN).