Connect a .NET application running on Linux on Power to IBM i using ODBC

Recently IBM has announced .NET 7 support on Linux on Power, now you can run .NET Applications on Power. This blog demonstrates how to connect a .NET 7 sample application running on Linux Power(ppc64le) to a remote Db2 on IBM i server using ODBC connector.

Initial setup of the servers:

To complete the steps outline in this blog, you’ll need the following:

A partition running IBM i on an IBM Power server. It is assumed that you have an IBM i server already setup.
A partition running Linux (RHEL 8.7/9.1) on an IBM Power server. If you need access to Power hardware, this blog, Accelerate your open source development with access to IBM Power resources lists several free and low-cost options. Open Source developers might consider the OSL option.

NET 7 is available as the RPM, dotnet-sdk-7.0. in the AppStream repositories for RHEL 8.7 and RHEL 9.1 The AppStream repositories are enabled by default on RHEL 8/9 systems.

NOTE: The rest of the steps in this blog are run on the Linux on Power server.

Install ODBC drivers on the Linux on Power server

Install the IBM i Access ODBC Driver (see doc here). This installs ibm-iaccess and its dependencies, libtool-ltdl and unixODBC.

curl https://public.dhe.ibm.com/software/ibmi/products/odbc/rpms/ibmi–acs.repo | sudo tee /etc/yum.repos.d/ibmi–acs.repo

dnf install ––refresh ibm–iaccess

Verify your set up by running

odbcinst

commands as below:

odbcinst –j

The output may look like:
unixODBC 2.3.7
DRIVERS…………: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size…….: 8
SQLLEN Size……..: 8
SQLSETPOSIROW Size.: 8
Next print the list of drivers using the command below

odbcinst –q –d
The output may look like:
[PostgreSQL]
[MySQL]
[FreeTDS]
[MariaDB]
[IBM i Access ODBC Driver]
[IBM i Access ODBC Driver 64-bit]
Look at the details of the IBM i Access ODBC Driver

odbcinst –q –d –n “IBM i Access ODBC Driver 64-bit”
The output may look like:
[IBM i Access ODBC Driver 64-bit]
Description=IBM i Access for Linux 64-bit ODBC Driver
Driver=/opt/ibm/iaccess/lib64/libcwbodbc.so
Setup=/opt/ibm/iaccess/lib64/libcwbodbcs.so
Threading=0
DontDLClose=1
UsageCount=1

Install .NET 7 on the Linux on Power server

Install dotnet7 using yum ( the server in the example is running RHEL 8.7 ):

yum install dotnet

Create .NET 7 console application on the Linux on Power server

Create the console application using the dotnet new command:
dotnet new console -o odbc_sample

Add the System.Data.Odbc package using the dotnet add command. This will add package reference in csproj file.
# Change directory to odbc_sample
cd odbc_sample

# Add the System.Data.Odbc Package to the project
dotnet add package System.Data.Odbc
Note the included System.Data.Odbc PackageReference in the odbc_sample.csproj file.
cat odbc_sample.csproj

<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net7.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>

</Project>

Copy the code below into the Program.cs file. This sample connects to Db2 on IBM i and reads all rows and columns from qcustcdt table in the qiws schema and displays the same on console output.

Note: See the format of ConnectionString to connect to DB2 on IBM i. Be sure to modify the program to supply the connection parameters for your IBM i server. Also modify the program to retrieve data from tables on your IBM i server.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
// Replace connection string with your IBMi systems credentialss
string MyConString = “DRIVER={IBM i Access ODBC Driver 64-bit}; ” +
“SYSTEM=<<hostname of IBMi machine>>; ” +
“UID=<uid>; ” +
“PWD=<pwd>;”; // Open a Database Connection
OdbcConnection DbConnection = new OdbcConnection(MyConString);
DbConnection.Open();
OdbcCommand DbCommand = DbConnection.CreateCommand();
// Select rows from a table qiws.qcustcdt on IBMi
DbCommand.CommandText = “SELECT * FROM qiws.qcustcdt”;
OdbcDataReader DbReader = DbCommand.ExecuteReader();
// Display the retrieved data to the console
int fCount = DbReader.FieldCount;
Console.Write( “:” );
for ( int i = 0; i < fCount; i ++ )
{
String fName = DbReader.GetName(i);
Console.Write( fName + “:” );
}
Console.WriteLine();
while( DbReader.Read())
{
Console.Write( “:” );
for (int i = 0; i < fCount; i++)
{
String col = DbReader.GetString(i);
Console.Write(col + “:”);
}
Console.WriteLine();
}
// Close the Database Connection
DbReader.Close();
DbCommand.Dispose();
DbConnection.Close();
}
}
}

Rebuild the project:
dotnet build

Rerun the application:
dotnet run
The run may fail with the error “System.Data.ODBC is not supported on this platform.” The output may look like:
Unhandled Exception:
System.PlatformNotSupportedException: System.Data.ODBC is not supported on this platform.
at System.Data.Odbc.OdbcConnection..ctor(String connectionString)
at ConsoleApplication1.Program.Main(String[] args) in /root/odbc_sample/Program.cs:line 18
[ERROR] FATAL UNHANDLED EXCEPTION: System.PlatformNotSupportedException: System.Data.ODBC is not supported on this platform.
at System.Data.Odbc.OdbcConnection..ctor(String connectionString)
at ConsoleApplication1.Program.Main(String[] args) in /root/odbc_sample/Program.cs:line 18

This is a known problem, see issue . The fix should be available in the next .NET7 service release. In the meantime, as a workaround, you have to add a runtimes section to the end of /usr/lib64/dotnet/shared/Microsoft.NETCore.App/7.0.0/Microsoft.NETCore.App.deps.json as shown below:

diff –git a/Microsoft.NETCore.App.deps.json b/Microsoft.NETCore.App.deps.json.orig
index 75a6e51..eba6595 100644
— a/Microsoft.NETCore.App.deps.json.orig
+++ b/Microsoft.NETCore.App.deps.json
@@ -733,18 +733,4 @@
“sha512”: “”,
“path”: “microsoft.netcore.app.runtime.rhel.8-ppc64le/7.0.0”
}
+ },
+ “runtimes”: {
+ “rhel.8-ppc64le”: [
+ “rhel.8”,
+ “rhel-ppc64le”,
+ “rhel”,
+ “linux-ppc64le”,
+ “linux”,
+ “unix-ppc64le”,
+ “unix”,
+ “any”,
+ “base”
+ ]
+ }
}
}
After this change, rebuild and rerun the application and this time it displays the content of qcustcdt table in the QIWS schema as shown below:
dotnet build
dotnet run
The program should display the content of the qcustcdt table in the QIWS schema as below
:CUSNUM:LSTNAM:INIT:STREET:CITY:STATE:ZIPCOD:CDTLMT:CHGCOD:BALDUE:CDTDUE:
:938472:Henning :G K:4859 Elm Ave :Dallas:TX:75217:5000:3:37.00:0:
:839283:Jones :B D:21B NW 135 St:Clay :NY:13041:400:1:100.00:0:
:392859:Vine :S S:PO Box 79 :Broton:VT:5046:700:1:439.00:0:
:938485:Johnson :J A:3 Alpine Way :Helen :GA:30545:9999:2:3987.50:33.50:
:397267:Tyron :W E:13 Myrtle Dr :Hector:NY:14841:1000:1:0:0:
:846283:Alison :J S:787 Lake Dr :Isle :MN:56342:5000:3:10.00:0:
:693829:Thomas :A N:3 Dove Circle:Casper:WY:82609:9999:2:0:0:
:593029:Williams:E D:485 SE 2 Ave :Dallas:TX:75218:200:1:25.00:0:
:583990:Abraham :M T:392 Mill St :Isle :MN:56342:9999:3:500.00:0:
:123456:Wold :J K:Makle St :Rock :MN:55904:788:2:0:0:

Conclusion

With the ability to connect from .NET applications on Linux on Power to data residing on an IBM i server, IBM Power customer will have the ability to modernize their apps while running on the same Power platform as their IBM i business databases. Applications can now be moved closer to the data on their Power servers.

Expand on the program in this demo to experiment with connecting to your business databases.

References

IBM-provided Repositories ODBC Linux Driver Repositories

Using ODBC to connect to IBM i

Known Issue: Unhandled Exception: System.PlatformNotSupportedException: System.Data.ODBC is not supported on this platform