Mainframe, MVS and zOS Discussion

Why SELECT * is not preferred in embedded SQL programs?

I can think of the following reasons:

• If the table structure is changed ( a field is added ), the program will have to be modified
• Program might retrieve the columns which it might not use, leading on I/O over head.

but are these the only reason for this or there can be other?

Another quick reason is that by coding the full SELECT anyone looking at the program knows which fields it is retrieving and thus, presumably, using.

Thanks nicc. Though I wonder that these are the only reasons to not use it in the programs..

And also when we do an unload using SELECT * and after ALTER new column ADD we might tend to miss out on the LRECL of the file .. Also doing a select * might be fetching a lot of column than what is actually needed too

Looks like NOBODY considered the fact that the GENERAL format is something like
where the relative position in the lists associates the column to the variable
a makes pretty difficult to associate the columns to the variables

SELECT * when all you wanted was columns in an index prevents index-only access. And I'm sure there are other access path no-nos.

It also occurs to me that if column data is (eg RACF) protected then the request will fail, whether you wanted columns you don't have access to or not.

Of course an unqualified row selection is equally problematic, which is what I mistakenly originally thought the question was about. :-)

MartinPacker wrote:SELECT * when all you wanted was columns in an index prevents index-only access. And I'm sure there are other access path no-nos.

It also occurs to me that if column data is (eg RACF) protected then the request will fail, whether you wanted columns you don't have access to or not.

Of course an unqualified row selection is equally problematic, which is what I mistakenly originally thought the question was about. :-)

This is very late to acknowledge the reply but I was not aware about that we can RACF secure some columns out of many. That's a news. But why would someone create a column and then don't allow the access to it?

You don't 'not allow access' - you create a column and restrict access to it i.e. if you have a need to know that data and have the access then you can select it. If you don't have the authority then you cannot.

 and anyway... what happened when You tried !

nicc wrote:You don't 'not allow access' - you create a column and restrict access to it i.e. if you have a need to know that data and have the access then you can select it. If you don't have the authority then you cannot.

Thanks!
enrico, when I tried it worked for the table I used. I thinking that it can be generalized DB2 CPU saving method. In case we need all the columns from a table for a program, and I code SELECT * instead of all columns, I see no difference in CPU. Apart from that, I'm not able to relate any other benefit, apart from what you have said above, we can get by not using it. I mean it's kind of a choice, rather a tip when we need all the columns.

In production code select by column. Using * is fine for a quick one-off but not for a regular thing.

okay, thanks for your answer, nicc.

Just my $ .02...

When you execute an SQL which has SELECT, there are two basic steps which happen under the covers (not always, but for the simplicity of the discussion let's talk about these two tasks) -

[ol][li]To select the qualified rows, use an index [/li]
[li]or get the qualified rows from the table for the application/end-user[/li][/ol]
For instance, if the application just needs the columns which are present in the index, database should not even perform the second step. And usually databases don’t do it. They (can) process query just with the information stored in the index - which means database is doing an index-only scan.

OTOH, if with SELECT * query selects a column that’s not in the index, the database can not do an index-only scan. It has to go through a table-scan. And it has to do it for all such columns. And with SELECT * there are good chances of having many such columns being used in your query and the query will be a CPU hogger.


Basically, in the end, it's all about performance. DB2, from a syntax per se, does not stop you from using SELECT * but performance is an another animal!

Thanks Anuj.