How dangerous or expensive is it to issue a SELECT * FROM some very large table with no WHERE clause, no GROUP BY and no ORDER BY? The answer is that it depends a lot on the tool you are using to issue the statement. The execution of the statement from a DB2 point of view is the same whether you are using your own selfmade program or SPUFI, QMF, DSNTEP2, SAS or other vendor products. To read the first row the application has to issue a FETCH, to read the next row it has to issue another fetch and so on. It is completey up to the application to stop FETCH'ing.

When DB2 processes a SELECT * FROM without any clauses then DB2 knows that no rows has to be filtered and no sorting has to be done. Therefore DB2 will return a row from the table in the fastest possible way. If the application stops FETCH'ing then DB2 stops reading rows from the table. So no matter how large the table is rows will be returned to the application very fast. In other words the application has total responsability for the cost of the execution.

In QMF it is totally harmless to issue a SELECT * FROM without clauses on 99% of all installations. QMF has a built in parameter for the maximum number of FETCH'es that will be issued for any SELECT statement. The maximum is determined at installation time, but it is possible to override it at QMF startup time. To override the maximum you have to make your own startup procedure for QMF.

In SPUFI it is also relatively harmless to issue the SELECT * FROM without clauses, because SPUFI has a parameter controlling the maximum number of rows to be returned. You can change this parameter before each SQL statement execution and if you set it to zero then all rows will be returned and this may not be what you wanted. If you are lucky the output dataset runs out of space. Many other tools like File-AID for DB2 and Access for DB2 has a builtin maximum limit that can be adjusted before the next SQL statement execution.

In your own application you are in control, but in SAS, DSNTIAUL and DSNTEP2 you must be very careful. These programs doesn't stop until the last row has been FETCH'ed.