Connecting Toad for SQL Server to AS400

=====================================

In today's interconnected world, organizations increasingly rely on diverse data sources within their IT infrastructure. When working with legacy systems like IBM System i (formerly known as AS/400), it is essential to establish efficient connections between these platforms and newer technologies such as Microsoft SQL Server.

Toad for SQL Server offers a robust set of tools that can help bridge this gap by providing connectivity solutions for various data sources, including DB2 and AS400. In this article, we will explore the process of connecting Toad for SQL Server to an AS400 database using linked servers and stored procedures in SQL Server.

Prerequisites

---------

Before diving into the configuration steps, ensure you meet the following prerequisites:

1. Toad for SQL Server is installed on your machine.

2. SQL Server Management Studio (SSMS) is also available since we will be performing some administrative tasks using SSMS later in this article.

3. Microsoft Host Integration Services 2005 (HIS) Service Pack 1 or higher, along with the latest cumulative updates, are installed on all servers involved.

Step-by-Step Configuration

--------------------------

### Step 1: Install Microsoft Host Integration Server

To establish a connection between SQL Server and AS400/DB2 databases using linked servers, you need to install Microsoft Host Integration Server (HIS) on one of your machines. HIS acts as an integration layer that provides the necessary drivers for communication between different systems.

Follow these steps:

1. Download and extract the latest version of Microsoft Host Integration Server from 'here'(https://www.microsoft.com/download/details.aspx?displaylang=en).

2. Run the extracted setup file (for example, `HIS2005SP1-x86.exe`) as an administrator.

3. Follow the installation wizard's instructions to complete the installation process.

### Step Step 2: Configure DB2OLEDB Driver

After installing HIS, you need to configure and register the DB2OLEDB driver on your SQL Server instance:

1. Open SSMS by double-clicking on its icon or navigating through Start > All Programs > Microsoft SQL Server > 'Your Edition' > Tools.

2. Expand Object Explorer, right-click on your desired server name in Object Explorer, then select Properties from the context menu.

In the properties dialog box that appears:

* Click on Linked Servers under the Security tab and click on New...

* In the Linked Server - New window:

* Enter a linked server name (for example, AS400\_LINK).

* Select 'OLE DB' as your Data Source Type.

* For Data Source, enter the fully qualified path to your AS400 database file system directory (for instance, `\\AS400_SERVER_NAME\DBNAME`).

### Step Step 3: Create a Linked Server

Now that we have configured our data source and driver:

1. In SSMS Object Explorer:

* Right-click on the server name in Object Explorer.

2. Select 'Linked Servers' > 'New Linked Server...' from the context menu.

In the New Linked Server - AS400\_LINK dialog box:

* For Link Name, enter a descriptive name for your linked server (for example, AS400_LINK).

* Under Provider dropdown list, select 'Microsoft OLE DB Provider for iSeries'.

### Step Step 4: Test Your Connection

After completing the above steps and restarting SSMS if necessary:

1. Open SQL Server Management Studio.

2. In Object Explorer:

* Expand 'Server Name' > 'Linked Servers'.

3. Double-click on your newly created linked server (for example, AS400\_LINK).

If everything has been set up correctly without any errors, you should now see the list of available databases under Tables node in SSMS's Object Browser.

-------

In conclusion, connecting Toad for SQL Server to an AS400 database involves several steps that include installing Microsoft Host Integration Services (HIS), configuring DB2OLEDB drivers, creating a linked server, and testing your connection. By following this step-by-step guide, you should now be able to establish a robust data link between your legacy System i platform and newer SQL Server environment using Toad for SQL Server's powerful tools.

Remember that these instructions are meant as general guidelines only; always consult official Microsoft documentation or contact IBM support if faced with specific issues related to HIS setup on AS400 systems.

Connecting toad sql server to as400