Search code examples
sql-serverdb2database-administrationdb2-luwsql-server-2019

PolyBase on SQL Server 2019: IBM DB2 External tables

I need to make the data from the DB2 10.5 database available as an external table on a PolyBase enabled SQL Server 2019 instance and running into a problem...

In DB2 version 11.1 IBM added the LIMIT clause (LIMIT ) In DB2 version 10.5 IBM was still using the FETCH clause (FETCH FIRST ROWS ONLY)

SQL server is miss translating a T-SQL TOP clause into LIMIT instead of the FETCH that this database requires.

Has anyone run into this issue before? If so does anyone have a workaround for this limitation?

Solution

  • If you have control over the Db2 server (or can request changes on it) you can use the Db2 compatibility feature.

    Set the registry variable DB2_COMPATIBILITY_VECTOR:

    db2set DB2_COMPATIBILITY_VECTOR=MYS

    and restart the instance. It will make Db2 recognize LIMIT and OFFSET clauses. Setting just this bit should not affect other behaviour of the Db2 server.