Connect to DB2: A Guide to Linked Servers via Host Integration Server

Table of Contents

Establishing connectivity between SQL Server and IBM DB2 databases is a common requirement in enterprise environments. Often, organizations need to integrate data residing on mainframe or IBM i (AS/400) systems running DB2 with their SQL Server applications. While various methods exist, utilizing a linked server through Microsoft Host Integration Server (HIS) is a robust and frequently employed approach. HIS provides the necessary middleware layer, translating data requests between the two disparate database systems and protocols.

Linked servers in SQL Server allow database administrators and developers to execute distributed queries, stored procedures, and remote stored procedures against OLE DB data sources. By configuring DB2 as a linked server, SQL Server can treat the remote DB2 database as if it were another local database, enabling seamless data access and integration directly from SQL Server queries and applications. This capability simplifies complex data retrieval scenarios that span across both platforms, reducing the need for cumbersome data extraction, transformation, and loading (ETL) processes for simple lookups or reporting.

Prerequisites for Setting Up DB2 Linked Servers via HIS¶

Before you can establish a successful linked server connection from SQL Server to DB2 using Host Integration Server, several prerequisites must be met. These prerequisites span software installation, configuration, and network setup to ensure reliable communication and data exchange between the two systems. Failing to address any of these critical points can lead to connection errors or performance issues during data access.

Firstly, you must have Microsoft Host Integration Server installed and properly configured. Specifically, the Data Access components of HIS are required. These components include the Microsoft OLE DB Provider for DB2 or the Microsoft ADO.NET Data Provider for DB2, depending on how you intend to connect. The OLE DB provider is necessary for configuring a linked server in SQL Server, as linked servers rely on OLE DB providers to interact with external data sources. Ensure the HIS server is running and accessible from the SQL Server instance.

Secondly, the target DB2 database must be accessible over the network from the Host Integration Server. This involves ensuring proper network connectivity, including firewalls and routing configurations, between the HIS server and the DB2 host. Depending on the DB2 platform (z/OS, iSeries, LUW), different communication protocols might be used, such as TCP/IP or SNA (Systems Network Architecture). HIS supports both, but modern deployments typically leverage TCP/IP. You will need the DB2 host name or IP address, the port number the DB2 listener is using (commonly 50000 or other application-specific ports for TCP/IP), and the specific database name (location name for DB2 z/OS, database name for DB2 LUW, or RDB name for DB2 iSeries).

Thirdly, appropriate database client software might be needed on the HIS server. While HIS comes with its own data providers, sometimes native client installations (like IBM Data Server Client) can be helpful or even required for specific configurations or advanced features, although HIS providers are often sufficient. Verify the documentation for your specific version of HIS and DB2 to confirm client requirements. Crucially, a Data Source Name (DSN) or equivalent configuration must be created within the HIS environment to define the connection parameters to the target DB2 database. This configuration is often done using the HIS Data Source Wizard or the HIS OLE DB Provider for DB2 configuration tool.

Finally, proper security credentials and permissions are essential. The user account used to connect from SQL Server (either the SQL Server service account, a specific login mapped to the linked server, or a pass-through login) must have valid credentials on the DB2 database server. These credentials must have the necessary privileges within DB2 to access the specific schemas, tables, and views you intend to query. Understanding the authentication methods supported by the DB2 database and configured in the HIS data source definition (e.g., current user, specific username/password) is crucial for successful authentication.

Configuring the DB2 Data Source in Host Integration Server¶

The cornerstone of connecting SQL Server to DB2 via HIS is the correct configuration of the DB2 data source within the Host Integration Server environment. This step tells the HIS OLE DB Provider for DB2 how to locate and connect to the specific DB2 database you wish to access. The configuration process typically involves using the HIS Data Source Wizard or manually creating a Universal Data Link (UDL) file that uses the Microsoft OLE DB Provider for DB2.

Using the Data Source Wizard is the recommended approach for most users as it provides a guided interface. You will need to specify the network protocol (usually TCP/IP), the remote DB2 system’s address (host name or IP address), the port number, and the name of the target DB2 database. The wizard also prompts for authentication information. This can be a specific username and password that the HIS provider will use to connect to DB2, or it can be configured to use integrated security if the environment supports it and is set up correctly.

For TCP/IP connections, the provider establishes a direct socket connection to the DB2 database listener. Key parameters include the IP address or hostname of the DB2 server, the TCP port number, and the Initial Catalog (which corresponds to the DB2 database name). Different DB2 platforms have slightly different conventions for the database name parameter:
- For DB2 on z/OS, this is the Location Name.
- For DB2 on IBM i (AS/400), this is the Relational Database Name (RDB name).
- For DB2 on LUW (Linux, UNIX, Windows), this is the Database Name.

Authentication methods are critical. Options typically include:
- Standard Security: Providing a static username and password defined within DB2. This is the most common method.
- Package Security: Often used with SNA connections, but less common with TCP/IP.
- Integrated Security: Using the Windows credentials of the calling user or service account. This requires specific configuration on both the Windows domain and the DB2 server (e.g., using Distributed Relational Database Architecture - DRDA - access to RACF or other security managers).

It’s crucial to test the connection during or after creating the data source definition within the HIS tools. The HIS data source configuration utility usually provides a “Test Connection” button. A successful test confirms that the HIS server can communicate with the DB2 database using the specified parameters and credentials. If the test fails, troubleshoot network issues, firewall blocks, incorrect host/port/database name, or invalid credentials before proceeding.

This configured data source (which might be saved as a UDL file or registered in the system) serves as the bridge definition that the SQL Server linked server will reference. It abstracts the low-level connection details, allowing the linked server configuration in SQL Server to simply point to this predefined connection configuration.

Creating the Linked Server in SQL Server¶

Once the DB2 data source is properly configured within Host Integration Server and the connection can be tested successfully from the HIS machine, the next step is to create the linked server object within SQL Server. This can be accomplished using SQL Server Management Studio (SSMS) graphical interface or via Transact-SQL (T-SQL) scripts.

Using SSMS:
1. Open SSMS and connect to your SQL Server instance.
2. Navigate to Server Objects -> Linked Servers.
3. Right-click on Linked Servers and select New Linked Server….
4. In the New Linked Server dialog box:
* Under Linked server, enter a descriptive name for your linked server (e.g., LINKED_DB2_SALES).
* Under Server type, select Other data source.
* Under Provider, select Microsoft OLE DB Provider for DB2. This is the provider included with Host Integration Server.
* In the Product name field, you can enter a descriptive name for the data source product (e.g., IBM DB2).
* In the Data source field, you will specify how the OLE DB provider finds the DB2 connection information. This is typically the name of the UDL file you created with the HIS Data Source Wizard, including its full path (e.g., C:\Program Files\Microsoft Host Integration Server\Data Sources\MyDB2Data.udl). Alternatively, some configurations allow entering connection string parameters directly here or referencing a system-defined data source name, but using a UDL file is common with the HIS provider.
* Leave Provider string and Location usually empty unless specific advanced options are required by the provider documentation.
* Leave Catalog empty, as the database name is specified in the data source UDL file.

5. Go to the Security page. This is where you define how SQL Server authenticates to the linked server. Common options include:

   • Not be made: No connection is made.
   • Be made using the login’s current security context: Attempts to use the Windows credentials of the logged-in SQL Server user. This works if Kerberos delegation is configured and the DB2 server supports integrated authentication from Windows principals.
   • Be made using a security context that was NOT made: You define mappings for local SQL Server logins to remote DB2 logins. For example, you can map a specific SQL Server login to a specific DB2 username and password. This is a common and often simpler approach. You can also set a default remote login for all local logins not explicitly mapped.
   • Be made using the login’s current security context OR be made using a security context that was NOT made: A combination of the above.

   Configure the security context based on how your HIS data source is configured and what DB2 allows. The most straightforward is often providing a remote login and password for a specific SQL Server login or for all logins not defined.

6. Go to the Server Options page. Here you configure various settings affecting how SQL Server interacts with the provider. Most settings can be left at their defaults initially, but one crucial setting relates to the Allow InProcess option discussed in the original snippet. While you can view this setting here after creation, it’s often controlled differently initially. Other important options include:

   • RPC and RPC Out: Allow remote stored procedure calls.
   • Collation Compatible: Set if collation rules are the same on both servers.
   • Data Access: Must be True to enable queries.
   • Use Remote Collation: Determines if SQL Server or the remote server’s collation rules are used for character comparisons.

7. Click OK to create the linked server.

Using T-SQL:
You can achieve the same using the sp_addlinkedserver and sp_addlinkedsrvlogin stored procedures.

-- Add the linked server
EXEC sp_addlinkedserver
    @server = N'LINKED_DB2_SALES',  -- Name of the linked server
    @srvproduct = N'IBM DB2',      -- Product name
    @provider = N'MSDASQL',        -- Or 'Microsoft.HostIntegration.OLEDB.DB2' depending on exact provider/version
    @datasrc = N'C:\Program Files\Microsoft Host Integration Server\Data Sources\MyDB2Data.udl'; -- Path to the UDL file

-- Map a specific local login to a remote login/password
-- This example maps the SQL Server login 'MySQLLogin'
-- to the DB2 user 'DB2USER' with password 'DB2Password'.
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N'LINKED_DB2_SALES',
    @useself = N'False', -- Specify specific remote login/password
    @locallogin = N'MySQLLogin',
    @rmtuser = N'DB2USER',
    @rmtpassword = N'DB2Password';

-- Or map all local logins not explicitly mapped to a single remote login/password
-- EXEC sp_addlinkedsrvlogin
--     @rmtsrvname = N'LINKED_DB2_SALES',
--     @useself = N'False', -- Specify specific remote login/password
--     @locallogin = NULL,   -- NULL means for all local logins not explicitly mapped
--     @rmtuser = N'DefaultDB2User',
--     @rmtpassword = N'DefaultDB2Password';

Note that the provider name (@provider) might vary slightly depending on the specific HIS version and how the OLE DB provider is registered. Common values are MSDASQL (if using the Microsoft OLE DB Provider for ODBC, which then uses the HIS ODBC driver) or the direct OLE DB provider name like Microsoft.HostIntegration.OLEDB.DB2. Check the HIS documentation for the exact provider name to use with sp_addlinkedserver. Using the direct OLE DB provider is generally preferred.

The ‘Allow InProcess’ Setting¶

The original snippet highlights a specific configuration setting for the OLE DB provider used by the linked server: the Allow InProcess setting. This setting is crucial for many OLE DB providers, including the Microsoft OLE DB Provider for DB2, and its configuration can significantly impact the linked server’s behavior and stability.

In SQL Server, OLE DB providers can be configured to run either in-process with the SQL Server instance (within the SQL Server memory space) or out-of-process (in a separate process, usually odbcad32.exe or a similar host process). Running a provider out-of-process offers greater isolation; if the provider encounters an error or crashes, it is less likely to directly affect the SQL Server process itself, potentially preventing a SQL Server service crash.

However, running a provider out-of-process also incurs overhead due to inter-process communication. Data requests and results must be marshaled between the SQL Server process and the provider’s process, which adds latency and can impact performance, especially for frequent or large data transfers.

Many OLE DB providers, including the Microsoft OLE DB Provider for DB2 from HIS, are designed and tested primarily to run in-process for optimal performance and compatibility. The Allow InProcess setting, when enabled, permits SQL Server to load and run the provider directly within its own address space.

The method described in the snippet – enabling it via the Properties dialog of the linked server under the General tab, then clicking Options, and finally enabling Allow InProcess – is often the only way to enable this specific flag for a given provider. Once you enable Allow InProcess for the Microsoft OLE DB Provider for DB2 using this method for one linked server, SQL Server remembers this preference for that specific provider. Subsequently created linked servers using the same provider (e.g., Microsoft.HostIntegration.OLEDB.DB2) will automatically use the in-process setting, even if you create them via T-SQL. This is a provider-specific flag managed by SQL Server’s OLE DB provider configuration rather than just a linked server setting.

Why is Allow InProcess important for the HIS DB2 Provider? Running the HIS DB2 provider in-process generally provides better performance due to reduced communication overhead. It allows the provider to interact more directly and efficiently with the underlying HIS client components responsible for communicating with DB2. While running out-of-process is theoretically safer from an isolation perspective, the Microsoft OLE DB Provider for DB2 is typically stable when configured correctly, and running it in-process is the recommended and default operational mode after it’s enabled.

If you encounter instability or crashes seemingly related to the linked server, disabling Allow InProcess might be a troubleshooting step, forcing the provider to run out-of-process. However, be aware that this might introduce a performance penalty. For production environments, ensuring the provider is running in-process after successful testing is usually the goal for optimal performance.

Testing and Using the Linked Server¶

After creating the linked server and configuring the Allow InProcess setting (if necessary), it’s crucial to test the connection and verify that you can access data from the remote DB2 database.

The simplest test is to attempt to query a table in the remote database using the four-part naming convention: [LinkedServerName].[Catalog].[Schema].[Object].

For example, if your linked server is named LINKED_DB2_SALES, the DB2 database name is DB2CAT, and you want to query a table named CUSTOMER in the SALES schema on DB2, the query would look like this:

SELECT TOP 10 *
FROM [LINKED_DB2_SALES].[DB2CAT].[SALES].[CUSTOMER];

Note that the middle part of the four-part name corresponds to the DB2 catalog name (database name, location name, or RDB name) specified in your HIS data source configuration. The third part is the schema name within DB2 (also known as collection ID on iSeries or schema name on LUW/z).

If this query returns data without errors, your basic linked server connection is working. If it fails, check the error message carefully. Common errors include:
* Login failed: Indicates a problem with authentication credentials. Verify the security configuration of the linked server and the credentials in the HIS data source definition. Ensure the DB2 user exists and has privileges.
* Cannot find the object: Indicates that the table, schema, or database name is incorrect in your four-part name or that the DB2 user does not have permissions to see the object. Verify the object names and case sensitivity (DB2 object names are often uppercase).
* OLE DB Provider error: A generic error from the provider. Check the HIS logs and the Windows Event Viewer on both the SQL Server and HIS server machines for more detailed error messages from the Microsoft OLE DB Provider for DB2. This often points to network issues, configuration problems in the HIS data source, or provider-specific issues.

You can also use the OPENQUERY function for querying linked servers. OPENQUERY sends the entire query string to the linked server for execution, which can sometimes be more efficient as it allows the remote database (DB2) to process the query using its own query optimizer before returning the results.

SELECT *
FROM OPENQUERY(LINKED_DB2_SALES, 'SELECT COL1, COL2 FROM SALES.CUSTOMER WHERE CUSTOMER_ID = 12345');

Using OPENQUERY is highly recommended for complex queries with filtering, joins, or aggregations, as it leverages the power of the DB2 database engine. When using the four-part name directly, SQL Server might attempt to pull the entire remote table and perform operations locally, which is extremely inefficient for large tables.

Troubleshooting Common Issues¶

Setting up linked servers to DB2 via HIS can sometimes present challenges. Here are some common issues and troubleshooting steps:

1. Network Connectivity: Ensure the SQL Server can reach the HIS server, and the HIS server can reach the DB2 host on the specified port. Use ping and telnet (or Test-NetConnection in PowerShell) from the HIS server to the DB2 host and port to verify basic connectivity. Check firewalls on both sides and any network devices in between.
2. HIS Data Source Configuration: Double-check the DB2 host name/IP address, port number, and database name in your HIS data source configuration. Ensure the correct network protocol (usually TCP/IP) is selected. Verify the authentication method and credentials are correct and match what DB2 expects. Use the “Test Connection” feature in the HIS tool.
3. Authentication/Permissions: This is a frequent source of errors.
   • Ensure the username and password configured in the HIS data source (if using standard security) or the credentials mapped in the linked server security page are valid DB2 credentials.
   • Verify the DB2 user has CONNECT privileges to the database and SELECT privileges on the specific tables/schemas you are trying to access.
   • If using integrated security, ensure the required configurations (SPNs, Kerberos delegation) are correctly set up on both the Windows domain and the DB2 server side.
4. OLE DB Provider Issues:
   • Ensure the Microsoft OLE DB Provider for DB2 (or the specific version included with your HIS installation) is correctly installed and registered on the SQL Server machine (or the HIS machine if the provider runs there, which is usually the case with HIS components).
   • Check the version compatibility between HIS, the OLE DB provider, SQL Server, and the DB2 version.
   • Examine the Windows Event Viewer logs on the SQL Server and HIS server for specific error messages from the provider or HIS services.
5. SQL Server Configuration:
   • Verify the linked server name, provider name, and data source parameter (@datasrc in sp_addlinkedserver or the Data source field in SSMS) are correctly specified.
   • Ensure the Data Access option is enabled for the linked server in SSMS (Server Options page).
   • Confirm the Allow InProcess setting is enabled for the Microsoft OLE DB Provider for DB2 if you are encountering provider initialization issues or performance problems, as discussed previously.
6. Object Naming and Case Sensitivity: DB2 object names (tables, schemas, columns) are typically case-sensitive and often uppercase. When querying from SQL Server, ensure you use the correct case in your four-part names or OPENQUERY strings. Enclosing object names in double quotes in OPENQUERY queries can sometimes help with case sensitivity or special characters (e.g., SELECT "COLUMN_NAME" FROM "SCHEMA_NAME"."TABLE_NAME").

When troubleshooting, start by verifying the most basic steps: network connectivity from HIS to DB2, followed by the HIS data source connection test. Only then move on to troubleshooting the linked server configuration in SQL Server and the specific queries.

Performance Considerations and Best Practices¶

Querying remote databases via linked servers, especially across different platforms like SQL Server and DB2, introduces performance considerations. Network latency, data transfer volume, and the capabilities of the OLE DB provider and the remote database optimizer all play a role.

1. Use OPENQUERY for Remote Processing: As mentioned earlier, favor OPENQUERY for any query that involves WHERE clauses, JOINs, GROUP BY clauses, or aggregations. This pushes the processing to the DB2 server, which is optimized for handling its own data. Executing SELECT * FROM [LinkedServer]... and then filtering or joining in SQL Server can be disastrously slow for large tables as it pulls all data across the network.
2. Retrieve Only Necessary Columns and Rows: Select only the columns you need, not SELECT *. Use WHERE clauses to filter rows as early as possible. This minimizes the amount of data transferred over the network.
3. Understand Remote Query Execution: Be aware that SQL Server’s query optimizer has limited information about the statistics and indexing on the remote DB2 database. It relies on the OLE DB provider to provide this information, but it’s not always complete or optimally used. This is another reason why OPENQUERY is preferred, as it defers optimization to the remote DB2 optimizer.
4. Index Appropriately on DB2: Ensure that the DB2 tables you query frequently via the linked server have appropriate indexes to support the WHERE and JOIN conditions used in your queries. A well-indexed table on the DB2 side is critical for good performance when accessed remotely.
5. Monitor Network Traffic: Use network monitoring tools to understand the volume of data being transferred between SQL Server, HIS, and DB2. High network traffic for relatively simple queries might indicate that filtering or processing is happening on the SQL Server side instead of being pushed down to DB2.
6. Configure Allow InProcess (Enabled): As discussed, running the OLE DB provider in-process is generally recommended for better performance compared to running it out-of-process.
7. Consider sp_executesql with OPENQUERY: For dynamic queries or queries where parameters are needed, you can combine sp_executesql with OPENQUERY to execute the remote query safely while passing parameters. This prevents SQL injection issues compared to building the query string using simple string concatenation.

DECLARE @RemoteQuery nvarchar(max);
DECLARE @CustomerID int = 12345;

SET @RemoteQuery = N'SELECT COL1, COL2 FROM SALES.CUSTOMER WHERE CUSTOMER_ID = ' + CAST(@CustomerID AS nvarchar(10));

SELECT *
FROM OPENQUERY(LINKED_DB2_SALES, @RemoteQuery);

While linked servers provide convenience, they are not always the most performant solution for complex data integration scenarios. For high-volume data transfer, complex transformations, or scenarios requiring transactional consistency across both systems, dedicated ETL tools or message queuing systems might be more appropriate. However, for simple lookups, reporting, or joining small DB2 tables with SQL Server data, linked servers offer a powerful and accessible method.

Configuring a linked server to DB2 using Host Integration Server is a multi-step process involving setup on the HIS server, configuration of the data source, and finally, creation and tuning of the linked server object in SQL Server. Paying close attention to prerequisites, connection parameters, security, and using OPENQUERY effectively are key to a successful and performant implementation.

We hope this detailed guide assists you in connecting your SQL Server environment to DB2 databases using Microsoft Host Integration Server.

What are your experiences or challenges when setting up linked servers to DB2? Share your thoughts and questions in the comments below!