Join me on a mission to connect to a SQL Server database from IBM i

This will be a growing blog… I’m going to use this to record a diary of sorts, chronicling my adventures in accessing and reading a Microsoft SQL Database direct from an IBM i application.

Apparently, if the bods at Microsoft can be believed, connecting these two machines is a relatively straight forward concept.

Microsoft offer HIS (Host Integration Server)

What is HIS? Microsoft Host Integration Server (HIS) technologies and tools enable enterprise organizations to integrate existing IBM host systems, programs, messages and data with new Microsoft server applications. HIS allows IT administrators to securely and efficiently connect new systems to existing systems using industry-standard High Performance Routing (HPR) and Transmission Control Protocol (TCP) over Internet Protocol (IP). This reduces operating expenses and total cost of ownership while supporting existing and new computing workloads.

Part of the HIS setup is the DRDA Server – and this simply exposes MS-SQL Server connection to allow IBM Servers to connect and read the tables easily.

Microsoft Host Integration Server – The DRDA Service

The DRDA Service offers technology and tools to enable IT professionals and enterprise developers to deploy new solutions based on Microsoft SQL Server, while connecting existing workloads based on IBM DB2.

IBM i supports DRDA as both a client (application requestor) and a server (application server).

The following diagram provides a high-level architecture of the technologies and components involved in a DRDA Service solution that connects IBM DB2 clients to Microsoft SQL Server databases.

The previous illustration shows the plan for connecting IBM CICS for z/OS client programs, through DB2 for z/OS and the Service for DRDA, to remote Microsoft SQL Server databases.

The Microsoft Service for DRDA (Distributed Relational Database Architecture) is an Application Server (AS) that enables DRDA Application Requester (AR) clients, such as IBM DB2 for z/OS and DB2 for i5/OS, to execute static SQL statements mapped to SQL Server stored procedures. The DRDA Service provides host-initiated data integration essential to enterprises during a phased workload migration, or for daily operations in support of remote batch or business intelligence solutions.

The DRDA Service offers the following features.

• Access from DRDA clients to SQL Server across a TCP/IP network connection.
• Security authentication and data encryption.
• Distributed two-phase commit transactions.
• Static SQL statements mapped to SQL Server stored procedure calls.
• Limited dynamic data manipulation language commands mapped to T-SQL commands.
• Data type mapping and conversion, string encoding, and datetime formatting.
• Error code and text mapping to DRDA reply messages and formatted error objects.
• A trace utility for recording flows and commands to enable troubleshooting of problems.

Install/Configure the Windows side of DRDA

The instructions at Service for DRDA (Operations) – Host Integration Server | Microsoft Docs say you need to start a windows background service to run DRDA on your SQL Server – Service name is msdrdaservice

Then I find this which seems to say that msdrdaservice is an option the SQL Server config:

Installing Master Data Services

You use the SQL Server setup installation wizard or a command prompt to install Master Data Services.

To install Master Data Services using SQL Server Setup on a Windows Server machine

1. Double-click Setup.exe, and follow the steps in the installation wizard.
2. Select Master Data Services on the Feature Selection page under Shared Features.
   
   This installs Master Data Services Configuration Manager, assemblies, a Windows PowerShell snap-in, and folders and files for Web applications and services.

Install/Configure the IBM i side of DRDA

We need to know a few things

• The network address, DNS or TCP/IP Address, of the SQL Server we are going to connect to
• The database, remote SQL Schema, that we are connecting to
• The SQL Server USER and PASSWORD
• Authentication information (is the password encrypted?)

Then we use the WRKRDBDIRE to list our remote databases and press here we can add a link to our new database:

WRKRDBDIRE 

You can add a connection to your SQL Server like this:

ADDRDBDIRE RDB(RMTSCHEMA) RMTLOCNAME('1.2.3.4' IP) RMTAUTMTH(USRENCPWD *ALWLOWER)

During my testing, I ended the *DDM Server and restarted it:

ENDTCPSVR SERVER(*DDM)

STRTCPSVR SERVER(*DDM)

NOTE: It’s DDM on the IBM world and DRDA in Microsoft

Now the connection is defined, we can simply use SQL to connect to the remote database.

You can either start green screen SQL or do it from the IBM i ACS SQL Script editor.

We use the CONNECT command to connect to the remote database we defined:

connect to RMTSCHEMA user USERNAME using 'PASSWORD';

This will use the *DDM Server connection to talk and connect to the DRDA listener on your SQL Server.

Now you can read tables from that database just as if they were local

🙂