Unable to query AS400 over ODBC with PolyBase on SQL 2019 Linux-CodePudding

I would like to create external tables on a SQL Server 2019 instance running in an Ubuntu container querying an DB2 database on an AS400. I'm using the ibm iaccess 1.1.0.15 amd64 ODBC driver. When I try to create the external table, I get back the following error:

Msg 105082, Level 16, State 1, Line 15
105082;Generic ODBC error: NativeOdbcConnection.Open, error in OdbcConnectionCreate: SqlState: , NativeError: 111234, 'Error calling: SQLDriverConnect(this->_hdbc, NULL, (SQLWCHAR*)connectionString, SQL_NTS, NULL, 0, &cbConnStrOut, SQL_DRIVER_NOPROMPT), SQL return code: -1 | SQL Error Info: **Internal DmsNative error: SQLGetDiagRec returned: -1 | ConnectionPooling: 1 | Error calling: pConn->Create(connectionString, databaseName, useConnectionPooling, packetSize, connectionLoginTimeout, environmentSettings, transactionToken, transactionTokenSize, resourceGroupName, driverCapabilities, spid) | state: FFFF, number: 1, active connections: 1', Connection String: Driver={IBM i Access ODBC Driver};system=MYAS400SYSTEM;uid=MYAS400UID;server=MYAS400.MYDOMAIN.com.

Here is the Dockerfile I am using

# SQL Server Command Line Tools - custom image
# From Ubuntu 20.04 as base image
FROM ubuntu:20.04 as base

# Installing system utilities
RUN apt-get update && \
    apt-get install -y apt-transport-https curl gnupg && \
    # Adding custom MS repository
    curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list > /etc/apt/sources.list.d/mssql-server-2019.list && \
    curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | tee /etc/apt/sources.list.d/msprod.list
# From base image
FROM base as release

# Add the SQL startup scripts
COPY . /usr/src/app

COPY entrypoint.sh /
RUN chmod  x /entrypoint.sh

# Grant permissions for the setupsql script to be executable
RUN chmod  x /usr/src/app/setupsql.sh

# Installing SQL Server drivers and tools
RUN apt-get update 
RUN apt-get install -y mssql-server-polybase
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17
RUN ACCEPT_EULA=Y apt-get install -y mssql-tools 
# Install the IBM iAccess ODBC Driver
RUN dpkg -i /usr/src/app/ibm-iaccess-1.1.0.15-1.0.amd64.deb
RUN apt-get install -f
# Cleanup the Dockerfile
RUN apt-get clean && \
    rm -rf /var/lib/apt/lists

CMD exec /bin/bash entrypoint.sh

I can connect to the SQL server instance in the container over localhost using SSMS. Creating the credential and the datasource are both successful. Creating the external table is where I get the error.

Here is the SQL script to create the external data source and external table:

CREATE DATABASE SCOPED CREDENTIAL AS400Credential
WITH IDENTITY = 'AS400UID',
SECRET = 'AS400PWD';  
GO

CREATE EXTERNAL DATA SOURCE MYAS400 WITH (LOCATION = 'odbc://MYAS400.MYDOMAIN.com', 
   CONNECTION_OPTIONS = 'DRIVER={IBM i Access ODBC Driver};SYSTEM=MYAS400;', 
   PUSHDOWN = OFF,
   CREDENTIAL = AS400CREDENTIAL);
GO

CREATE EXTERNAL TABLE EXT.Test 
(
    hstr NCHAR(50) NOT NULL,
    hreg NCHAR(50) NOT NULL,
    htr# NCHAR(50) NOT NULL,
    httm NCHAR(50) NOT NULL
)
WITH ( LOCATION='MYAS400DATABASE.SCHEMA.TABLE', DATA_SOURCE= MYAS400)

Any ideas on how to successfully connect to the AS400 using PolyBase?

CodePudding user response：

PolyBase Generic ODBC connectivity is currently only available on Windows.

This feature requires SQL Server on Windows.

Configure PolyBase to access external data with ODBC generic types

Also currently Linked Server to non-SQL Server targets, and External Access and Unsafe CLR are Windows Only.

You may be able to use the Python extensibility and pyodbc to query your iSeries.