We're contemplating changing a whole bunch of our programs to switch from using a simple SELECT * to instead have all columns listed on the programs.

The underlying assumption is that the program has to go lookup the catalog to figure out the column listing on a SELECT * and by listing the columns instead (and we'd simply be replacing the * with ALL columns on the table), we'd skip that catalog lookup, thereby improving performance.

Is there any documentation that discusses this aspect?

Thanks
rm

If you are using static SQL, it will not involve a catalog lookup at execution time. Static SQL would normally be used in most COBOL or PLI programs (but not always). If you are using a query tool, you have dynamic SQL and a catalog lookup is necessary in any case.

However, you should never use select * in a program since the table may change (if you add a column, etc) and the program may no longer work.

It is static embedded SQL in all these programs - we understand the impacts on the table structure changes etc. Wondering if you could point to some documentation online (ibm site perhaps) that supports this. My instinct was to doubt that assertion as well, but I'm hoping to find some documents that support the theory in either case...

Thanks for the response.

It is not a theory.

Actually, as in all theories, there are exceptions.

The exception in this case is if you are prepared to use "DESCRIBE CURSOR c1 INTO :mysqlda" and then use the appropriate processing to allocate storage for the actual columns the SELECT * will be retrieving.

But if you aren't prepared to do this work up front ...

http://www.dbazine.com/mullins_view.shtml/ (specifically orientated to "create view (...) as select * ..."
http://expertanswercenter.techtarget...979420,00.html


James Campbell

That is not static SQL, it is dynamic. It is embedded SQL, but not static.

There may be some catalog access required, even for static SQL. This might include plan/package authorizations. But DB2 will not have to resolve the list of columns from the catalog if the program is using static embedded SQL.

You should have your syscatspace in a bufferpool that is large enough to hold most of the catalog in memory, and large enough to not get flushed out of memory if you are sharing the bufferpool with other tablespaces.

Because of the headaches that would be caused by schema changes, anyone who uses Select * in embedded static SQL should be drawn and quartered.