Solved

How to query link database to as400

Posted on 2006-10-20
20
366 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.
0
Comment
Question by:amphastar
  • 8
  • 6
  • 3
  • +1
20 Comments
 
LVL 9

Expert Comment

by:gpompe
ID: 17774904
Try as400.library..table
0
 
LVL 20

Expert Comment

by:Sirees
ID: 17775116
Can you try

as400.test.dbo.table

or

as400...table
0
 

Author Comment

by:amphastar
ID: 17775879
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"....
0
 
LVL 9

Expert Comment

by:gpompe
ID: 17775955
I am not sure, but try as400.ServerName.Library.Table.

0
 
LVL 9

Expert Comment

by:gpompe
ID: 17775956
ServerName is the AS400 name.
0
 

Author Comment

by:amphastar
ID: 17776676
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.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17781975
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
0
 

Author Comment

by:amphastar
ID: 17787973
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?
0
 

Author Comment

by:amphastar
ID: 17788710
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
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17794397
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'
0
 

Author Comment

by:amphastar
ID: 17795150
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
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17801936
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.
0
 

Author Comment

by:amphastar
ID: 17805077
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.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17810716
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?
0
 

Author Comment

by:amphastar
ID: 17811169
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?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17816140
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?
0
 

Author Comment

by:amphastar
ID: 17819005
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?
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 17823832
>> 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
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now