Post reply

Data Extraction from AS400 to Sql Server

  • August 24, 2006 at 6:20 am

    #68432

    Hi

    I am using Sql Server 2000 and i am extracting data from AS400.

    i am using for this Routeone softwear but it is slow is there any quick way to do it either by DTS or Anything else.

     

    Thanks

     

  • August 24, 2006 at 6:50 am

    #656551

    HI your connection with AS is ODBC? if ok it´s very slow.

  • August 25, 2006 at 2:10 am

    #656779

    Sorry for explaining this .........

    Actually i am using IIS FTP server

    on AS400 routeone and on sql server routeone client routeone from AS400 is connected with FTP server and it transfer file in csv format after that DTS convert it to SQL Table.

    Don't know how routeone is connected n AS400. But i looking for anyother way with which i can do extraction fast.

    Thanks.

  • August 25, 2006 at 6:56 am

    #656810

    In SQL 2000 there is an OLE DB for ODBC provider (MSDASQL) that you can use to set up a linked server to the AS/400. Once you set up the linked server, then you can use that data source directly in a DTS package and skip the RouteOne software and IIS FTP altogether.

    If speed is the only issue, then you may want to look into reading from and writing to better performing drives like a SAN or iSCSI. If you suspect the network to be the bottleneck, then try using a gigbit network adapter. You can use the Performance Monitor if the bottleneck is even on the SQL Server.

  • August 25, 2006 at 7:31 am

    #656823

    Vandy-

    Greg is correct; as  we currently have a linked server connection to an AS400 and it works well in my environment. Let me know if you have any issue setting up a linked server; I can try to help. Thanks!

  • August 25, 2006 at 9:43 am

    #656859

    you can install IBM iSeries Access for Windows, and it will provide you the different librarys to connecto to AS400 by ODBC

  • August 25, 2006 at 9:56 am

    #656861

    Is the IBM iSeries Access for Windows downloadable from the Internet somewhere? thanks.


    smv929

  • August 25, 2006 at 4:13 pm

    #656963

    About the IBM iSeries Access for Windows, I think is only bundled with IBM's AS/400 software. I've have never seen it for download. Online only patches and PTFs.

    About the fasted method, imho, if you will download large amounts of data, DTS using OLE DB is way much faster than a linked server.

     

  • August 25, 2006 at 5:37 pm

    #656978

    That is correct DTS on xp_cmdshell running as job can make it almost real time, remember SQL Server Agent needs access to the network which means admin level permissions.  Hope this helps.

    Kind regards,

    Gift Peddie

    Kind regards,
    Gift Peddie

  • August 28, 2006 at 8:26 am

    #657097

    1. Install IBM iSeries Access for windows ODBC Driver. This driver comes with IBM's AS/400 software.
    2. Create a Data Source selecting the iSeries Access ODBC Driver. From control panel-- > administrative tools-- >Data Sources
    3. In SQL Server, create the link server to AS/400. From Security -- > Link Server. Once you create the linked server, then you can use that data source in a DTS package.   
    4. If you are not able to create a link server then just create a DTS package and import data from ODBC driver – DNS name that you created in step (2) to SQL Server.

     

Viewing 10 posts - 1 through 9 (of 9 total)

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