How to connect DB2 to SQL Server Migration Assistant (SSMA) for DB2

Robin Glass 0 Reputation points
2024-09-07T23:05:45.7766667+00:00

I have been unable to fully connect to DB2 using Microsoft's SSMA for DB2 client (DB2ToSQL).

After making sure all pre-requisites were in place and following the connection instructions, SSMA just shows it's "connecting", however, netstat shows it connected.

Using remote access app, Splashtop to a Windows 11 desktop I can successfully download individual DB2 AS400 database files using IBM i Access Client Solutions. See attached image.

Shouldn't I be using the same connection parameters used by IBM i Access Client Solutions to establish an SSMA connection to the DB2 database? I used the same server name (ip address), server port (8471 or 8475), DB2 SID-Initial catalog-database name.

When I submit the connection parameters, SSMA responds with "connecting", not for just a few seconds but for as long as you wait -- hours. See screenshot.

All pre-requisites for SSMAforDB2_9.5.0 were checked-complete:

Windows 11

Windows ® Installer. V 5.0.22621.3880

dotnet 8.0.401

  1. DB2OLEDBV6_x64
  2. Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 22631: )
  3. Access to and sufficient permissions on the computer that hosts the target instance of SQL Server -- done
  4. 7.8 GB memory
  5. The DB2 database has to be 7.0 or higher. According to SQL queries the DB2 is version 7.0 release 5

SELECT RELEASE_LEVEL, TEXT_DESCRIPTION

FROM QSYS2.SOFTWARE_PRODUCT_INFO

WHERE PRODUCT_ID = '5770SS1'

AND LOAD_TYPE = 'CODE' AND PRODUCT_OPTION = '*BASE'

Release level: V7R5M0 Text description: IBM i

SELECT os_name, os_version, os_release

FROM SYSIBMADM.ENV_SYS_INFO;

OS_NAME:IBM i OS_VERSION: 7 OS_RELEASE: 5

I found the name of the database with the SQL query and its shown in

VALUES CURRENT SERVER

Results: XXXXXXXX (X'd out)

See attached image

I submitted the connection parameters - Connect To DB2 (DB2ToSQL)

https://learn.microsoft.com/en-us/sql/ssma/db2/connect-to-db2-db2tosql?view=sql-server-ver16

  1. Selected: Standard mode
  2. Entered values for the provider (Mode manager) DB2 for i
  3. server name (IP address)
  4. server port
  5. DB2 SID (initial catalog - database name)
  6. user name
  7. password

See attached

connect_attempt

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
540 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.