After reading 'Defining an SQL procedure' section of DB2 UDB for iSeries SQL Programming Concepts V5R2 and 'Returning result sets from stored procedures' section of i5/OS Information Center, Version 5 Release 4, I created the following stored procedure:
CREATE PROCEDURE TESTDAT.REPORT (IN DATESTART DATE, IN DATEEND DATE) LANGUAGE SQL READS SQL DATA BEGIN DECLARE CSR1 CURSOR WITH RETURN FOR SELECT * FROM SYSIBM.SYSDUMMY1; OPEN CSR1; RETURN; END
Next I read 'Example 1: Calling a stored procedure that returns a single result set' section of the same i5/OS Information Center, Version 5 Release 4 and tried to call this procedure from SqlDbx client (via ODBC) or SSRS report (via IBMDASQL provider) using below statement:
CALL TESTDAT.REPORT (DATE('11/01/2013'), DATE('11/03/2013'));
It executed successfully, but did not return the resultset. Executing this thru iSeries navigator successfully returns 2 statements - select and declare cursor. Calling
SELECT * FROM SYSIBM.SYSDUMMY1;
returns IBMREQD column with 1 row of 'Y'. What am I missing from the documents above?
Generated SQL for this procedure includes other options besides already discussed:
SPECIFIC TESTDAT.REPORT NOT DETERMINISTIC READS SQL DATA CALLED ON NULL INPUT SET OPTION ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE , COMMIT = *NONE , DECRESULT = (31, 31, 00) , DFTRDBCOL = *NONE , DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX
Can any of them affect returning of resultset to SSRS?