How to query link database to as400

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.
amphastarAsked:
Who is Participating?
 
nmcdermaidConnect With a Mentor Commented:
>> 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
 
gpompeCommented:
Try as400.library..table
0
 
SireesCommented:
Can you try

as400.test.dbo.table

or

as400...table
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
amphastarAuthor 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"....
0
 
gpompeCommented:
I am not sure, but try as400.ServerName.Library.Table.

0
 
gpompeCommented:
ServerName is the AS400 name.
0
 
amphastarAuthor 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.
0
 
nmcdermaidCommented:
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
 
amphastarAuthor 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?
0
 
amphastarAuthor 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
0
 
nmcdermaidCommented:
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
 
amphastarAuthor 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
0
 
nmcdermaidCommented:
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
 
amphastarAuthor 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.
0
 
nmcdermaidCommented:
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
 
amphastarAuthor 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?
0
 
nmcdermaidCommented:
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
 
amphastarAuthor 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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.