i am studying db2 dynamic sql.

I wrote this very simple script test.sql:

begin atomic
select * from table1 fetch top 5 rows only;
end@

Invoking this script with:
db2 -td@ test.sql -z test.log

The test.log does't contain the result, only one message saying the command succeeded.

If I change the test.sql to be non-inline:
select * from table1 fetch top 5 rows only@

then the test.log contains 5 records.

Could you please kindly explain why this is the case?

sorry, didn't say the version of DB2. I use client API to connect to DB2 server, and the client API is 8. So I guess I am using DB2 8?

The CLP only works on statements, one at a time. In your first scenario, you have just one statement, it happens to be a compound statement. Whatever that compound statement does internally is not reported back, just the results of the entire statement. Compound statements are usually reserved for doing atomic work (update, insert, delete), not returning result sets.

Andy

to return a result set use stored procedure instead