Global Data Management Forum

 View Only
Expand all | Collapse all

IBM.EntityFrameworkCore (6.0.0.200) in .NET 6.0 Razor project

  • 1.  IBM.EntityFrameworkCore (6.0.0.200) in .NET 6.0 Razor project

    Posted Fri August 05, 2022 09:03 AM
      |   view attached
    I used the following blog article from Michelle Betbadal as my initial guidance. My project is a Visual Studio Razor project. Hence, not MVC and thus, no controllers. Principles for using Entity Framework are the same though. The only MAIN DIFFERENCE between her example and my project IS, I am calling a stored procedure I created on the IBM I. Many thanks in advance to anyone who can help me understand what the problem is so I can correct it and move on.

    I'll start first with the error I am getting.
     
    IBM.Data.Db2.DB2Exception (0x80004005): ERROR [08001] [IBM] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "10.100.255.1". Communication function detecting the error: "connect". Protocol specific error code(s): "10061", "*", "*". SQLSTATE=08001at IBM.Data.Db2.DB2ConnPool.Open(DB2Connection connection, String& szConnectionString, DB2ConnSettings& ppSettings, Object& ppConn)at IBM.Data.Db2.DB2Connection.Open()at Customer_Portal.Pages.Activity.IndexModel.OnGetAsync(String id) in G:\Visual Studio Projects\Customer Portal\Pages\Activity\Index.cshtml.cs:line 102

    Package dependencies installed:
    Id Version
    -- ------- 
    Microsoft.AspNetCore.Mvc.Razor.RuntimeCompilation 6.0.7
    Microsoft.Extensions.Configuration.UserSecrets 6.0.1
    Microsoft.CodeAnalysis.CSharp.Workspaces 4.2.0
    Microsoft.EntityFrameworkCore 6.0.7
    Microsoft.EntityFrameworkCore.SqlServer 6.0.7
    IBM.EntityFrameworkCore 6.0.0.200
    Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore 6.0.7
    Microsoft.AspNetCore.Identity.EntityFrameworkCore 6.0.7
    IBM.Data.DB2.Core 3.1.0.600
    System.Linq.Dynamic.Core 1.2.19
    Microsoft.EntityFrameworkCore.Tools 6.0.7
    MimeKit 3.3.0
    Microsoft.VisualStudio.Web.CodeGeneration.Design 5.0.2
    MailKit 3.3.0
    Microsoft.AspNetCore.Identity.UI 6.0.7

    Here are debug.print pseudo sanity checks when running my program, from start to error.
    Startup.cs::ConfigureServices()::BEGIN
    Startup.cs::ConfigureServices()::Connection String: Database=MYHOST;Server={IP ADDRESS OF IBM I};UserID={MY UID};Password={MY PWD};PersistSecurityInfo=True
    Startup.cs::ConfigureServices()::END
    Startup.cs::Configure()::BEGIN
    Startup.cs::Configure()::END
    Microsoft.Hosting.Lifetime: Information: Now listening on: https://localhost:5001
    Microsoft.Hosting.Lifetime: Information: Now listening on: http://localhost:5000
    Microsoft.Hosting.Lifetime: Information: Application started. Press Ctrl+C to shut down.
    Microsoft.Hosting.Lifetime: Information: Hosting environment: Development
    Microsoft.Hosting.Lifetime: Information: Content root path: G:\Visual Studio Projects\{MY PROJECT FOLDER}
    OnGetAsync()::Examining parameters about our DB2 context...
    OnGetAsync():: _db2Context.ContextId = cf0454ad-598c-4d2c-bca1-dd1568ed0eb5:0
    OnGetAsync():: _db2Context.Database.GetConnectionString() = Database=MYHOST;;Server={IP ADDRESS OF IBM I};UserID={MY UID};Password={MY PWD};PersistSecurityInfo=True
    OnGetAsync():: _db2Context.Database.CanConnect()= False
    OnGetAsync():: _db2Context.Database.ProviderName = IBM.EntityFrameworkCore
    OnGetAsync():: Intending to run this command: _db2Context.Activities.FromSqlRaw(procCall).ToList()
    OnGetAsync():: Value in variable named procCall: CALL {MYLIB}.{MY STORED PROCEDURE}(16077908,01,7597610,01)
    OnGetAsync():: Setting _db2Context Database facade timeout: 300
    OnGetAsync()::Examining database facade db connection: System.Data.Common.DbConnection dbCon = _db2Context.Database.GetDbConnection()
    OnGetAsync():: dbCon ConnectionState.Closed
    OnGetAsync():: Attempting to open db connection: dbCon.Open()
    Exception thrown: 'IBM.Data.Db2.DB2Exception' in IBM.Data.Db2.dll
    IBM.Data.Db2.DB2Exception (0x80004005): ERROR [08001] [IBM] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "{IP ADDRESS OF IBM I}". Communication function detecting the error: "connect". Protocol specific error code(s): "10061", "*", "*". SQLSTATE=08001
    at IBM.Data.Db2.DB2ConnPool.Open(DB2Connection connection, String& szConnectionString, DB2ConnSettings& ppSettings, Object& ppConn)
    at IBM.Data.Db2.DB2Connection.Open()
    at Customer_Portal.Pages.Activity.IndexModel.OnGetAsync(String id) in G:\Visual Studio Projects\{MY PROJECT FOLDER}\Pages\Activity\Index.cshtml.cs:line 102

    There is a file named db2diag.log that gets updated whenever I run my project. It's in this folder: C:\ProgramData\IBM\DB2\G_Visual Studio Projects_{MY PROJECT NAME}_bin_x64_Debug_net6.0_clidriver. I'll attach the file. 

    Summary rundown of my project...

    In Startup.cs...
    using IBM.EntityFrameworkCore;
    using IBM.EntityFrameworkCore.Storage.Internal;
    .
    .
    public void ConfigureServices(IServiceCollection services)
    {
    .
    .
    var connection = "Database=gsDBName;" +
    ";Server=" + gsDataSource +
    ";UserID=" + gsUid +
    ";Password=" + gsPwd +
    ";PersistSecurityInfo=True";

    services.AddDbContext<IbmDb2Context>(options =>
    options.UseDb2(
    connection,
    p => p.SetServerInfo(IBMDBServerType.AS400, IBMDBServerVersion.AS400_07_02))
    );
    .
    .

    My \Data\IbmDb2Context.cs...
    using System;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.EntityFrameworkCore.Metadata;
    using Customer_Portal.Models;

    namespace Customer_Portal.Data
    {
    public partial class IbmDb2Context : DbContext
    {
    public IbmDb2Context()
    {
    }
    public IbmDb2Context(DbContextOptions<IbmDb2Context> options) : base(options)
    {
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);

    public virtual DbSet<Activity> Activities { get; set; }
    }
    }

    My Models\Activity.cs file ...
    using Microsoft.EntityFrameworkCore;
    using System.ComponentModel;

    namespace {My Project Namespace}.Models
    {
    /*
    * Property members for For stored procedure results
    * proc name: {Redacted for forum}
    * Inbound parameters:
    * TRIPNO NUMERIC(8)
    * SEQNO NUMERIC(2)
    * ACCTNO NUMERIC(7)
    * MBRNO NUMERIC(2)
    * Outbound resultset:
    * TripNum, BillSeqNum, AcctNum, MbrNum, HdrSvcDate, LineNumDate,
    * Descriptn, Units, Amount, Typ, Descriptn
    */

    [Keyless]
    public partial class Activity
    {
    [DisplayName("Trip Number")]
    public string TripNum { get; set; }

    public string BillSeqNum { get; set; }

    [DisplayName("Account Number")]
    public string AcctNum { get; set; }
    public string MbrNum { get; set; }

    [DisplayName("Service Date")]
    public string HdrSvcDate { get; set; }
    public string LineNumDate { get; set; }
    public string Descriptn1 { get; set; }
    public string Units { get; set; }
    public string Amount { get; set; }

    [DisplayName("Type")]
    public string Typ { get; set; }
    public string Descriptn2 { get; set; }
    }
    }

    My Pages\Index.cshtrml.cs 

    public async Task<IActionResult> OnGetAsync(string id)
    {
    .
    .
    try
    {
    DisplayMsg($"OnGetAsync()::Examining parameters about our DB2 context...");
    DisplayMsg($"OnGetAsync()::\t_db2Context.ContextId = {_db2Context.ContextId}");
    DisplayMsg($"OnGetAsync()::\t_db2Context.Database.GetConnectionString() = {_db2Context.Database.GetConnectionString()}");
    DisplayMsg($"OnGetAsync()::\t_db2Context.Database.CanConnect()= {_db2Context.Database.CanConnect()}");
    DisplayMsg($"OnGetAsync()::\t_db2Context.Database.ProviderName = {_db2Context.Database.ProviderName}");
    DisplayMsg($"OnGetAsync()::\tIntending to run this command: _db2Context.Activities.FromSqlRaw(procCall).ToList()");
    DisplayMsg($"OnGetAsync()::\tValue in variable named procCall: {procCall}");

    DisplayMsg($"OnGetAsync()::\tSetting _db2Context Database facade timeout: 300");
    _db2Context.Database.SetCommandTimeout(300);

    DisplayMsg($"OnGetAsync()::Examining database facade db connection: System.Data.Common.DbConnection dbCon = _db2Context.Database.GetDbConnection()");
    System.Data.Common.DbConnection dbCon = _db2Context.Database.GetDbConnection();
    switch (dbCon.State)
    {
    case ConnectionState.Closed:
    DisplayMsg($"OnGetAsync()::\tdbCon ConnectionState.Closed");
    DisplayMsg($"OnGetAsync()::\tAttempting to open db connection: dbCon.Open()");
    dbCon.Open();
    break;
    case ConnectionState.Open:
    DisplayMsg($"OnGetAsync()::\tdbCon ConnectionState.Open");
    DisplayMsg($"OnGetAsync()::\tAttempting to run command and get data");
    OCurAcctActivities = _db2Context.Activities.FromSqlRaw(procCall).ToList();
    break;
    }
    }
    catch (Exception e)
    {
    .
    .

    To test that my stored project on the IBM I works, I've used the Run SQL Scripts utility in my 5250 Emulator program, and it works and I get data results back. I also created use a Windows Desktop Tester project which uses .Net Framework 4.7.2 and IBM.Data.DB2.iSeries (2.0.50727) to call my stored procedure using C# and I get data results back.

    My Local Environment...
    DB2CLP=DB20FADE
    DB2DSDRIVER_CFG_PATH=C:\ProgramData\IBM\DB2
    DB2INSTANCE=DB2
    DB2PATH=C:\Program Files\IBM\SQLLIB

    ------------------------------
    Joseph Perez
    ------------------------------

    #DataManagementGlobal
    #DataServerDrivers

    Attachment(s)

    log
    db2diag.log   1 KB 1 version


  • 2.  RE: IBM.EntityFrameworkCore (6.0.0.200) in .NET 6.0 Razor project

    Posted Mon August 08, 2022 06:22 AM
    Hi @Joseph Perez,

    Thanks for providing all the details. I've following observations:
      - Please confirm the service port on IBM i data server where DRDA messages are listened to?
      - ​Server={IP ADDRESS OF IBM I} seems to have only IP? If yes, you would need to append the port number to it e.g. Server={IP ADDRESS OF IBM I}:drdaPortNumber (please notice colon too).
      - IBM.Data.DB2.iSeries .NET Provider is part of IBM i Access for Windows - .Net Data provider and AFAIK, does not require any additional license.
      -
    IBM.EntityFrameworkCore 6.0.0.200 has dependency on Net.IBM.Data.Db2 than IBM.Data.DB2.Core 3.1.0.600.

    Here are some generic info about SQL30081N and SQL10007N (from db2diag.log):
      - SQL30081N TCP/IP communication errors - IBM Documentation
      - SQL10000 - SQL19999 - IBM Documentation

    NOTE:
    Db2 Connect .NET Provider would required a valid license to get connected to IBM i.

    Regards,
    Devendra


    ------------------------------
    Devendra Singh Bhadauria
    ------------------------------



  • 3.  RE: IBM.EntityFrameworkCore (6.0.0.200) in .NET 6.0 Razor project

    Posted Tue August 09, 2022 06:08 PM
    Hi Devendra, thanks for replying. I modified my connection string so that it now includes the port number of 446. I verified on the I via Netstat that DRDA is in listening mode but I also noticed that in the local and report port columns, there was only an asterisk (*). I also used Nuget for the solution to download and install Net.IBM.Data.Db2. When I run my project and get to the page where I need to run my stored procedure, I am now getting this error message.

    IBM.Data.Db2.DB2Exception (0x80004005): ERROR [42968] [IBM] SQL1598N An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968

    at IBM.Data.Db2.DB2ConnPool.Open(DB2Connection connection, String& szConnectionString, DB2ConnSettings& ppSettings, Object& ppConn)
    at IBM.Data.Db2.DB2Connection.Open()
    at Customer_Portal.Pages.Activity.IndexModel.OnGetAsync(String id) in G:\Visual Studio Projects\Customer Portal\Pages\Activity\Index.cshtml.cs:line 101

    ------------------------------
    Joseph Perez
    ------------------------------



  • 4.  RE: IBM.EntityFrameworkCore (6.0.0.200) in .NET 6.0 Razor project

    Posted Wed August 10, 2022 03:07 AM
    Edited by System Test Fri January 20, 2023 04:09 PM
    Hi @Joseph Perez,

    After rectification of other issues, It seems you don't have a valid license for Db2 Connect. In case your team or organization does not have it, please reach @Hosathota Vishwanatha for a trial license​​​​.

    This is high level of Db2 Connect:
    Db2 Connect | IBM

    Regards,
    Devendra

    ------------------------------
    Devendra Singh Bhadauria
    ------------------------------



  • 5.  RE: IBM.EntityFrameworkCore (6.0.0.200) in .NET 6.0 Razor project

    Posted Fri August 12, 2022 02:14 PM
    Hi Devendra. I sent a message to Hosathota a few days ago about the license and has not responded. Can you reach out to him? I'm a little confused though. I have a different project, windows desktop app, that uses IBM.Data.DB2.iSeries, and have no problem logging onto our IBM I and querying our DB2 database. But as I demonstrated in my Razor web app that uses IBM.EntityFrameworkCore and IBM.EntityFrameworkCore.Storage.Internal, I'm having no such luck. Why is their a requirement for a license in one while the other does not? It's particularly interesting because our copy of DB2 is on the IBM I and afaik it is licensed along with the OS.

    ------------------------------
    Joseph Perez
    ------------------------------



  • 6.  RE: IBM.EntityFrameworkCore (6.0.0.200) in .NET 6.0 Razor project

    Posted Tue August 16, 2022 06:41 AM
    Hi Joseph,
     apologies for the delay in getting back to you and please check your forum inbox to proceed further.

    IBM.EntityFrameworkCore (and its dependency drivers) are much more advanced and latest drivers and are not specific to IBM i.

    In terms of licensing, Db2Connect license is separate one which can be used to connect not only to IBM i but also to zOS, Informix and IBM LUW servers. A separate license is needed when connecting to IBM i and zOS database servers.

    Thanks and regards
    Vishwa

    ------------------------------
    Hosathota Vishwanatha
    ------------------------------



  • 7.  RE: IBM.EntityFrameworkCore (6.0.0.200) in .NET 6.0 Razor project

    Posted Tue August 16, 2022 11:37 AM
    Thanks Vishwa. I've sent the email. I'm also working with our IBM contact in obtaining a purchased license for DB2 connect.

    ------------------------------
    Joseph Perez
    ------------------------------



  • 8.  RE: IBM.EntityFrameworkCore (6.0.0.200) in .NET 6.0 Razor project

    Posted Mon August 22, 2022 11:38 AM
    @Hosathota Vishwanatha, @Devendra Singh Bhadauria, thank you fo​​r all your help. With the trial license, I am now able to connect to our IBM I Db2, call my stored procedure and get a datareader back with data.

    ------------------------------
    Joseph Perez
    ------------------------------



  • 9.  RE: IBM.EntityFrameworkCore (6.0.0.200) in .NET 6.0 Razor project

    Posted Thu August 25, 2022 01:47 PM
    Hello, How can we get a License to use it in our Company, also how long it is the Trial License ?

    ------------------------------
    Christian Polychroniadis
    ------------------------------



  • 10.  RE: IBM.EntityFrameworkCore (6.0.0.200) in .NET 6.0 Razor project

    Posted Wed December 20, 2023 02:05 PM

    I am doing a POC to create a .Net 7 Web API to retrieve records from our iSeries and getting the message about the license requirement.

    Is there any chance that a trial license is still available so I can complete this POC for our IT execs ?



    ------------------------------
    Richard Rothe
    ------------------------------