For iSeries Access ODBC: To return data when using embedded SQL in ILE programs, specify the compile option ACTGRP(*CALLER) and not the default of *NEW.
Verify that the program executes a return instead of an exit.
When the stored procedure program executes an exit instead of a return, set the Close SQL Cursor option to *ENDACTGRP. If the Close SQL Cursor option is set to *ENDMOD, the cursor will be closed before data is retrieved.
Also, verify that the CREATE PROCEDURE specifies the correct number of result sets. This is especially important when using array result sets.