Sql-server – SQL Server – Query Over Linked Server to IBM DB2 throws an error

db2db2-midrangeiserieslinked-serversql server

We have a partner that uses a IBM DB2 iAccess database, which we connect to using a linked server on our SQL Server.
It's been working great for a long time, but over the weekend they upgraded their system, and now one of our jobs is failing.

The linked server still connects fine, meaning that if I look at the server under Server Objects->Linked Servers->servername it shows up, and I can see all the tables.

When I run the query through an OpenQuery it works fine:

Select * From OpenQuery(GLDATA, 'Select WPCPKG, WPCBAC, WPCDEN, WPCPRT from S782D480.GLDATA.GPCKCTL') –This works

But when I use the four part naming that is needed to do an insert into that table, it fails:

select WPCPKG, WPCBAC, WPCDEN, WPCPRT from GLDATA.S782D480.GLDATA.GPCKCTL — fails, but used to work last week

The error says:

OLE DB provider "MSDASQL" for linked server "GLDATA" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "GLDATA" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 – SYSCOLUMNS in QSYS2 type *FILE not found.".
Msg 7311, Level 16, State 2, Line 7
Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "MSDASQL" for linked server "GLDATA". The provider supports the interface, but returns a failure code when it is used.

Per this article I've tried dropping and recreating the linked server, and turning "allow inaccess" off and on in the provider, but that didn't work.

The error is vague enough that I'm not finding much information to try, and I suspect that it is a config problem on their end, but unless I give them the solution on a platter they are very slow to fix things.

Best Answer

Well, I did a lot of searching, found some information on the SQL0204 error, which led me to this support page:

https://www-01.ibm.com/support/docview.wss?uid=nas8N1016377

which has instructions on what commands to run on the server if you see that SQL0204 error.

I forwarded that on to the partners IT guy, who tried it (and a few other commands according to him) and it started working.

I will try to see if I can get him to send me what other commands he ran so I can make this answer more complete.

Edit:
In case the linked page ever disappears, here is a screen shot of the information:

https://www-01.ibm.com/support/docview.wss?uid=nas8N1016377