Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    9

    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:
    Code:
    SELECT * FROM openQuery(MyAS400, 'SELECT * FROM library.table')
    I've read in many places about the alternate query syntax for linked tables that advises:
    Code:
    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.
    Last edited by mxskweeb; 09-03-03 at 17:19.

  2. #2
    Join Date
    Aug 2003
    Posts
    68

    Re: Linked server to AS400 - cannot see 'catalog'

    ANY LUCK YET?
    jm

  3. #3
    Join Date
    Aug 2003
    Posts
    9

    Re: Linked server to AS400 - cannot see 'catalog'

    Originally posted by jmayo
    ANY LUCK YET?
    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
    Code:
    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.

    Thanks for asking (and bumping)

  4. #4
    Join Date
    May 2003
    Location
    Seattle, WA
    Posts
    4
    Try using the IP for the AS400 server as the name...

  5. #5
    Join Date
    Aug 2003
    Posts
    9

    current status: same

    Well I tried checking the option in the ODBC settings to convert CCSID and tried using the AS400's IP address in a few places, each with the same results as before.

    I will be talking to IBM again on Tuesday and update status here then. Thanks for the suggestions so far.

  6. #6
    Join Date
    Aug 2003
    Posts
    9

    Current status: Working

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •