Unanswered: Linked server to AS400 - cannot see 'catalog' - WORKING
Update 09.03.03 - Now working, see reply 6 below.
This is how I've set up a linked server to our AS400:
- Installed Client Access Express V5.1, latest SP on SQL Server
- Created a system DSN using the CA ODBC driver pointing to the AS400
- Created a linked server, supplying the DSN name as the Data Source
I can see the tables on the AS400, and can successfully query them via a pass through query:
SELECT * FROM openQuery(MyAS400, 'SELECT * FROM library.table')
I've read in many places about the alternate query syntax for linked tables that advises:
SELECT * FROM LinkSvr.Catalog.Schema.Table
And this seems to be the only way to perform inserts or updates, so it's kinda important. Here's the killer: we have no 'catalog' and our AS400 people don't know what it is. In all the examples I've researched a catalog collumn just shows up in the linked server tables view, but for our linked server that collumn shows a value of *N for every table.
I've experimented with the catalog option in the Linked Server properites, but since our AS400 people don't know what to put there, I don't either. Same goes for the catalog tab in the ODBC - there are a few options there, but none of them seemed to make any difference. We're researching this 'catalog' with our AS400 people, but they say there is nothing called a catalog on on the AS400 or nothing corresponding to it that they can identify. They say there is nothing but a Library and the files, but the Library they gave us shows up as the schema in the linked tables and the files show up as the table names.
Sorry for such a long post. Thanks in advance for any help.
Well I talked to a guy at IBM who said the catalog name is the same as the 'system name' in AS400 nomenclature. That's easy enough--should be the name of our AS400. But how to get that to show up in the 'catalog' collumn in the list of tables under the linked server; that is the question. I've tried supplying that name in the catalog field in the linked server properties, but the catalog collumn still displays a "*N" for every table, and queries like
SELECT * FROM LnkSvr.SysName.Schema.Table
don't work when I substitute the name of our AS400 for 'SysName' above. I'm going to examine the DSN properties more and see if there is some place else I can specify it in there that I've just been overlooking.
OK, it turned out there was a setting missing on the AS400 side. Basically there was nothing wrong with how I was setting up the DSN and/or linked server (at least regarding the problem seeing the 'catalog').
Anyway, on the AS400 we found there was no relational database name defined in the 'Work with Relational Database Directory Entries' screen. We did a wrkrdbdire, added the system name as the RDB name, then had to restart the host server and some jobs (sorry that's a little unclear - I'm pretty clueless about the AS400 side). Once we did that the catalog shows up in the linked server and queries using the SELECT field FROM LnkSvr.Catalog.Schema.Table syntax work. I have to admit it's a little frustrating that our AS400 people (with years of AS400 experience) had no knowledge of this setting even existing. Of course it's a little gratifying too that the issue was not on my end.
I don't know how much this will help others with linked server problems unless they're having the same missing catalog issue, but at least it's something to watch out for.