Configure Lineage

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

DB2 for iSeries does not include a table that contains the query data for QLI, so one must be created before QLI can run.

Three primary methods are currently available to obtain DB2 queries:

  • Event Monitor (use DB2’s event auditing feature)

  • Plan Cache (pull queries in flight from dump_plan_cache into a table)

  • Third Party Applications (utilize other repositories where queries are available - Alation writes a view/query on top of the datasource and pulls the queries)

Event Monitor

  • Advantage: Existing functionality, customizable, output stored in table

Customers have used the following techniques in the CREATE EVENT MONITOR script to mitigate negative effects:

  • Filter out username(s) such as monitoring/security software, OLTP users, etc.

  • Filter out database and/or schema names which are not being cataloged.

  • Filter out queries that are of no use to Alation (e.g. DCL/TCL statements).

  • Use proactive scheduling and run the audit at certain times and/or capture DDL/DML for a period to establish a “baseline” popularity/top users and augment on a schedule.

  • Schedule a job that will pull the Alation specific queries out of the EVENT MONITOR table and place them in another table for QLI retrieval. The EVENT MONITOR table can then be purged on a regular schedule to keep it from growing too large.

Requires coordination between the business users and DBA as to what can be excluded in the CREATE EVENT script.

The types of DML and DDL queries Alation utilizes:

  • SELECT%FROM%

  • WITH%SELECT%FROM%

  • WITH%SELECT%FROM%

  • DELETE%

  • INSERT%SELECT%

  • UPDATE%FROM%

  • MERGE%

  • WITH%DELETE%

  • WITH%DELETE%

  • WITH%INSERT%SELECT%

  • WITH%INSERT%SELECT%

  • WITH%UPDATE%FROM%

  • WITH%UPDATE%FROM%

  • WITH%MERGE%

  • WITH%MERGE%

  • CREATE%TABLE%

  • CREATE%VIEW%

  • ALTER%TABLE%

  • ALTER%VIEW%

  • DROP%TABLE%

  • DROP%VIEW%

  • TRUNCATE%

  • USE%

The audit can be run for two full weeks or run +/- 5 days of ETL jobs to get a good baseline for top users and popularity. The full audit can then be run every few weeks to pick up new queries and to further build out popularity/top users.

Simple stored procedure can be used that run every x minutes to pull the Alation-specific queries out of the Events Table and place them in another.

Plan Cache

  • Advantage: Lower CPU usage, store only on what is needed.

Requires coordination between the business users and DBA as to what can be excluded in the CREATE EVENT script.

Third Party

  • Advantage: Data usually already present (query, username, etc), don’t have to maintain/set up event monitor/plan cache

Perform QLI

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

  • 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.

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

  • Click the Import button to perform QLI on demand.

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

  • 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.