2

Microsoft states that:

[...You can also use the generic ODBC connector to connect to additional providers using third-party ODBC drivers.]

The reality is much far away form that.

I've been trying to follow several guides about how to query DB2 from PolyBase. I know this should be possible. I've also find guides about how to query SalesForce through the ODBC Driver so yes, you should be able to query a lot of things.

The only stable code I could create and execute successsfully is this:

-- Create a database 
CREATE DATABASE Test_PolyBase;GO

-- Use that database 
USE Test_PolyBase;GO

-- Create a database master key. 
ALTER MASTER KEY ENCRYPTION BY PASSWORD = 'password';GO

-- Set DB2 user and password 
ALTER DATABASE SCOPED CREDENTIAL Test_PolyBase
    WITH IDENTITY = 'db2inst1' --put here DB2's username 
        ,SECRET = 'db2_password';--put here DB2's password 
    GO

From there on is a no men's land where no one really knows what's the next move.

I've successfully created a System DSN using the IBM DB2 ODBC DRIVER - DB2COPY1 Driver:

enter image description here

But then:

CREATE EXTERNAL DATA SOURCE Test_PolyBase
    WITH (
            LOCATION = 'odbc://192.168.1.44:50000' 
            ,CONNECTION_OPTIONS = 'DSN={testdb}'
            --,PUSHDOWN = ON
            ,CREDENTIAL = Test_PolyBase
            )

Is not working.

I tried to use a more detailed setup and the Driver itself instead of the DSN:

CREATE EXTERNAL DATA SOURCE Test_PolyBase
    WITH (
            LOCATION = 'odbc://192.168.1.44' 
            ,CONNECTION_OPTIONS = 'Driver={IBM DB2 ODBC DRIVER - DB2COPY1};PolyBaseOdbcSupportsSetDescRec=false'
            --,PUSHDOWN = ON
            ,CREDENTIAL = Test_PolyBase
            );

And I have also used a more detailed one:

CREATE EXTERNAL DATA SOURCE Test_PolyBase
    WITH (
            LOCATION = 'odbc://192.168.1.44:50000' 
            ,CONNECTION_OPTIONS = 'Dsn={testdb};
                                    Driver={IBM DB2 ODBC DRIVER - DB2COPY1};
                                    uid=root;
                                    server=192.168.1.44;
                                    port=50000;
                                    database=testdb;
                                    PolyBaseOdbcSupportsSetDescRec=false'
            --,PUSHDOWN = ON
            ,CREDENTIAL = Test_PolyBase
            )

I'm using the PolyBaseOdbcSupportsSetDescRec=false option as described in the example but no joy, the error is always the same:

OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Cannot generate SSPI context".
Msg -2146893042, Level 16, State 1, Line 0
SQL Server Network Interfaces: No credentials are available in the security package

Why SQL Server is trying to use OLE DB provider "MSOLEDBSQL"? That should be for SQL Server, right? Not DB2?!

And why ...for linked server "(null)" (which means I'm not indicating the linked server)? I shouldn't been using linked server at all.

EDIT: After extensive investigation I'm even more certain hat this might be flagged as a bug.

The kb4552255 is in fact specifying that:

You can access configurations that ship with PolyBase when specifying a DSN in the CONNECTION_OPTIONS of a Generic ODBC External Data Source definition. The options are picked up by matching the driver name used in the DSN definition. This currently applies to the following drivers:

• IBM DB2 ODBC DRIVER

• HDBODBC

• Microsoft Spark ODBC Driver

So the examples provided in the reply should work. With DSN but also with Driver.

Improve this question
6
  • 1
    PolyBase is just a fancy Linked Server technically, is my understanding, hence the same or similar error messages. Speaking of Linked Servers, you should create a classic one first to debug if the issue is with your ODBC driver or something external to your server, before you spin your wheels trying to tune your code for creating the PolyBase object. Once you get a classic Linked Server working, you can script it out and re-use most of those same settings to create the PolyBase object. Commented Jun 28, 2022 at 11:23
  • Thank you @J.D. I did that last week and I also wrote a post about it: jeeja.biz/2022/06/22/… . I have all the ingredients to make ODBC Driver for DB2 spin up and work. I've actually done that more than once at work. The problem is I can find documentation for PostgreSQL, Oracle, MongoDB, etc... but DB2 seems forgotten. There are simply no examples . Commented Jun 28, 2022 at 11:36
  • 1
    Open a ticket with MS. PolyBase is still new a enough feature within the product that there's quite a few issues with it. External Statistics didn't work as advertised for over a year until the release of CU9 as one example. Commented Jun 29, 2022 at 19:18
  • Good idea @JohnEisbrener, however I'm doing my own research on PolyBase late at night and I don't have a Premium account with Microsoft; I presume opening a ticket will not work. PolyBase has very low adoption rate and it start to be forgotten. I would not be surprise to find bug or lack of documentation Commented Jun 29, 2022 at 21:51
  • Give the Azure Feedback site a shot. While not as useful as connect, it is connect’s successor site for bug submissions/feedback. Commented Jun 30, 2022 at 1:55

1 Answer 1

Reset to default
1

When you create the linked server, it uses OLE DB which seems to work with DB2, but Polybase can't use it only ODBCs.

Have you enabled polybase? I guess yes.

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1
RECONFIGURE

Why are you using ALTER instead of CREATE? The master key encryption and database scoped credential exist before? I've only used CREATE for both.

I have some examples of ODBC external data sources, see if you need to specify additional properties, but don't use the "Driver" specification use the "DSN" specification instead:

CREATE EXTERNAL DATA SOURCE [MySpark] WITH (
  LOCATION='odbc://pabechevb.azurehdinsight.net:443',
  CONNECTION_OPTIONS='Driver={Microsoft Spark ODBC Driver}; AuthMech=6;',
  CREDENTIAL=[SparkCredential])

CREATE EXTERNAL DATA SOURCE [Cassandra] WITH (
  LOCATION='odbc://192.168.1.5:9042',
  CONNECTION_OPTIONS='Driver={DataStax Cassandra ODBC Driver}; Host=192.168.1.5; AuthMech=1;',
  CREDENTIAL=[CassandraCred])

CREATE EXTERNAL DATA SOURCE [Sap] WITH (
  LOCATION='odbc://40.123.224.129:39015',
  CONNECTION_OPTIONS='Driver={HDBODBC}; SERVERNODE=40.123.224.129:39015;',
  CREDENTIAL=[SapCredential])

Try to get rid of the SSPI message, all it indicates you're not authenticating properly. And if you ever get this message, you're stuck as there is no way out: Msg 105082, Level 16, State 1, Line 12 105082;Generic ODBC error: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed .

Improve this answer
3
  • Thank you @pabechevb. The CREATE/ALTER was a typo, sorry about that. I like the example you proposed but as you can see every CONNECTION_OPTIONS vary by database. How can I know if I have to use AuthMech or Host or SERVERNODE with DB2? There is a total lack of documentation and examples on internet. Commented Jun 29, 2022 at 21:57
  • Hi, I updated my question. I think your examples should work but they are not. This might be a bug that creates incompatibility between PolyBase and DB2 Commented Jun 30, 2022 at 11:42
  • 1
    I agree with you, there is a total lack of documentation and examples, that's why I had to do my own research and come with those examples. We all can benefit from your knowledge on DB2 if you make it work, as of now I have not seen how to do it. You seem to be the only person who can help us all with your articles. Commented Jun 30, 2022 at 22:35

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.