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

Question

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?

Answer 1

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.

Answer 2

The solution to reporting on iSeries SQL stored procedure in SSRS is to install DB2UDB .Net provider that comes with IBM Client Access into BIDS by adding two XML tags to RSReportDesigner.config.

Add the following to <Data> tag in .config:

<Extension Name="DB2UDB" Type="IBM.Data.DB2.iSeries.iDB2Connection,IBM.Data.DB2.iSeries, Version=12.0.0.0, Culture=neutral, PublicKeyToken=9CDB2EBFB1F93A26"/>

Add the following to <Designer> tag:

<Extension Name="DB2UDB" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/>

That will add DB2UDB to a list of providers in the datasource properties. Then you can use a connection string with the following format in the data sources:

Data Source=<IP of FQDN>;Initial Catalog=<DB name>;UserID=<user ID>;Password=<pass>;

The user ID and password need to be entered into connection string, as the provider will not be able to use them if entered into Credentials tab.

SSRS report wizard cannot create a report, based on an SP. A report based on a temporary select statement should be used instead (select * from sysibm.sysdummy1 or any other quick select would do).

Once a report is created, its dataset type can be changed from query to stored procedure. SP name has to be entered manually (drop down is empty). Parameters have to be entered manually (it will not refresh fields until the parameters entered). Parameter names need to match SP and parameter values entered as [@<param name>]. The Date parameters have to be specified as Text type or Date/Time with value expression similar to this:

=FormatDateTime(Parameters!DATESTART.Value, DateFormat.ShortDate)

If using Text type, enter dates without quotes, ex: 10/31/2013

Answer 3

iSeries Navigator did not "return" two statements. It echoed them in the messages tab, NOT the data results tab.

SYSIBM.SYSDUMMY1 is an IBM supplied table with 1 column and one row. You got the correct answer for the procedure you wrote.

SYSDUMMY1 is generally used in example code as a proxy to evaluate expressions that do not require any table. For example:

SELECT monthname(CURRENT DATE)
    FROM SYSIBM.SYSDUMMY1;

This is an unnecessary construct however, since the VALUES statement works just as well in these situations, and requires no table or view as a data source

VALUES monthname(CURRENT DATE);

Where SELECT INTO would be used, VALUES INTO is the equivalent.

So now that we've eliminated your need to use SYSDUMMY1, the question remains what is it you are trying to accomplish?