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:5001Microsoft.Hosting.Lifetime: Information: Now listening on:
http://localhost:5000Microsoft.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