Set the tables from a link in SQL 2008

Posted on 2011-04-19
Last Modified: 2012-05-11
I have the link nget the tables from the AS400
how can I do that?
Question by:jaymz69

    Accepted Solution

    In the past we researched this many times before and tried extensively in the past on SQL 2000 and always failed to get linked servers working Via IBM's OLE DB providers. This left us having to use Microsoft's ODBC drivers instead and then a rather clunky manually written Openquery method to query data despite having read that this could be quite slow in comparison.

    Anyway we are finally upgrading our SQL server to 2008 (Standard Edition AP Clusters, we had Enterprise before but didn't use anything bar clustering from this edition) I decided to have another go, having discovered that Microsoft think we ought to pay Enterprise edition money if we want to use their OLE DB drivers for DB2 I was even more determined to get IBM's (included free with Iseries Access) own OLE DB drivers working.

    Anyway I have finally puzzled out settings that work and its actually quite easy through Server Management Studio. I thought I would post details here for anyone else who has struggled to get this working. Similar settings should in theory work through SISS though I haven't done enough with SISS yet to give details!

    First Open Server Management Studio (SMS) and expand out

    Server Objects, then Linked Servers, then Providers.

    Right click Properties on the Providers you want to use (IBM ones start IBM, they are DA400, DASQL and DARLA, The first two perform well the last seems extremely slow so I don't recommend using DARLA unless you have to for some reason.)

    Tick the box for Allow InProcess. This must be done on any IBM driver provider before you create any Linked servers based on it. IBM have a technical document on this if you want the reasons I suggest you read at

    Now for the easy bit...

    Right click Linked Servers and select new.

    Enter the following into each field, where you need to use your own data I have highlighted this in Bold

    "Linked Server" = xxxxxxxx whatever you want to call this linked server, Keep it simple, I suggest the Library name you want to connect to on your iSeries.

    "Provider" = Drop down the list box and select IBM DB2 UDB for iseries IBMDA400 OLE DB provider or IBM DB2 UDB for iseries IBMDASQL OLE DB provider as you wish. as I say I dont recommend the DARLA version as this seems very slow in performance (slower than MS's ODBC drivers!)

    "Product Name" = i520 Anything you like here it really doesn't matter to much so keep it simple again, it wont even be part of the naming string for a select statement.

    "Data Source" = x.x.x.x This is the biggy and I kept trying to give the datasource a datasource name! Just stick to the ip address of your iseries or its declared DNS hostname on your windows DNS servers.

    "Provider String" = User Id=uuuu;Password=pppp;Default Collection=LibraryName; You need to get this right! User Id is your iSeries machine username that you want to connect as (obvioucly must have rights to the Iseries DB2 Library concerned) password that goes with your iSeries account and finally the DB2 Library name you want to link to. To avoid mistakes copy the entire line below to your clipboard and paste it in and edit the User, password and library to suit you.

    User Id=uuuu;Password=pppp;Default Collection=LibraryName;


    "Catalog" = Leave blank as you don't need this.

    Because you have to provided the Account details in the "provider string" section above you do NOT need to provider any logon context info in the "Security page" part of the Linked Server setup.

    Click OK and you should have a working configured ISeries linked server to a library on your machine.

    Repeat the above to all other Libraries on your iSeries hat you need to link to.

    In our tests the IBM OLE DB provider based linked servers (excluding DARLA) worked about three times faster than ones configured through the Microsoft ODBC drivers and a preset ODBC datasource connection to the iSeries.


    Author Closing Comment

    What I found and it worked.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now