Resolution for the record:

Interactive SQL is preparing/executing the statement twice. It is done once for the query, and once for the OUTPUT TO statement.

When the result set(s) are cached in memory (that is, when the number of rows is less than the maximum result set rows limit), then we can write the cached rows to the OUTPUT file without having to re-execute the query. But if we cannot cache all the rows, then we have to re-prepare/re-execute the statement for the OUTPUT TO.

This is a change in behavior from previous releases (SQLA 16 to SQLA 17). By default, in previous versions, we would not process all result sets and essentially pause fetching rows when reaching the output rows limit. Then when the OUTPUT statement was encountered, we could dump the cached rows and then continue fetching the remaining rows, and move on to the next result set. The problem with this is that calls to stored procedures would usually result in incomplete processing. Only a part of the first result set was fetched and side effects were missing because the statements leading up to each of the subsequent result sets would not be executed.

Now we always iterate through all the result sets which means closing each one and moving to the next one. When an OUTPUT TO statement is encountered then we have to repeat this (and re-prepare/re-execute unless we have managed to cache all the rows from all of the result sets).

You can get the same behavior in SQLA 16 by enabling the "fetch all result sets" option in Interactive SQL (dbisql).

One work-around / correct way to deal with this is to change the output rows limit to a very large value before executing the statement and the following OUTPUT statement.

Another way to handle this is to store the result set from the stored procedure call into a temporary table and then log the temporary table contents using SELECT and OUTPUT. The SELECT statement would still be executed twice if the row limit is encountered, but the overhead of the system procedure would be eliminated.