Post reply

AS400 linked server via Client Access ODBC

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

  • March 25, 2004 at 3:21 pm

    #500505

    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

  • March 25, 2004 at 11:54 pm

    #500584

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

     

  • March 26, 2004 at 6:33 am

    #500629

    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.

  • March 26, 2004 at 11:41 am

    #500716

    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

  • March 26, 2004 at 6:30 pm

    #500785

    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

  • March 26, 2004 at 9:50 pm

    #500790

    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.

  • March 27, 2004 at 2:04 am

    #500793

    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.

     

  • March 28, 2004 at 5:17 am

    #500806

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

  • March 28, 2004 at 5:20 am

    #500807

    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? 

  • March 28, 2004 at 6:18 am

    #500808

    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.

  • March 28, 2004 at 7:13 pm

    #500821

    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?

  • April 8, 2004 at 7:39 am

    #502141

    Thanks, unchecking the 'row blocking' worked.

  • April 8, 2004 at 7:43 am

    #502142

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

  • May 7, 2004 at 3:42 pm

    #505718

    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