Programming Languages on Power

 View Only

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

By Janani Janakiraman posted Thu December 15, 2022 10:05 PM

  

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.

.NET connecting to IBM i DB2 via ODBC

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

  1. Install the IBM i Access ODBC Driver (see doc here). This installs ibm-iaccess and its dependencies, libtool-ltdl and unixODBC.
    # The repositories are located under https://public.dhe.ibm.com/software/ibmi/products/odbc/
    curl https://public.dhe.ibm.com/software/ibmi/products/odbc/rpms/ibmi-acs.repo | sudo tee /etc/yum.repos.d/ibmi-acs.repo
    
    #Install the ODBC Driver
    dnf install --refresh ibm-iaccess​
  2. Verify your set up by running odbcinst commands as below:

    # Print the current configuration of unixODBC
    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
     # Query ODBC configuration files for list of drivers
    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
    # The command prints the details of the 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-sdk-7.0

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

  1. Create the console application using the dotnet new command:
    dotnet new console -o odbc_sample
  2. 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
    
    <Project Sdk="Microsoft.NET.Sdk">
    
      <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net7.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
      </PropertyGroup>
    
      <ItemGroup>
        <PackageReference Include="System.Data.Odbc" Version="7.0.0" />
      </ItemGroup>
    
    </Project>
    
  3. 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();
            }
        }
    }                       
    
  4. Rebuild the project:
    dotnet build
  5. 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

Permalink