Configure Lineage

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Enhanced Connector Enhanced connectors add extended capabilities and require a separate entitlement in addition to your Alation platform license.

The IBM i DB2 Enhanced connector supports table-level lineage through Query Log Ingestion (QLI). Column-level lineage is not currently supported.

DB2 for IBM i does not include a built-in table that contains query data for QLI, so database monitoring must be enabled and a query history table must be created before QLI can run.

Prerequisites

Database monitoring must be enabled on IBM i to capture SQL query history. Run this command on the IBM i command line as system administrator:

STRDBMON OUTFILE(QGPL/SQLAUDIT) OUTMBR(*FIRST *REPLACE) JOB(*ALL) TYPE(*BASIC)

You will see a response like:

Database monitor started for job *, monitor ID 2036122002.

Make note of your monitor job ID for use when stopping monitoring later.

This creates the QGPL/SQLAUDIT table for QLI.

Note

Database monitoring can impact system performance and will eventually consume storage. To stop monitoring, return to the IBM i command line, paste the following command, and press Enter (replace the MONID value with your monitor job ID noted above):

ENDDBMON JOB(*ALL) MONID(2036122002)

Configure QLI in Alation

QLI must be configured on the Query Log Ingestion tab of the data source Settings page using the custom query-based method. The table-based QLI option does not work for this connector.

Custom Query-Based QLI (Required)

You must use custom query-based QLI for IBM i DB2. Use this template in the Custom QLI Query field:

WITH Q (username, starttime, queryString, defaultDatabases, sessionId, sessionStartTime, cancelled, seconds)
AS (SELECT QQUSER, QQSTIM, QQ1000, QVQLIB, QQRID, QQSTIM, '', QQI4 / 1000 FROM QGPL.SQLAUDIT)
SELECT Q.* FROM Q
WHERE Q.starttime > STARTTIME AND Q.starttime < ENDTIME;

Perform QLI

You can either perform QLI manually on demand or enable automated QLI:

  1. To perform manual QLI, under the Automated and Manual Query Log Ingestion section of the Query Log Ingestion tab, ensure that the Enable Automated Query Log Ingestion toggle is disabled.

    Note

    Metadata extraction must be completed first before running QLI.

  2. Click Preview to get a sample of the query history data to be ingested.

  3. Click the Import button to perform QLI on demand.

  4. To schedule QLI, enable the Enable Automated Query Log Ingestion toggle.

  5. Set a schedule under Automated Query Log Ingestion Time by specifying values in the week, day, and time fields. The next QLI job will run on the schedule you have specified.

    Note

    The hourly schedule for automated QLI is not supported.