Delete to AS400 linked Server restricted by size

Question

Post reply

Delete to AS400 linked Server restricted by size

Dave Gall

Old Hand

Points: 311
More actions
May 23, 2004 at 8:56 am

#65185

We've been struggling here with our linked server connection to AS400. We're using IBM iseries Client access on Windows 2000 to an AS400 V5.
Currently we can Update , Insert and select on the AS400 target table OK .
The delete problem symptoms are; if the table size exceeds approx 66K bytes we get an error ( invalid schema requirements) or some such. Less than that size and we can delete without complaint.
The code used is
"Delete * from aaaa.aaaa.library.tablename"
Any suggestions??

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alejandro DAndrea SSC Veteran Points: 275 More actions · Answer 1

One question:

were you able to execture SELECTs that return more than those 66k?

Cause I just posted a question that looks a lot like yours, only for SELECTs. Here is what I wrote:

I linked a DB2 database to my sql server with

sp_addlinkedserver 'srv1.MYDOMAIN.com', 'DB2', 'MSDASQL', null, null, 'DSN=srv1.MYDOMAIN.COM;UID=user1;PWD=mypass', null

after creating an ODBC entry called "srv1.MYDOMAIN.COM" with the Client Access ODBC Driver (32-bit) driver.

The thing is, when I run a query, it returns what I guess is a fixed quantity of bytes!!! For instance, I query a customers table that has 34031 rows and I only get the first 35 (customers 1 to 35). Then I say "ok, give me all rows but the ones for customer 1,2,3,4 and 5". The query returns again 35 rows, but in this case I get customers 6 to 41!!!

I tried several variations of this and I confirmed it is a matter of quantity of information.

Has anyone fought with this before!?!?

Thanks!!

Dave Gall Old Hand Points: 311 More actions · Answer 2

I would suggest that you check your client access configuration( or is it in ODBC??) under performance, turn off row blocking and maximize your blocking size. as far as my problem goes yes I can select the entire table and pull it into SQL. Its only on a delete that I find this anomaly!!

Nilssond Hall of Fame Points: 3285 More actions · Answer 3

I think the asterisk is the problem. Try :

DELETE FROM <TABLE> where <conditions>

bbron SSC Eights! Points: 936 More actions · Answer 4

David,

Don't hate me for this... but I'd like to enquire how you manage to insert?

I ahve the same config as you... but the only way I issue command is via the OpenQuery:

Select * from openQuery(IBMAS400, 'select * from LIBRARY.TABLE')

When I check the table in the linked server, I find the following fields:

Name, Schema, Catalog, Type. Name should be the table name while Schema is the library name... what's the Catalog and Type? Somemore, the catalog is usually "*N" while the type is "USER". Catch no ball.

Hope you can help.