Most of my experience is with SQL Server and I'm trying to call a DB2 stored procedure on an AS/400 (V5R3) through ASP .NET 2.0 that returns a result set . I don't have direct access to the AS/400 (DB2 V8 I believe, I haven't gotten a response back about DB2 version). The AS/400 operator that created the stored procedure told me to call the stored proc like this:

Select * from Table(PFMSFN( :ParmDriver, :ParmStrDt, :ParmEndDt)) as Pfm1
ParmDriver defined as 6 char, Required
ParmStrDt defined as 10 char, format YYYY-MM-DD, Required
ParmEndDt defined as 10 char, format YYYY-MM-DD, Required

I've tried various methods to call this based on the IBM redbook for the .NET data provider and the method the operator suggested, but it always fails. I've tried using both the IBM.Data.DB2.iSeries namespace and the OleDb namespace. I've tried hardcoding test values into the call:

call IDCPFMSFN('ABCDEF', '2001-01-01', '2007-01-01')

And setting up parameters in various ways (here are examples of 3 different variations):

Cmd.Parameters.Add("@ParmStrDt", iDB2DbType.iDB2Char, 10, ":ParmStrDt");
Cmd.Parameters.Add("@ParmStrDt", iDB2DbType.iDB2Char, 10,);
Cmd.Parameters.Add(":ParmStrDt", iDB2DbType.iDB2Char, 10,);

I also tried using DeriveParameters() just to try connecting to the stored proc:

iDB2Command Cmd = new iDB2Command("PFMSFN", CommandType.StoredProcedure, Conn);

I keep getting errors like "SQL0204 PFMDFN in type *N not found." (for the proc call) and "The stored procedure cannot be found in SYSPROCS, or the procedure name is ambiguous." (for DeriveParameters).

I have also tried using the LibraryList in the connection string and the full path in the proc call ("Call i99.PFMDFN") with no luck. The AS/400 operator mentioned the library list is set, so there
should be no need to qualify the library. I've also been told the stored procedure is there and that I have permissions to it.

What is the correct way to call this stored procedure? What affect do the colons have in the param names?

Also, I haven't seen a stored proc called this way before, with a select statement. Is this common for DB2 and would it be neccessary to call it like this? I haven't seen any examples like this for DB2 on the Internet.

If I was to ask the AS/400 operator about checking the logs to see what is happening on the server side, what would be checked? If someone could direct me to info on the Internet about DB2 logging on the AS/400, that would be appreciated.