SSMA for Db2 cannot connect to Db2 running on AIX.

Question

SSMA for Db2 cannot connect to Db2 running on AIX.

Ikuo Takekawa/竹川育夫 0

connection string is:

Provider=DB2OLEDB;Password=[password];Persist Security Info=True;User ID=[UserId];Initial Catalog=[DB Name];Data Source=[DB Name];Authentication=Server_Encrypt_Pwd;Network Address=[Server Name];Network Port=[Port No];Default Schema=[UserID];DBMS Platform=DB2 for LUW;Package Collection=NULLID;Use Early Metadata=false;

Am I missing something or doing something wrong?

Or is there something else besides the connection string that could be causing the problem?

Spec:

SSMA for Db2 v10.4

Install PC Windows 11

DB2/AIX64 11.5.8.0

3 answers

Your answer

Answer 1

It seems like you are experiencing connectivity issues with SSMA for Db2 when trying to connect to a Db2 database running on AIX. Here are a few things to check that might help resolve the issue:

Network Address and Port: Ensure that the Network Address and Network Port specified in your connection string are correct and that the Db2 server is reachable from your Windows machine. You can use the ping command to check connectivity and telnet to verify that the port is open.
Db2 Configuration: Make sure that the Db2 instance on AIX is configured to accept connections on the specified port. You can check the port configuration by running the command db2 get dbm cfg | findstr SVCENAME on the AIX server to confirm the service name and port.
User Permissions: Verify that the user specified in the connection string has the necessary permissions to connect to the database.
Connection String Format: While your connection string looks mostly correct, ensure that the Data Source and Initial Catalog are properly set. Sometimes, the Data Source should be the database alias rather than the database name itself.
Firewall and Security Settings: Check if there are any firewalls or security settings on the AIX server or your local machine that might be blocking the connection.
SSMA Version Compatibility: Ensure that the version of SSMA for Db2 you are using is compatible with the version of Db2 on AIX. You mentioned using SSMA for Db2 v10.4 and Db2/AIX64 11.5.8.0, which should generally be compatible, but it's worth checking the documentation for any known issues.

If you've checked all these aspects and are still facing issues, consider looking into the logs for any specific error messages that might provide more insight into the problem.

References:

Answer 2

Ikuo Takekawa/竹川育夫 0

I understand that there are no problems with points 1 through 5. My question is specifically about point 6.

As additional information, when I press the connect button, the message "Connection to DB2 established successfully" appears, followed by the message "Error occurred while collecting data.

An SQL error has occurred."

Akhil Gajavelly 875 Reputation points Microsoft External Staff Moderator

2025-11-20T05:45:56.5866667+00:00

Hi @Ikuo Takekawa/竹川育夫,

Since SSMA shows “Connection to DB2 established successfully” and then fails with “Error occurred while collecting data. An SQL error has occurred.”, the issue is not the connection string. The failure happens when SSMA tries to read Db2 metadata.

The common causes are:

1.      Missing/incorrect NULLID packages

SSMA requires the Db2 CLI/OLE DB packages. On the Db2 server run:

db2 bind db2cli.lst blocking all grant public collection NULLID

(or use the package collection you specified in the connection).

2.      Insufficient catalog permissions

The SSMA user must be able to read SYSCAT tables. Grant:

GRANT DATAACCESS TO USER <userid>;

(or at minimum SELECT on SYSCAT.TABLES, SYSCAT.COLUMNS, etc.).

3.      DBMS Platform must be “DB2 for LUW”

If SSMA detects z/OS or iSeries, metadata collection fails.

4.      Authentication mismatch

Try switching to:

Authentication=Server

Many AIX Db2 setups do not support Server_Encrypt_Pwd.

Because the connection succeeds but metadata collection fails, the root cause is almost always missing packages (NULLID) or missing catalog permissions.

Thanks,
Akhil.
Ikuo Takekawa/竹川育夫 0 Reputation points

2025-11-20T08:38:23.5433333+00:00

Hi Akhil.

Thank you for your answer.

First, regarding point 4.

At first, I tried "Authentication=Server," but I couldn't authenticate. After some trial and error, I was able to authenticate by changing it to "Authentication=Server_Encrypt_Pwd."

Next, regarding 2.

I have confirmed using another tool that the SYSCAT schema can be referenced using the UserID listed in the connection string.

Regarding point 3.

My DB is "DB2 for LUW" and it is running on AIX.

Finally, number 1.

I found that the error was SQLCODE -805, SQLSTATE 51002. NULLID.MSCS001 is missing. I checked SYSCAT.PACKAGES, and it certainly wasn't there. I don't know how to obtain (or recreate) NULLID.MSCS001.

If you know, I'd appreciate some advice.
Ikuo Takekawa/竹川育夫 0 Reputation points

2025-11-21T08:26:54.75+00:00

It looks like the command you've already given will solve the problem.

db2 bind db2cli.lst blocking all grant public collection NULLID

evidence:

https://www.ibm.com/docs/en/db2/11.1.0?topic=pack-binding-bind-files-after-installing-fix-packs

Answer 3

Ikuo Takekawa/竹川育夫 0

More additional information.

An investigation into the event monitor revealed an SQLCODE -805.It says that the package NULLID.MSCS001 does not exist.I searched SYSCAT.PACKAGES and it certainly wasn't there.

What is NULLID.MSCS001 and how can I get it?

Share via

SSMA for Db2 cannot connect to Db2 running on AIX.

3 answers

Your answer