When using Microsoft® SQL Server Linked Server with the IBM DB2 OLE DB (IBMDADB2) provider, the following error may occur:
Error : "Msg 7320, Level 16, State 2, Line 1
Cannot execute the query
When performing an UPDATE, INSERT or DELETE with Microsoft® SQL Server Linked Server, SQL Server requests a server-side forward-only updatable cursor with the IBM OLE DB provider. DB2 does not support this type of cursor, and reports this back to SQL Server which would allow SQL Server to request a different kind of cursor. Unfortunately instead SQL Server returns an error message indicating that the operation cannot be performed.
Resolving The Problem
This problem can be resolved by using the Microsoft® OLE DB provider for ODBC (MSDASQL) instead of the IBM OLE DB provider. MSDASQL uses the IBM DB2 ODBC/CLI driver, and with that driver, the cursor type can be manipulated to allow the operation to succeed.
In the db2cli.ini file on the client machine where SQL Server is running, add the following lines under the data source name (DSN) section of the db2cli.ini. For example, if the data source name is SAMPLE, add the following to the db2cli.ini file under the [SAMPLE] section as follows:
DisableKeysetCursor=1 will tell the application that the IBM DB2 ODBC/CLI driver does not support Keyset cursors. PATCH2=6 will tell the application that scrollable cursors are not supported. These two keywords will allow SQL Server Linked Server to perform an INSERT/UPDATE/DELETE with the MSDASQL provider when using the IBM DB2 ODBC/CLI driver.
Once the db2cli.ini file is updated, SQL Server Linked Server should be restarted, and the link should be recreated to use the MSDASQL provider instead of the IBMDADB2 provider.
Was this topic helpful?
16 June 2018