Db2 – How efficiently does iSeries DB2 use index with WHERE <>

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.

As Heinz Z. did, I discovered the problem.

One of your function parameters is char, while you pass the string literal 'ANY', which is considered a varchar. The database engine looks for an overloaded version of the function with varchar parameters, but doesn't find it.

Solution is either

• changing function parameter to varchar

• or cast parameter to char in the function call:

  SELECT * 
  FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'), 
                              DATE('10/23/2013'), 
                              CAST('ANY' AS CHAR(3))
            )) AS T
  

If doesn't work, try in any case to remove all parameters from the function and see if they are the culprits. Then you can investigate deeper adding one by one, try to work on dates format, for example you can try to pass current date instead of 10/23/2013.

Also you must investigate on why it thinks that FNREPORT is a type and not a function...