Query Plan and Data Lineage

The Data Virtuality Server has special procedures for working with query plans and data lineage.

SYSADMIN.getDataLineage

This procedure returns a data lineage plan for the specified SQL query in XML format.

SQL

SYSADMIN.getDataLineage(IN "sql" string NOT NULL) RETURNS (dataLineage xml NOT NULL)

It has the following parameters:

Parameter	Description
`sql`	String representing the SQL query to build the plan for
`dataLineage`	Data lineage plan in XML format

SYSADMIN.getDataLineageWithRelationsOnly

This procedure returns the data lineage for a given query.

SQL

SYSADMIN.getDataLineageWithRelationsOnly(IN "sql" string NOT NULL, IN "level" integer, OUT targetSchema string NOT NULL, OUT targetTableName string NOT NULL, OUT targetColumnName string NOT NULL, OUT sourceSchema string, OUT sourceTableName string, OUT sourceColumnName string)

It has the following parameters:

Parameter	Description
`sql`	String representing the SQL query to find relations
`dataLineage`	Maximum depth until which the lineage is returned. If set to `NULL`, the whole lineage is returned.

Example of usage:

SQL

SELECT * FROM SYSADMIN.getDataLineageWithRelationsOnly('SELECT id FROM views.view_test_datalineage') a;

SYSLOG.getQueryLogPlan

This procedure returns a query plan for the specified entry in the SYSLOG.QueryLogs system table in XML format.

SQL

SYSLOG.getQueryLogPlan(IN logId biginteger NOT NULL, OUT queryPlan string RESULT)

It takes the following parameters:

Parameter	Description
`logId`	ID of the entry in the `SYSLOG.QueryLogs` system table
`queryPlan`	Query plan in XML format

SYSLOG.getQueryLogDataLineage

This procedure returns a data lineage plan for the specified entry in the SYSLOG.QueryLogs system table in XML format.

SQL

SYSLOG.getQueryLogDataLineage(IN logId biginteger NOT NULL, OUT dataLineage string RESULT)

It takes the following parameters:

Parameter	Description
`logId`	ID of the entry in the `SYSLOG.QueryLogs` system table
`dataLineage`	Data lineage plan in XML format