Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
BTW, if your server is running IBM i 7.1, then I recommend sticking with the 7.1 Info Center: publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp –  WarrenT Oct 30 '13 at 0:39
    
I mentioned earlier in another question that we are on 5. –  ajeh Oct 30 '13 at 13:55
add comment

3 Answers 3

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.

share|improve this answer
    
With or without DYNAMIC RESULT SETS 1, the procedure does not return data to SSRS. –  ajeh Oct 30 '13 at 13:19
    
I would phrase it like this: "SSRS cannot read data returned by the procedure". –  mustaccio Oct 30 '13 at 13:49
    
Although I do get resultset in SquirrelSql via JTOpen AS/400 driver with or without DYNAMIC RESULT SETS 1. I agree, that this is better phrased like SSRS problem with iSeries stored procedure. –  ajeh Oct 30 '13 at 13:50
add comment
up vote 2 down vote accepted
+100

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

share|improve this answer
add comment

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?

share|improve this answer
    
This was a purely hypothetical SQL which I used as a guarantee that it would return a result set of a known structure. The real question asked here is why am I not getting resultset via ODBC. –  ajeh Oct 30 '13 at 0:09
    
See the answer from @mustaccio –  WarrenT Oct 30 '13 at 0:17
    
If you want to see the answer in iNavigator results, try creating a table function [ publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/… ] –  WarrenT Oct 30 '13 at 0:22
    
No, that's not the goal. I have to find a way to report on the iSeries parametrized stored procedures in SSRS. –  ajeh Oct 30 '13 at 16:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.