How to add DB2 as a linked server in SQL

 How to add DB2 as a linked server  in SQL

In this post, I will demonstrate how to add a new linked server in SQL Server.

-- Creating the Linked Server: [AS400DB]
EXEC master.dbo.sp_addlinkedserver 
    @server = N'AS400DB',
    @srvproduct = N'AS400',
    @provider = N'IBMDASQL',
    @datasrc = N'10.0.1.1';

-- Setting up the linked server login
EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'AS400DB',
    @useself = N'False',
    @locallogin = NULL,
    @rmtuser = N'MyUser',
    @rmtpassword = '########';
GO

-- Configuring server options
EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'collation compatible',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'data access',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'dist',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'pub',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'rpc',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'rpc out',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'sub',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'connect timeout',
    @optvalue = N'0';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'collation name',
    @optvalue = NULL;
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'lazy schema validation',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'query timeout',
    @optvalue = N'0';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'use remote collation',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'remote proc transaction promotion',
    @optvalue = N'true';
GO

This script will successfully configure the linked server, allowing you to connect to an AS400 database from SQL Server. Ensure that all server names, IP addresses, and credentials are accurate before executing.