Writing an external procedure to return result sets to a distributed client

An external procedure can return multiple query result sets to a distributed client if the value of DYNAMIC RESULT SETS in the stored procedure definition is greater than 0.

Procedure

For each result set you want returned, your stored procedure must complete the following steps:
1. Declare a cursor with the option WITH RETURN.
2. Open the cursor.
3. If the cursor is scrollable, ensure that the cursor is positioned before the first row of the result table.
4. Leave the cursor open.
For example, suppose you want to return a result set that contains entries for all employees in department D11. First, declare a cursor that describes this subset of employees:
```
EXEC SQL DECLARE C1 CURSOR WITH RETURN FOR
  SELECT * FROM DSN8C10.EMP
    WHERE WORKDEPT='D11';
```
Then, open the cursor:
```
EXEC SQL OPEN C1;
```
Db2 returns the result set and the name of the SQL cursor for the stored procedure to the client.
When the stored procedure ends, Db2 returns the rows in the query result set to the client.

Db2 does not return result sets for cursors that are closed before the stored procedure terminates. The stored procedure must execute a CLOSE statement for each cursor associated with a result set that should not be returned to the DRDA client.
Use meaningful cursor names for returning result sets.
The name of the cursor that is used to return result sets is made available to the client application through extensions to the DESCRIBE statement.
Use cursor names that are meaningful to the DRDA client application, especially when the stored procedure returns multiple result sets.
You can use any of these objects in the SELECT statement that is associated with the cursor for a result set:
Tables, synonyms, views, created temporary tables, declared temporary tables, and aliases defined at the local Db2 subsystem.
Return a subset of rows to the client by issuing FETCH statements with a result set cursor. does not return the fetched rows to the client program.
Db2 does not return the fetched rows to the client program. For example, if you declare a cursor WITH RETURN and then execute the statements OPEN, FETCH, and FETCH, the client receives data beginning with the third row in the result set. If the result set cursor is scrollable and you fetch rows with it, you need to position the cursor before the first row of the result table after you fetch the rows and before the stored procedure ends.
You can use a created temporary table or declared temporary table to return result sets from a stored procedure.
This capability can be used to return non-relational data to a DRDA client. For example, you can access IMS data from a stored procedure by using the following process:
1. Use APPC/MVS to issue an IMS transaction.
2. Receive the IMS reply message, which contains data that should be returned to the client.
3. Insert the data from the reply message into a temporary table.
4. Open a cursor against the temporary table. When the stored procedure ends, the rows from the temporary table are returned to the client.