Set the tables from a link in SQL 2008

I have the link nget the tables from the AS400
how can I do that?
Who is Participating?
jaymz69Author Commented:
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.

jaymz69Author Commented:
What I found and it worked.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.