sqlentity-framework-coredb2db2-400

IBM.EntityFrameworkCore Error - [42815] [IBM][AS] SQL0451N

I am getting the error below when trying to run a stored procedure using IBM.EntityFrameworkCore against a Db2 database:

IBM.Data.Db2.DB2Exception (0x80004005): ERROR [42815] [IBM][AS] SQL0451N  
The "1" definition, in the statement that defines routine "*N        ", 
contains a data type "FOOSCHEMA.FOONAME" that is not appropriate for a non-sourced routine 
written in the given language or a routine that is defined as autonomous.
   at IBM.Data.Db2.DB2Connection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode)
   at IBM.Data.Db2.DB2Command.ExecuteNonQueryObject(Boolean skipInitialValidation)
   at IBM.Data.Db2.DB2Command.ExecuteNonQueryObject()
   at IBM.Data.Db2.DB2Command.ExecuteNonQuery()

My logic is below

using var db2 = await db2Context.CreateDbContextAsync();
using var command = db2.Database.GetDbConnection().CreateCommand();

command.CommandText = "call FOOSCHEMA.FOONAME(?, ?, ?, ?, ?)";
command.CommandType = System.Data.CommandType.Text;

command.Parameters.Add(new DB2Parameter
{
    ParameterName = "FooInputParam",
    Value = "ST",
    Direction = System.Data.ParameterDirection.Input,
    DB2Type = DB2Type.Char,
    Size = 2,
});

command.Parameters.Add(new DB2Parameter
{
    ParameterName = "FooInputOutputParam",
    Direction = System.Data.ParameterDirection.InputOutput,
    DB2Type = DB2Type.Char,
    Size = 500
});

/* 3 additional `Input` params redacted */

await db2.Database.OpenConnectionAsync();
var commandResult = await command.ExecuteNonQueryAsync(); // ERROR HERE

I did some searching but came up empty on things to try. I am able to successfully call other stored procedures in Db2 using IBM.EntityFrameworkCore in my environment.

Below is the program signature

enter image description here

Does anyone have any ideas of what I need to look at to resolve?

Environment Details

Solution

  • By a process of elimination, the workaround is to ensure that the routine being called is catalogued. in other words the target routine appears in the database catalog (in your case, in qsys2.routines and all related tables for arguments etc.). To catalog a procedure, use the create procedure statement, and when the target procedure is external to the RDBMS, use a specific flavour of that statement as described in the docs here (be sure to read all the related pages, there are many).

    Only IBM can state whether this is a feature or a defect (and if so, in which exact component, it might not be their entity framework support alone) '. So you should open a ticket IBM i-support to get an answer, although you might need to be persistent. You could then update this answer. IBM may also suggest other different workarounds.

    I do know that in the past with the Db2-LUW product (i.e. not i-series) it was possible to invoke uncatalogued external procedures, but that some decades ago IBM made this practice either more difficult or restricted . I never use uncatalogued routines now since recent decades, regardless of the scaffolding/interface.

    For tracing the driver, again refer to IBM, as the tracing details will vary with the driver. That is not a programming matter but a documentation matter, but IBM software is usually able to be traced at runtime for the purposes of problem determination. Their support for the MS Entity Framework seems to be very much an add on ,with minimal documentation, but I believe will still be traceable.