AS400 linked server via Client Access ODBC

  • 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?

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

  • 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"')

     

  • 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.

  • 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

  • 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

  • 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.

  • 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.

     

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

  • 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? 

  • 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.

  • 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?

  • Thanks, unchecking the 'row blocking' worked.

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

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

    Thanx for the block tip.

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

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