We help IT Professionals succeed at work.

How to query link database to as400

amphastar
amphastar asked
on
Medium Priority
456 Views
Last Modified: 2012-06-21
I have a link database to as400 through system ODBC.

How can I query a table on the as400, assuming the link name is "as400", library name is "test", table name is "table".
I found some articles here that suggests two syntax:
select * from as400.library.owner.table
or
select * from as400.library.schema.table

Which one should I use? The first one doesn't work with me. And I don't know what is the schema for the as400 database if I use the second one.

Can someone give me a hand?

Thanks a lot.
Comment
Watch Question

Commented:
Try as400.library..table

Commented:
Can you try

as400.test.dbo.table

or

as400...table

Author

Commented:
no luck!
for
as400.library..table
and
as400.test.dbo.table

They both don't work. I got the message like:

...server "as400" doesn't contain table named "library"."table"....
or
...server "as400" doesn't contain table named "library"."dbo"."table"....

Commented:
I am not sure, but try as400.ServerName.Library.Table.

Commented:
ServerName is the AS400 name.

Author

Commented:
No, it doesn't work.

By the way, when I created the link database, I used ODBC driver and put in the system DSN name in the data source. Is that all I need? I didn't see any problem when I set up the connection.
Do you see anything when you go into Enterprise Manager/Security/Linked Servers/Your Linked Server? Is there a list of tables?

Do you have any AS400 client tools that you can use to verify that your login works, and can see tables?


It is rare to get SQL Server and AS400 playing nicely so don't be suprised if it throws up errors.


If you are trying to get data out of the AS400 then another more reliable way to do it is to export to flat files then import into SQL

Author

Commented:
No, I can't see a list of table through the Enterprise Manager. It is just a icon without "+" sign.

And I think the ODBC works find because I can use the same ODBC dsn to create a link table on Access database and retrieve data.

Is there anything wrong when I create the SQL link database?

Author

Commented:
One more thing, when I created the ODBC, I used the IBM Client Access driver, is that possible the reason for the problem? SQL server has another driver called "IBM AS400 OLE DB provider" driver. Should I use that to create the link database or use that to create a system DSN if possible?

Thanks
How about as400...table  (?!) can you tell I'm clutching at straws.

If you want to try the OLE DB driver then you don't need to create a DSN, you can create a linked server and pick the OLE DB driver directly under 'Provider Name'

Author

Commented:
No, "as400...table" doesn't work either.

If I choose IBM as400 OLE DB driver. All I need to put in is the source name which is the name of the machine. However, when I tried to run a "select" command, I got a message saying something like "can not create a instance of ..."

Can you help you create a link database to as400 using OLE DB?

Thanks
I can try to help but I must warn you that I have never got this working before!

What is the exact message you are getting?


On the OLE DB front, first try this:

1. Create a new text file
2. Paste in this (substituting in correct values)

Provider=IBMDADB2;Database=sample;HOSTNAME=db2host;PROTOCOL=TCPIP;PORT=50000;uid=myUsername;pwd=myPassword;

3. Rename the file extension to be UDL
4. Double click the UDL file and try and test it with the test button.

Author

Commented:
when I double click on the UDL file, it says "the provider can not be found. Ensure the it is install properly". What is the name of this driver, and how can I install in to my Server. One thing I notice is that there is driver called "IBM AS400 OLE DB provider" under SQL server, but it doesn't exist in the OS system ODBC setting screen. There is IBM client access driver instead.
To try to test your IBM AS400 OLE DB Provider,

1. Create a blank text file
2. Rename the extension to UDL
3. Now you can effectively go through a wizard - first page, pick IBM AS400 OLE DB as the driver, second page enter connection parameters and test.


If you test, does it work?

Author

Commented:
What were I saying is that the "IBM AS400 OLE DB" doesn't exist in the UDL wizard. It is only shown in the SQL manager link database setup screen. Was the driver installed inproperly?
So its not in the list on the first page of the UL wizard? You're right, it mustn't be installed properly, we should probably discount it as I cound't get a properly installed one working.

The only way I have ever got a reliable extract out of the AS400 is to use client tools provided. Do you have iSeries client access tools installed?

Author

Commented:
Yes, I do. I have the IBM client access driver in the wizard. And I use a DSN using that driver. It works with my Microsoft access link table, but not with the Microsoft SQL server 2005 link database. I don't know why. So I tried to use the IBM AS400 OLE DB driver that come with the SQL server. However, no luck again.
Do you know what was missing when I installed the SQL server? I didn't see any error message when I installed the server.

I saw an anticle saying that the standard version doesn't support AS400 DB2 so well while the Enterprise version does. Is that correct?
>> I saw an anticle saying that the standard version doesn't support AS400 DB2 so well while the Enterprise version does. Is that correct?

Doesn't sound right to me.



Have a look at this post and see if it helps.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_22035990.html

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.