1659 - Creating a Linked Server

You can use HiT OLEDB Server/400 or HiT OLEDB Server/DB2 and Microsoft SQL Server 2008 to manage data stored on DB2 and integrate it with SQL Server. Using the Distributed Query features that Microsoft SQL Server 2008 offers, you can join data stored on native databases, with tables stored on DB2, MS Access files and almost any type of file.

To use this functionality, you will need to create a Linked Server, and a connection to your remote database.   If you are trying to create a linked server to DB2/400, you will need HiT OLEDB Server/400. For DB2 running on other platforms, please use HiT OLEDB Server/DB2 instead. Before creating the linked server, you need to set the “Allow InProcess” provider option. This is to allow Microsoft SQL Server 2008 to use the HiT OLEDB provider DLL by loading it directly into the Microsoft SQL Server 2008 address space.

Setting a Provider Option from SQL Server Management Studio

1. Open SQL Server Management Studio.
2. Select Providers from the Server Objects/Linked Server section.
3. Right-click on the HITOLEDB provider name, then choose the Properties option.
4. Make sure that the Allow inprocess option is checked.

Creating a Data Link File using the HiT Toolbox

The easiest way to create a linked server is to use an UDL file, also know as data link file. This will allow you to specify all connection parameters in one file and simply refer to the file for all future connections. A data link file can be created with the HiT Toolbox. For more information about creating and configuring a data link file, look under “Creating a Data Link in the Toolbox” in HiT OLEDB ToolBox Help.

Once you have created the data link file, here are a few things to note when creating a linked server:

• In the linked server property, data source field, type the name of the UDL file including the .UDL extension. For example, if you created a data link in the HiT OLEDB ToolBox and called it “MyAS400”, you should enter “MyAS400.udl” in the linked server property data source field.
• In the linked server property, product name field, type the name of the OLE DB data source to add as a linked server. For example “OLEDB for AS400”.
• When a data link file is created, by default, it is placed as follows:
  
  Windows XP: C:\Program Files\Common Files\System\Ole DB\Data Links
  Windows 2008, Vista or Windows 7: C:\ProgramData\HiT Software\Data Links 
  
  In HiT OLEDB version 5.0, if you keep the UDL file in the default location, SQL Server is smart enough to know where to look for it and there is no need to specify the path. However, if you are using an older version of the HiT OLEDB provider or if you moved the file to a different location, you will need to specify the complete path to the file unless using the default location in Windows XP environment.
• We recommend using the udl file for the connection. However, if you need to run INSERT, UPDATE or DELETE statements in addition to SELECT statements, please add the following into the linked server provider string:
  
  Port Number=8471; Rowset Locate=True; 

Then just follow steps 1 through 6 below to finish.

 If you choose not to use a data link file to create a linked server, use the steps below.  

Creating a Linked Server using SQL Server Management Studio

1. Start the SQL Server Management Studio.
2. Select Linked Server from the Server Objects menu.

3.  Right-click on Linked Server, New linked Server and insert data in the configuration window.
4. In the General tab insert the required parameters:

Linked Server:   Insert the reference name for this linked server. It’s preferable to use a name without spaces in order to avoid problems when using it in an SQL statement.

Provider Name: Select HiT Software OLEDB Provider for DB2/400 if you are trying to connect to the IBM DB2 for i (iSeries/AS400). If you are trying to connect to DB2 on platforms other than iSeries/AS400, please choose HiT Software OLEDB Provider for DB2 instead.

Product Name:   Insert the name of OLEDB data source to add as linked server.

Data Source:      Insert the IP address of your DB2 server or the name of a data link (UDL) file.

Provider String: Insert the connection string with the necessary parameters
Example:
Libraries=My_libraries; Init Timeout=90; Port Number= 8471; Host Code Page=037;Rowset Locate=True;

Please note that the parameters for the provider string are case sensitive.

5. Select the Security Tab.  For information on setting security parameters, click on Help. 

   6.  Use the Server Options tab to view or specify the following options:

• SetCollation Compatible as TRUE if you want the WHERE and ORDER BY clauses on character fields to be resolved by DB2 and not by SQL Server.  For a detailed description of the Collation Compatible option, look up Collation Compatible in the HiT Software knowledge base.
• CheckRPC Out to call a DB2 stored procedure using Query Analyzer and T-SQL.

After this operation, the tables on the linked server are available for all features that are compatible with Microsoft SQL Server 2008.

You can use either Four Part Name Query or OpenQuery with linked server. For example, for a Linked Server called AS400, open a new query window in SQL Server Management Studio and run either of the following:

Four Part Name Queries:

Select * from AS400.ServerName.MyLib.Customers 
Update AS400.ServerName.MyLib.Customers Set FIRSTNAME = ‘John’ Where ID=5 
Delete from AS400.ServerName.MyLib.Customers Where ID=10

where: 

AS400 is the Linked Server Name 
ServerName is the IBM DB2 for i (AS/400) Server Name 
MyLib is the IBM DB2 for i Library Name 
Customers is the Table Name

OpenQueries:

Select * from OpenQuery (AS400 , 'Select * from MyLib.Customers' )
UPDATE Openquery (AS400 , 'Select * from MyLib.Customers Where ID=5' ) 
Set FIRSTNAME = 'John' 
Delete Openquery (AS400 , 'Select * from MyLib.Customers Where ID=10' )