Db2 – Not getting a result set in SSRS 2008r2 from an iSeries DB2 SQL stored procedure

db2iseriesssrs-2008stored-procedures

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?

Update:

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?

Best Answer

One thing that you are missing in your stored procedure declaration is the DYNAMIC RESULT SETS option. The default value for that option is 0, may be that's what confuses the client software. Whether it (the client software) can even handle cursors returned by a stored procedure is another story.

Related Question