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

../../_images/DS_DB2LUW01.png

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.

  1. Connect to the DB2 instance

    CONNECT TO database-name

  1. 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;