Linking AS/400 from SQL Server

If you ever wanted to link your SQL Server with an AS/400 box, this is how to do so, follow these "easy" instructions and you are done.

If you want to set up a linked server with AS/400, first things first: You must make sure that your SQL Server has the OLE DB provider for DB2, you can get it for free with this link:

http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/DB2OLEDB.exe

If you need to check that the OLEDB provider for DB2 is already installed, open your SQL Server Management Studio Console and browse to Server Objects\Linked Servers\Providers, once it is set up, it will be listed as DB2OLEDB.

Other thing you are going to need is an account in the AS/400 box, this will make SQL Server act as a client for the AS/400, be sure to give only the strictly necessary rights to that account, avoid granting open wide privileges to service accounts, like the one you are creating in your AS/400 in this point.

The next step is creating your linked server... finally! open a new query in your Management Studio Console by pressing CTLR+N and copy/paste the following commands, obviusly, you must change the <...> in each parameter accordingly

EXEC master.dbo.sp_addlinkedserver

@server = N'<...>', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB', @datasrc=N'<...>', @provstr=N'Provider=DB2OLEDB;Password=<...>;Persist Security Info=True;User ID=<...>;Initial Catalog=<...>;Network Address=<...>;Package Collection=<...>', @catalog=N'<...>'

Here is a brief explanation of each parameter

@server= This will be the "internal" name to identify your linked server from others you already have.
@datasrc= this is your AS/400 host name
@catalog= This is the database in AS/400 you want to connect to. If you don't know which database is available, either ask to the AS/400 administrator or use the WRKRDBDIRE command, tipically the entry that has a *LOCAL value in the remote location column, is the database to specify in this parameter... Be Extremely carefull when using this command you can screw up the AS/400 and I would not blame its administrator if he wants to kill you after! maybe I would kill him for leaving commands like this available to anyone, so hands off!.
Password= The AS/400 password for the account created for your linked server
User ID= The AS/400 user ID for the account created for your linked server
Initial Catalog= same as @catalog
Network Address= the Ip Address for your AS/400
Package Collection= This is a somewhat equivalent to a schema in SQL Server, you must specify the package where the tables you need are located. If you don't know which package collection specify, use the AS/400 command WRKOBJ and take note of the name in the Library column

I hope this can resolve your problems linking SQL with DB2!