MainframeSupport ønsker dig god påske. Næste tip på dansk udkommer mandag den 4. april 2005

MainframeSupport wishes you a happy easter. The next tip will be published monday the 4th of april 2005.

We have always been told not to use SELECT * in static SQL statements, but have you ever heard that it is generally a good idea to SELECT only the columns you need. Let me explain why.

DB2 uses a three layer architecture when executing a SQL statement. The first layer is called RDS (Relational Data Services), the next layer is the Data Manager (DM) and the third layer is the Buffer Manager (BM). The BM reads pages from DASD into the buffer pool. The DM extracts rows from the pages in the buffer pool while RDS processes the columns in the extracted rows and creates the result that is returned to our application programs. The bottom line is that RDS has to perform twice as much work when returning four columns instead of two.

Now it is easy to conclude that SELECT * is a bad idea both in static and dynamic SQL. Therefore you need to limit your SELECT list to the columns you really need. Do not include columns that you might use in some future version of your program, and when you make dynamic SQL calls where you want to view the contens of column X, Y and Z, then do not perform a SELECT * because it is faster to write, especially if the table contains a lot of columns.

I think it is difficult to obey these fairly simple rules in our daily work, but it will make our DB2 systems feel better. The problem is that leaving out some columns doesn't affect the elapsed time as we experience it at our terminals. You have to look at the total ressource consumption of DB2 to see the effect of selecting fewer columns. For static SQL calls performed a lot of times there will be a measurable effect when reducing the number of columns selected, so concentrate your tuning efforts on static SQL in the first place and then move on to dynamic SQL embedded in programs or other kind of applications performed on a regular basis.