AS400 linked server via Client Access ODBC

Question

AS400 linked server via Client Access ODBC

Richard Cranston

Hall of Fame

Points: 3680
More actions
March 25, 2004 at 1:30 pm

#161130

I created a AS400 linked server via Client Access ODBC; and when I run the select statement, the number of records in the results is limited. It only shows a minimal records results. The file has about 20,000 records.
Any ideas?

Viewing 15 posts - 1 through 15 (of 15 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Could you post your select statement? I use linked servers using ODBC to an AS400 and have not seen this particular issue.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

Hi

Sorry to drop by like that but that's interesting because I have the same problem. The following is the query that I got from this forum

Select * from openQuery(IBMAS400, 'Select * from "LIBRARY"."FILENAME"')

Ben Blum Right there with Babe Points: 776 More actions · Answer 3

Same thing happens to me, I'd love to know why because it makes it hard to trust a solution when you can't even get the correct number of records to come accross.

Linda Johanning SSCertifiable Points: 6727 More actions · Answer 4

We had the same issue with the Client Access driver and could not count on the results, so we ended up purchasing a driver called HiT to install on the SQL box. Haven't had missing records since....

Linda

is2gu Grasshopper Points: 23 More actions · Answer 5

I use the odbc driver that came with Rumba 2000. It is probably not as fast as HIT but works great and was free. Does anybody else use this?

Jacob Mays

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

I think I'm using Client Access v5.1

So, for data transfer (especially from AS400 to SQL), I'm now using DTS. At times I use a temporary table to first store the result and then compare with existing tables.... and I insert only the latest ones. That's assuming that there's a primary key on the AS400 table (or a combination of fields to uniquely identify the record). So far no problems. Then I just create a job on that DTS and either schedule it or allow the user to launch the job via ASP

For upload, a bit more tricky. I send the result to a text file and then execute a DOS batch file, which basically utilises an "upload" program included in Client Access v5.1... all within a DTS package. Worked fine so far too.

fernando.pires@gustavsberg.com SSC Enthusiast Points: 106 More actions · Answer 7

It is simple to fix this.

1. Open the ODBC source.

2. Go to the tab 'Performance'.

3. Click the button 'Advanced'

4. Uncheck the option for 'row blocking'. Se picture below.

Sorry it´s in swedish. But i think you will get the picture.

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

Does not work.... Still same number of limited records

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

Funny..... Now it works... think after I set the setting, must wait for a while or must restart the connection... now it works!!

Thanks a zillion...

How about uploading? Ever tried Delete and Insert statement?

fernando.pires@gustavsberg.com SSC Enthusiast Points: 106 More actions · Answer 10

If you have permission to delete and insert ? Why not ?

Remember that you can set the ODBC source to read-only if you want.

Another tip is that you should set the ODBC connect to disconnect immed. It should not wait (there is a setting for this also).

Why ? Because iSERIES is a bit dumb when terminating ODBC connections or you can say that Windows is dumb in not notifying when it is finished. If you are unlucky you can have a connection going haywire taking up 100% of the CPU.

If you are a iSERIES admin you should from time to time use this command.

wrkactjob job(QZDASOINIT)

I have made it a habbit to do this when i for example have closed a connection (ODBC) that looks like it has been hung.

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

Thanks a zillion again.

Unfortunately I have no control over the iSERIES. But anyway, I've passed the command to the administrator.

Regarding the insert/delete command.... do I need to use the OPENQUERY method? I issued the following command

Select * from openQuery(IBMAS400, 'DELETE from "SHEAU"."AA_CHEQUE"')

... and I got the error:

Server: Msg 7357, Level 16, State 2, Line 1

Could not process object 'DELETE from "SHEAU"."AA_CHEQUE"'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.

OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDASQL', Query=DELETE from "SHEAU"."AA_CHEQUE"'].

Any reasons?

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 12

Richard Cranston

Hall of Fame

Points: 3680

April 8, 2004 at 7:39 am

#502141

Thanks, unchecking the 'row blocking' worked.

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 13

I found that anytime you update/delete records on the AS400, the file needs to be journaled.

Vanyel Ashkevron Newbie Points: 1 More actions · Answer 14

What do you mean by 'journaled' and how do I do that from the SQL server side?

Thanx for the block tip.