Connect SQL Server to AS400 with a Linked Server

Question: I need to set up a Linked Server between SQL Server 2016 and AS400 to extract data from a file and import the data onto a table on a SQL Server database.

How do I set up the Linked Server?

Answer:Setting up a Linked Server from SQL Server to AS400 can be tricky , as there a number of variables. But the method I've outlined below has repeatedly worked for me.

Thy trickiest part - and normally most fails occur is in the Provider String . the details below have worked for me

1)Step 1 : Obtain the Microsoft OLE DB Provider for DB2 . You'll need this installed before you attempt to set up the Linked Server. To get the Microsoft OLE DB Provider for DB2   , you'll need to extract from the SQL Server 2016 feature pack. To get the feature pack , go to :https://www.microsoft.com/en-us/download/details.aspx?id=52676

2)Step 2: Obtain some details about the target AS400 server. You will need to work with your AS400 DB2 administrator to get this information. Once you have the details , use them to configure the code in the code on Step 3

Server name

Password

User name 

DB2Database 

Schema

Library

3)Step 3: Set up Linked Server

EXEC sp_addlinkedserver
@server='[Add_LinkedServerName]'
@srvproduct='Microsoft OLE DB Provider for DB2',
@catalog='[Add_DB2DatabaseName],
@provider='DB2OLEDB',
@provstr='NetLib=TCPIP;NetAddr=[Add_Server_Name];NetPort=[Add-Port];RemoteLU=[Add_DB2DatabaseName];LocalLU=LOCAL;ModeName=QPCSUPP;InitCat=[Add_DB2DatabaseName];Default Schema=[Add_Library];PkgCol=[Add_Library];Commit=YES;IsoLvl=NC;CCSID=37;PCCodePage=1252;BinAsChar=NO;Data Source=[Add_DB2DatabaseName]_[Add_Library]'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'[Add_LinkedServerName]',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'[Add_USERNAME]',
@rmtpassword='[Add_PASSWORD]'