I am inserting records from SQL to DB2 through linked server using four-part name. I am connecting with DSN of iSeries AS 400 for DB2. But I am getting strange errors. I have full rights on the table as per my system admin. Basically, I cannot figure out the names of catalog and schema.

The statement I want is

SELECT * FROM <linkedserver name>.<catalog name>.<schema name>.<table name>

1. When I specify Linked server name and table name and skip the catalog and schema names using dots, giving: SELECT * FROM MyLinkedServer1...MyTable

it says:

OLE DB provider 'MyLinkedServer1' does not contain table 'MyTable'.  The table either does not exist or the current user does not have permissions on that table.

2. When I repeat the same thing as above and give table name in uppercase, it says:

Invalid schema or catalog specified for provider 'MSDASQL'.

3. When I try to specify ADMINISTRATOR in schema along with linked server and table name values, it says:

OLE DB provider 'MSDASQL' reported an error. 
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver]Invalid string or buffer length.]

4. When I do the same as in point 3 above, with any other schema name (valid or invalid) it says:

OLE DB provider '<linked server name>' does not contain table '"MySchemaName"."MyTable"'.  The table either does not exist or the current user does not have permissions on that table.

Any idea what's happening. Thanks.

Do you have iSeries client installed on the SQL Server box?

Have you created the linked server on the SQL Server box?

the four part name should be:

<linked server name>.<catalog/database name>.<library/schema  name>.<table name>

Try something like this,

SELECT *
FROM AS400LinkedServerName.AS400DatabaseName.AS400Library.AS400FileName

You can use the WRKRDBDIRE command to get the AS/400 database name.

A couple of notes here. .

Using Enterprise manager On the Linked server listing of the tables connected through the “client Access” System DNS ODBC it tells you the "schema" and "catalog" names to use (on the right side of the screen once you select the tables under your linked server name) !!

and for me these needed to be all in caps ..and it was case sensitive!!

I use select statement to select one order. It take 9 seconds.

But OPENQUERY only take 0.1 seconds to select one order.

Do anyone know why?

How can we make select statement run faster?

Our application only support select statement.

Thanks,

David

tony,

Try comparing the execution plans for both methods from inside Query Analyzer.