Orbiting the AS400

I need to link to tables in an AS400 on a customer site. They use IBM CA and
have the latest 5.2 version. (IBM CA includes both ODBC and OLE DB drivers).
I tried configuring the IBM OLE DB for AS400 driver using both graphical and
sp_addlinkedserver, but with no success. The best I can get is a link that
appears to connect without errors but the clicking on the tables or vies
icon I just get an empty recordset back. I have not been able to figure out
exactly what **should** be the correct configuration, in particular most
examples give connection strings, and this option does not appear to be
available with this driver:-(
Worse is to come, thinking that the problem may just be with access to the
schema information, I tried running a simple query against the AS400 even
thougth the list of tables was empty. SQL Server crashed, and it was a
production system. So, I installed CA on my laptop and tried again using my
"local" db. Identical.
Surfing around for help I found that many people seem to have similar
problems, and the suggested cure was to use the OLE DB for ODBC driver, and
it **almost** works. I see the tables and can run queries, but results are
sometimes incomplete. Selecting a table of 469 rows in query analyzer I only
got back 401 rows. Tinking it was a buffer problem I tried changing the
number of columns, but always got 401, except when I used Select * which
yields 392. At least one field is unique and I have tried using this on it's
own.
The ODBC DSN runs fine and returns 469 rows in queries sent from Access and
Excel.
Back to qury analyzer, Select Count(AnyField) always returns 469 rows,
suggesting that it is a datagrid/display problem, so I tried something like:
Select sqltable.GPART from sqltable where Not Exists (Select GPART from
as400table )
this returns all the GPARTs in the sqltable that are missing from the
"Select * from as400 table".
So, I surfed for solutions to this problem, and found that I was not alone
with this type of problem either. Using OLE DB for ODBC with the CA ODBC
driver can return missing rows.
Suggested cures were updating the ODBC driver (have been using latest and
greatest version from outset) or.....switching to OLE DB.
So having done the whole loop, I wonder if anybody had any idea where I can
go from here. I would very much like to hear if **anybody** has ever
succesfully linked in an AS400.
BTW, I know there is a Microsoft OLE DB driver for the AS400, but as far as
I can make out it requires an SNA server installation. Is this correct? I
have seen the license fee is around $2500, which is a bit steep if all you
are interested in is the OLE DB driver!Hi Rog,
Thanks for your post. The Microsoft OLE DB Provider for DB2, distributed
with Microsoft? Host Integration Server 2000, allows Microsoft SQL Server?
2000 distributed queries to query data in DB2 databases. The network
components needed to communicate with an IBM computer run in an SNA network.
For more information regarding Microsoft OLE DB Provider for DB2, please
refer to the following articles:
OLE DB Provider for DB2
http://msdn.microsoft.com/library/d...-us/acdata/ac_8
_qd_12_1gvm.asp
218590 INF: Configuring Data Sources for the Microsoft OLE DB Provider for
DB2
http://support.microsoft.com/?id=218590
216428 Configuring Microsoft ODBC Driver for DB2
http://support.microsoft.com/?id=216428
Also, as to the problem with IBM OLE DB for AS400 driver provided by IBM, I
am not familiar with the detailed information of this driver, therefore,
the help I am able to provide is very limited. For the best help, I suggest
you talk with IBM support engineers about this driver problem.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.