TSQL to query a linked AS400 server?

[puppet]

I have set up our AS400 as a linked server (from MS SQL Server) and can see all the relevant tables from within Enterprise Manager but do not know how to query them from Query Analyzer. We are running SQL 7. I know that I need to use a four part name with the following syntax

linked_server_name.catalog.schema.object_name

Linked server name is the name I used to link it?
Catalog is the name of the AS400 library?
Schema is ????? dbo on SQL what is it on AS400??
Object name is table name?


Any help appreciated.

 

[bperry]

I was wondering if you got this working yet.
From what little bit I have seen, what we usually think of as this in SQL Server:

SELECT * FROM
linked_server_name.catalog.schema.object_name

becomes this when linking to an AS400:

SELECT * FROM
linkedservername.AS400name.libraryname.tablename


But I've also heard a conflicting opinion.... that the above will not work at all, and that this is what you will have to do instead:

select * from openquery
(db2linkedservername, 'select * from schema.tablename')

Again, where .schema is probably the libraryname


Does any of that make sense to you? Have you gotten it working yet?

rgrds, etc
bperry

 

[puppet]

Yep I did get it in the end - kind of.

I couldn't get it to work at all with any combinations of linked_server_name.as400name.tablename...... I constantly got the error that no such table exists.

The select * from openquery(linkedservername, 'select * from tablename') works well if not a little slowly - I joined three tables in one select and it took 6 minutes to return 8000 rows -> 3-5 seconds when the tables are transferred over to the SQL server via dts and then the same query is run.

I can't for the life of me figure out how to update a table in the linked server from SQL server though. Any ideas?

 

[bperry]

I think the point to note is OPENQUERY can be used in places where you would normally just use a table name.

So, where you might do something like this in SQL SERVER:
----------------
Update A
Set ...

From SqlServerTable2 AS A

INNER JOIN SqlServerTable1 AS S
ON A.empid = S.empid
Where anyWhereCriteriaGoesHere
----------------

can get changed to something like this:

----------------

Update A
Set ...

From OPENQUERY(linkedservername, 'select * from tablename') AS A

INNER JOIN SqlServerTable1 S
ON A.empid = S.empid
Where anyWhereCriteriaGoesHere

----------------

By the way, I think this is well known to be notoriously slow.

rgrds, etc
bperry

 

[puppet]

update A
set c1pad6 ='NZL'
from openquery(as400test, 'select * from dscm00p') as A
where c1pad6='New Zealand'

Does this look OK to you? All I get is the following error:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'A'.

The following select works fine:


Select * from openquery(as400test, 'select * from dscm00p')
where c1pad6='New Zealand'

Any ideas?

 

[bperry]

Okay, yes, I can believe that.
I'll chew on it.

How's the sp_start_job business going?

 

[bperry]

Hi again,
I have a feeling this is a non-trivial problem, but hopefully someone else has already solved it. I think you should post the message below in the SQL Server forum, with a subject line like "Error updating AS400 linked server"

You might get someone who knows just what to do!

-----Cut Here -----------------
I am trying to update a table in an AS400 database through a SQLServer linked server (which is already set up).

This select works fine:

Select * from openquery(as400test, 'select * from dscm00p')
where c1pad6='New Zealand'

But this update fails with the following error:
Update A
set c1pad6 ='NZL'
from openquery(as400test, 'select * from dscm00p') as A
where c1pad6='New Zealand'

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'A'.

Any ideas?
----End Cut-------------

 

[puppet]

OK Will do - I Haven't had a chance to look at the trigger thing yet today - I'm more Network Administrator than DBA and today everything seems to be going wrong so I have had no time to think!!

 

[macbone2]

I wouldn't swear to this, but when using FTP (and there's every reason to believe the same protocols are buried in there somewhere) I would suggest that in the string
linked_server_name.catalog.schema.object_name

catalog = AS400 library
schema = database file
object_name = member

This is only a guess y'understand but if I am downloading an AS/400 file with a member name which is not the same as the file name FTP squeals if I don't insert the file name in the form
eg mylibrary/myfile.mymember however FTP doesn't protest if the file name is same as member name.

Hope this helps, if not ignore it!

 

[wiegelman]

Has anyone had any luck with this? I cannot even see the tables in the Enterprise manager, could someone possible give an example of how they set up their linked server.

Thanks in advance!

 

[puppet]

I chose Microsoft OLE DB Provider for ODBC Drivers and used an ODBC driver I set up to point to our database.

The only information I needed to enter was:

Product Name: AS400 (The name to use for the Linked Server)
Data Source: AS400 (the name of the ODBC connection))

Hope this helps.

 

[Guest_imported]

do something like this works on my site, omit the alias.

update openquery(ws3099, 'select * from unsubscribe') set updatecount=updatecount+1

Vincent

 

[wiegelman]

Still no luck, I can't see any of the AS/400 tables. I can link to another SQL Server with no problem but not to the AS/400. When I try to browse the tables, I cannot even see a job on the AS/400 that is the SQL server trying to access the tables.

What AS/400 server jobs need to be running to allow this to work - I know that the "host" server job (TCP/IP, etc. and the DDM server are running on the AS/400). What else needs to be started? Maybe that is my problem?

Thanks,
Tom

 

[morleyhill]

I'm no expert but I've just been through a similar exercise-

We have an AS400 called SERV
On SERV is a library called TSTDT
and in that library is a file called SEG1A

-which only gives three of the four parts.
I linked to SERV from SQL7.0 and called the linked server SERV also.

Now from within Query Analyser I use the syntax-
SELECT * FROM SERV.SERV.TSTDT.SEG1A
which works.

I'm not sure whether AS400 connections always use the server name for the Catalog or whether our AS400 was set up that way.

Hope this helps.

 

[macbone2]

this could also be aproblem with insufficient authority to access the files on AS/400. Check with your system administartor that you are authorised to access data from the AS400 in that library with the user id you are using

 

[bigfoot]

So how do you set up the AS/400 as a linked server???

Can you query the files directly?

Please help if you can.

I tried it in Enterprize Manager but I'm not too sure what to put into the linked server properties box?

It created it but it gives me an error when I click on it saying error 7302 could not create an instance of ole provider IBMDA400.

What am I doing wrong?

Thanks
Gary

 

[alandt]

Hello,
I had the same problems and found a solution using sql2000 and odbc-connection.
Use the stored procedure

exec master.dbo.sp_table_ex servername

You get all tables of the linked server with:
TABLE_CAT = catalog
TABLE_SHEM = shema (interesting, its the db2-library name)
TABLE_NAME = tablename

The query could look like this:
SELECT * FROM [myserver].[S654858B].[mylibrary].[mytablename]

Hope it works on your systems,
Greeting,
Klaus Alandt

 

[bigfoot]

When I run this:
SELECT * FROM [MYAS400].[S10A7850].[garylib].[comcls]

I get this:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'MYAS400' does not contain table '"S10A7850"."garylib"."comcls"'. The table either does not exist or the current user does not have permissions on that table.


And when I click on tables under my linked server (MYAS400), I can see the file there. I have permissions to the files.

 

[alandt]

Maybe there are problems with authentification. You have to be logged in sql-server with SQL-authentification and NOT with windows-authentification. Otherwise the linked server will not be authentified correctly.