Skip to main content
This guide explains how to connect Coalesce Quality to IBM Db2 securely.We need this information so we can collect metadata about your tables.To be able to finish this guide, you’ll need the following:
→ Access to grant privileges on your Db2 database (SECADM, DBADM or ACCESSCTRL)
→ The IBM Db2 integration enabled for your workspace. If IBM Db2 is not in the list of integrations, ask support to enable it.⏱️ Estimated time to finish: 10 minutes.
The integration supports Db2 for Linux, UNIX and Windows (Db2 LUW). Db2 for z/OS and Db2 for i keep their catalogs elsewhere and are not supported.

​
Data we collect

For the automated data anomaly testing we collect the following:
  • Number of rows in every table, from the catalog statistics (CARD)
  • Size of tables in bytes, from the statistics’ page count (NPAGES)
  • Timestamp of the last statistics collection (STATS_TIME)
To provide out-of-the-box monitors for data volume and freshness, we don’t require access to your actual data. These numbers are the statistics RUNSTATS (or automatic statistics collection) leaves in the catalog. A table whose statistics were never collected has no row count or size, and Db2 keeps no time of the last data change, so freshness follows the last statistics collection. Keep automatic statistics collection on (AUTO_RUNSTATS, on by default), or run RUNSTATS on the tables you monitor. We access metadata from the following catalog views: For Custom SQL monitors, we need SELECT access to your actual data tables.
A Db2 instance holds several databases, each with its own catalog, and a connection reaches only the database it connects to. If you need to monitor multiple databases, create a separate integration for each.

​
Create a dedicated user

Db2 has no users of its own: it checks the authorization ID and password against the server’s operating system, or LDAP when the instance uses an LDAP plugin. Create the user there (for example synq), then connect to the database as an administrator and grant it access.

​
Step 1: Grant metadata access (required)

PUBLIC can read the SYSCAT views by default, so this is all the catalog and the volume and freshness monitors need. If the database was created RESTRICTIVE, PUBLIC has no access to the catalog, and the user needs it granted:

​
Step 2: Enable Query Logs (optional)

Coalesce Quality reads query logs from the package cache through the MON_GET_PKG_CACHE_STMT table function, which needs EXECUTE on it:
The package cache is held in memory: it keeps one entry per statement with its metrics summed over every run, and statements drop out of it as it fills. It does not record who ran a statement, so the query logs carry no user.
Enable Fetch query logs in the Coalesce Quality integration settings to start collecting query history.

​
Step 3: List the users (optional)

With Fetch users and service accounts turned on (it shows once query logs are on), Coalesce Quality lists the authorization IDs the database knows from its grants, once a day. It reads SYSIBMADM.AUTHORIZATIONIDS and SYSCAT.ROLEAUTH, which PUBLIC can read unless the database was created RESTRICTIVE; then grant:
A user who connects only through a grant to PUBLIC or to an operating system group has no grant of their own, so they are not listed.

​
Step 4: Grant access for Custom SQL monitors (optional)

If you want Coalesce Quality to run Custom SQL monitors that query your actual data, grant SELECT on the tables:

​
Network Configuration

If your Db2 server is behind a firewall or has network access controls, you may need to whitelist Coalesce Quality’s IP addresses. See IP Whitelist for the complete list of IP addresses by region.

​
Input data in the UI

​
Integration name

E.g., db2-production

​
Database

The database name or alias, as in CONNECT TO <database>

​
Host name and port

The host of the Db2 server and the port of the instance (SVCENAME, or SSL_SVCENAME with SSL). The default is 50000, or 50001 with SSL.

​
Authorization ID and password

The user you created above.

​
Authentication

How the authorization ID and password travel to the server, as in the AUTHENTICATION setting. SERVER (the default) sends them unencrypted, so turn on SSL if the network isn’t trusted. SERVER_ENCRYPT encrypts them, and the instance has to accept encrypted credentials.

​
Connect over SSL

Connects with SECURITY=SSL. The instance needs SSL_SVCENAME set and SSL listed in DB2COMM.

​
Server certificate (optional)

The server’s certificate, or the certificate of the CA that signed it, in PEM format. The .arm file from gsk8capicmd_64 -cert -extract works as is. Leave empty if the certificate is signed by a public CA.

​
Audit table name (optional)

Table for SQL test audit logs, e.g. QUALITY.SQL_TEST_AUDIT. Coalesce Quality creates it if it does not exist. Leave empty if you don’t run SQL tests.