DB2 LUW¶
Required Information¶
To configure DB2 in Alation, you will need the following information:
Hostname or IP address of your server
Port number
Database Name
Service account with privileges listed below
Query Log Ingestion setup

Preliminaries¶
Firewall Configuration:
Open outbound TCP port 50000
Create Service Account¶
The DB2 service account is a Linux account. The account is created, authorities need to be granted to the new user.
Sample commands to create the account:
group -g 999 db2iadm1
useradd -u 1004 -g db2iadm1 -m -d /home/alation alation
password alation
Sample SQL to grant authorities to the account:
-- SQL: enable new user to connect to database;
GRANT CONNECT ON DATABASE TO alation;
-- This grants \`alation\` access to all data
GRANT DATAACCESS ON DATABASE TO alation;
Metadata Extraction¶
GRANT SELECT ON SYSCAT.ROLEAUTH TO alation;
GRANT SELECT ON SYSCAT.TABAUTH TO alation;
GRANT SELECT ON SYSCAT.ROLES TO alation;
GRANT SELECT ON SYSCAT.DBAUTH TO alation;
GRANT SELECT ON SYSCAT.SCHEMAAUTH TO alation;
GRANT SELECT ON SYSCAT.COLUMNS TO alation;
GRANT SELECT ON SYSCAT.TABLES TO alation;
GRANT SELECT ON SYSCAT.REFERENCES TO alation;
GRANT SELECT ON SYSCAT.KEYCOLUSE TO alation;
GRANT SELECT ON SYSCAT.INDEXES TO alation;
GRANT SELECT ON SYSIBM.SYSROUTINEPARMS TO alation;
GRANT SELECT ON SYSCAT.ROUTINES TO alation;
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO alation;
GRANT SELECT ON SYSCAT.VIEWS TO alation;
GRANT SELECT ON SYSCAT.SCHEMATA TO alation;
Table Profiling¶
Permissions granted at account creation are sufficient to allow table profiling.
Additional Setup: Enabling Explain in Compose¶
To support the Explain feature for DB2 connections, we need to follow the steps below on your DB2 system. This a one-time process.
Connect to the DB2 instance
CONNECT TO database-name
Run the procedure given below
CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))
Reference: IBM Knowledge Center
Query History¶
CREATE EVENT MONITOR alationQueryLogMonitor FOR STATEMENTS WRITE TO TABLE connheader(
TABLE alationDb2QueryLogger.connection_header INCLUDES(sequence_no, auth_id, appl_id, conn_time)
),
stmt(
TABLE alationDb2QueryLogger.statement INCLUDES(
appl_id,
stmt_operation,
stmt_text,
start_time,
stop_time,
system_cpu_time,
user_cpu_time
)
)
MANUALSTART BUFFERSIZE 512 NONBLOCKED;
SET EVENT MONITOR alationQueryLogMonitor STATE = 1;
GRANT SELECT ON alationDb2QueryLogger.statement TO alation;
GRANT SELECT ON alationDb2QueryLogger.connection_header TO alation;