Error querying an iSeries DB2 varchar(32000) column from SQL Server over linked server

Question

I am working on migrating an iSeries DB2 database to SQL Server (manually - not currently using SSMA). I have a DB2 column of type varchar(32000). When I try to query from SQL Server using openquery over a linked server, I get the error "Requested conversion is not supported." How can I get this data migrated using an SQL script?

Here is an example query over linked server:

select MYVARCHAR32000COLUMN
from   openquery(MYDB2LINKEDSERVER, 'select MYVARCHAR32000COLUMN from MYDB2DB.MYDB2TABLE')

OLE DB provider "MSDASQL" for linked server "MYDB2LINKEDSERVER" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 2304
Cannot get the current row value of column "[MSDASQL].MYVARCHAR320000COLUMN" from OLE DB provider "MSDASQL" for linked server "MYDB2LINKEDSERVER".

Answer 1

To get this data migrated, without changing the linked server driver, I ended up adding 4 CLOB columns on source table, then casting those 4 pieces as varchar(8000) in my destination SQL Server table.

On DB2 (source):

alter table MYDB2.MYTABLE add MYCOL_1 CLOB;
alter table MYDB2.MYTABLE add MYCOL_2 CLOB;
alter table MYDB2.MYTABLE add MYCOL_3 CLOB;
alter table MYDB2.MYTABLE add MYCOL_4 CLOB;

update MYDB2.MYTABLE set MYCOL_1 = trim(substring(MYCOL, 1, 8000));
update MYDB2.MYTABLE set MYCOL_2 = trim(substring(MYCOL, 8001, 16000));
update MYDB2.MYTABLE set MYCOL_3 = trim(substring(MYCOL, 16001, 24000));
update MYDB2.MYTABLE set MYCOL_4 = trim(substring(MYCOL, 24001, 32000));

Then on SQL Server (destination):

select cast(MYCOL_1 as varchar(8000)) + cast(MYCOL_2 as varchar(8000)) + cast(MYCOL_3 as varchar(8000)) + cast(MYCOL_4 as varchar(8000)) MYCOL
from   openquery(MYDB2LINKEDSERVER, 'select MYCOL_1, MYCOL_2, MYCOL_3, MYCOL_4 from MYDB2.MYTABL')