Hi All !

In the Db2 for LUW world, does the order of columns in a table impact performance ?
I know that on DB2 for z/OS (mainframe), you always tried to place frequently referenced columns before VARCHAR columns. Besides, column placement also influenced the amount of data that is logged during updates.

Are these considerations during table design for DB2 tables on Linux (or any LUW platform) ?

Thanks
Anil

In DB2 for LUW, it automatically puts varchar columns at the end. I am not sure what happens when you alter a table to add a column.

well if you alter the table it certainly appends the columns at the end .but i guess if the table is properly indexed the column structure will rarely be a cause of performance.

please share your research on this.

Cheers,
sinwar

The issue of physical column placement has to do with logging of UPDATEs to the row in the DB2 transaction log.

With DB2 for z/OS it is well documented that on an update, only the data changed will be logged, except that if VARCHAR data length is changed (likely any time a VARCHAR column is updated) then DB2 needs to log from the changed data to the end of the row. Therefore it is often recommended that VARCHAR columns be placed at the end of the row if they are often updated.

In DB2 for Linux, UNIX, and Windows the logging details are less well documented, but I have heard from IBM'ers that DB2 automatically will place VARCHAR columns physically at the end of a row when a table is created, regardless of the order of the columns as listed in the sycat.columns catalog table. But I have not seen any written documentation to verify this.

Of course, all of this only affects logging performance of updates of VARCHAR columns (for INSERTs and DELETEs the entire row is logged, and for SELECTs there is no logging). Whether any of this makes a noticeable difference in performance in a DB2 database is questionable unless there is a high transaction rate of UPDATEs to VARCHAR columns.