Sometimes we want or need to know which database objects are sourced from a specific other database object like a table. In a perfect environment we would simply ask our perfect meta data management tool which knows everything about data lineage. Unfortunately we are sometimes exposed to a non optimal environment where the meta data management is incomplete or does not exist.

I just want to show some possibilities to get at least some info out of the DB system - if you have the appropriate right to run the queries.

Going top down is easy in Teradata. Just place a SHOW in front of any SELECT or EXEC and you get all database object DDLs which are related to this statement. In some cases you can be exposed to the situation that the DDL is incomplete. But a direct show of the DB object in question should overcome this.

Unfortunately this would not overcome the bottom up question "Which database objects like views or macros are accessing the table X?". The problem is that Views can be build up on Views on Views.
The Teradata Administrator has a function to show References of a database object. Problem here is that this is showing only the direct references not all nested ones. At least this is a good starting point. DBQL releases the following SQL statement for the reference lookup:

SELECT DatabaseName,TVMName,TableKind AS "Type"
FROM dbc.TVM T,dbc.dbase D
WHERE D.DatabaseId=T.DatabaseId
 AND CreateText LIKE '%"DBC"."TVM"%' (NOT CS)
UNION
SELECT DatabaseName,TVMName,TableKind AS "Type"
FROM dbc.TextTbl X,dbc.dbase D,dbc.TVM T
WHERE X.TextType='C'
 AND X.TextString LIKE '%"DBC"."TVM"%' (NOT CS)
 AND X.DatabaseId=D.DatabaseId
 AND X.TextId=T.TVMId
MINUS
SELECT DatabaseName,TVMName,TableKind
FROM dbc.TVM T,dbc.dbase D
WHERE D.DatabaseId=T.DatabaseId
 AND DatabaseName='DBC'
 AND TVMName='TVM'
ORDER BY 1,2;

I have to admit I don't understand all of this - like TEXTTYPE = 'C' - but we can use this as a type of black-box logic.
The SQL below does the same for all DB objects in dbc.tables:

CREATE VOLATILE TABLE OBJ_DEPENDENCY 
AS
(
SELECT CAST(TA.DATABASENAME AS VARCHAR(30)) AS SOURCE_DB, 
       CAST(TA.TABLENAME AS VARCHAR(30)) AS SOURCE_OBJ, 
       TA.TABLEKIND AS SOURCE_OBJ_KIND, 
       D.DATABASENAME AS TARGET_DB,
       T.TVMNAME AS TARGET_OBJ,
       T.TABLEKIND AS TARGET_OBJ_KIND 
FROM   DBC.TVM T,
       DBC.DBASE D , 
       DBC.TABLES TA
WHERE D.DATABASEID = T.DATABASEID 
      AND T.CREATETEXT LIKE '%"' !! TRIM (TA.DATABASENAME) !! '"."'!! TRIM (TA.TABLENAME)!! '"%' (NOT CS) 
UNION
SELECT TA.DATABASENAME AS SOURCE_DB, 
       TA.TABLENAME AS SOURCE_OBJ, 
       TA.TABLEKIND AS SOURCE_OBJ_KIND, 
       D.DATABASENAME AS TARGET_DB,
       T.TVMNAME AS TARGET_OBJ,
       T.TABLEKIND AS TARGET_OBJ_KIND
FROM   DBC.TEXTTBL X,
       DBC.DBASE D,
       DBC.TVM T,
       DBC.TABLES TA
WHERE X.TEXTTYPE='C' 
      AND X.TEXTSTRING LIKE '%"' !! TRIM (TA.DATABASENAME) !! '"."'!! TRIM (TA.TABLENAME)!! '"%' (NOT CS) 
      AND X.DATABASEID=D.DATABASEID 
      AND X.TEXTID=T.TVMID 
MINUS
SELECT TA.DATABASENAME AS SOURCE_DB, 
       TA.TABLENAME AS SOURCE_OBJ, 
       TA.TABLEKIND AS SOURCE_OBJ_KIND, 
       D.DATABASENAME AS TARGET_DB,
       T.TVMNAME AS TARGET_OBJ,
       T.TABLEKIND AS TARGET_OBJ_KIND 
FROM   DBC.TVM T,
       DBC.DBASE D,
       DBC.TABLES TA 
WHERE D.DATABASEID=T.DATABASEID 
      AND D.DATABASENAME= TA.DATABASENAME
      AND T.TVMNAME= TA.TABLENAME
) WITH DATA
PRIMARY INDEX (SOURCE_DB,SOURCE_OBJ)
ON COMMIT PRESERVE ROWS;

As this SQL uses product joins to dbc.tables you might see some longer response times in bigger (number of DB objects) production environments. In case you hit serious performance problems use a permanent table, and create a macro which has a databasename as input parameter and run it for one DB at a time.

I found some self references of ETL tool generated DB objects which created some problems in a later analysis. Therefore we should ensure that the table does not contain self referencing records by deleting these.

DELETE 
FROM OBJ_DEPENDENCY
WHERE TRIM (SOURCE_DB) !! TRIM(SOURCE_OBJ) = TRIM (TARGET_DB) !! TRIM(TARGET_OBJ);

The table OBJ_DEPENDENCY contains all objects dependencies which can be derived via this approach from DBC. It is important to run this for the whole system as otherwise the analysis will be incomplete.
Feeding this info into some external program which is able to visualise graphs we can start to get a better understanding of our database objects. The picture below is showing the object dependencies of the R13.10 DBC database (all non DBC objects are excluded here). The different colours are indicating the different object types. Arrows are showing the relation direction. The placement / layout of the DB objects is chosen by the tool and has no meaning. The centre is quite messy and would require a much bigger area to plot to see all the details.

DBC Object Dependencies

The picture itself has some appeal but also shows some valuable insights like basic structures of the DBC components like the ResUsageSpma complex which is shown below in a higher resolution.

ResUsageSpma

Having these kind of visualisation tools available is nice but we can do similar things with SQL in the DB.

The following Recursive SQL is addressing the starting question of showing all dependent objects for a given DB or DB obj.

WITH RECURSIVE DEPENDENT
( SOURCE_DB,
  SOURCE_OBJ,
  SOURCE_OBJ_KIND,
  DEPENDENT_DB,
  DEPENDENT_OBJ,
  DEPENDENT_OBJ_KIND,
  DEPENDENCY_LEVEL 
) 
AS
(
SELECT SOURCE_DB,
       SOURCE_OBJ,
       SOURCE_OBJ_KIND,
       TARGET_DB AS DEPENDENT_DB,
       TARGET_OBJ AS DEPENDENT_OBJ,
       TARGET_OBJ_KIND AS DEPENDENT_OBJ_KIND,
       CAST(1 AS SMALLINT) AS DEPENDENCY_LEVEL  
FROM OBJ_DEPENDENCY 
UNION ALL
SELECT D.SOURCE_DB,
       D.SOURCE_OBJ,
       D.SOURCE_OBJ_KIND,
       O.TARGET_DB AS DEPENDENT_DB, 
       O.TARGET_OBJ AS DEPENDENT_OBJ,
       O.TARGET_OBJ_KIND AS DEPENDENT_OBJ_KIND,
       D.DEPENDENCY_LEVEL + 1 AS DEPENDENCY_LEVEL
FROM OBJ_DEPENDENCY O
     JOIN
     DEPENDENT D
        ON O.SOURCE_DB = D.DEPENDENT_DB
           AND O.SOURCE_OBJ = D.DEPENDENT_OBJ
           AND D.DEPENDENCY_LEVEL <= 100
)
SELECT * 
FROM DEPENDENT
ORDER BY SOURCE_DB,
         SOURCE_OBJ,
         SOURCE_OBJ_KIND,
         DEPENDENCY_LEVEL;

This query returns for all DB objects the dependent objects with the level of dependency. 1 on the dependency_level would indicate that the dependent object accessing the source object directly. 2 would indicate an indirect access via a different object. For example does DBC.ResOneNode access DBC.ResUsageSpma on level 2 via the view DBC.ResGeneralInfoView.
Be aware that a source object can occur more than once in the result set with different dependency levels.


Excel can be a good place to use this data for further analysis.

Analysing this data can give you some interesting insights about your applications and designs. For example estimate the maximum value of your dependency_level before you query your system. If the highest number is higher than expected it might be worth to check the design.
It is not possible to judge the quality of a database design based on only one number but check yourself if you can explain the business logic if you access a database object which is going down to other objects on levels >5 for example.
Be aware that we do not get any information about the type of dependency. If a view is accessing a table we don't know if this is “only” for referential integrity checking, or filtering or if columns are selected in the view.

So have fun to analyse your database object dependencies! 

DISCLAIMER:

As we use here a black-box SQL derived from Teradata Administrator as a starting point we can't be sure that we get the correct and complete results. Neither can we be sure that the analysis will work with the next DB release. Would be nice if Teradata would give us and support a system view with this info in a future release. Maybe some bigger customer wants to request this.

Appendix

Mathematica code

As some asked find attached the code which produced a graph plot of object dependency. GraphPlot is the Mathematica internal function we use – therefore the code is quite limited.

I assume you managed to configure the mathematica JDBC settings and you established a JDBC connection your TD system.
The start would be to download the OBJ_DEPENDENCY content via

dat = SQLExecute[conn,
"select * 
 from obj_dependency 
 where source_db = 'DBC' 
       and target_DB = 'DBC' 
 order by 1,2,4,5"];

To plot graphs relations need to be in a form of A → B. Therefor you create this kind of relations via

dep = Flatten[ 
   Map[{#[[3]] <> " " <> #[[1]] <> "." <> #[[2]] -> #[[6]] <> " " <> #[[4]] <>
         "." <> #[[5]]} &, dat], 1];

Graph[dep] would already the following picture.


This is already nice – rest will be just to make it a bit nicer We need to list of object types in the result set.

objTypes = StringTrim[Union[dat[[All, 3]], dat[[All, 6]]]]

For the color definition we define the following function

getColor[
  word_String,
  words_List,
  colorschema_String
  ] := Module[{number},
  number = N[(Position[words, word][[1]] - 1)/(Length[words] - 1)][[1]];
  ColorData[colorschema][number]
  ]

For the vertex coloring and shap we define the following function

plotVertex[position_List, text_String] := Module[{},
   {
    White,
    Text[
     Framed[
      Style[text,
       12
       ],
      FrameStyle -> Black,
      Background -> getColor[StringTake[text, 1], objTypes, "BrightBands"],
      RoundingRadius -> 5],
     position
     ]
    }
   ];

The final grap plot is than

GraphPlot[
  dep,
  DirectedEdges -> True,
  VertexLabeling -> True ,
  VertexRenderingFunction -> plotVertex,
  PackingMethod -> "ClosestPackingCenter",
  ImageSize -> {6000, 4000}
  ]
 ]
Discussion
rob.heise 2 comments Joined 04/11
03 Dec 2011

This is great information - may I ask what visualization framework you utilized?

jacek.adamowicz 1 comment Joined 04/11
03 Dec 2011

Actually I always wondered why Oracle can have its DBA_DEPENDENCIES view and Teradata can't. Hopefully such view appears one day.

ulrich 51 comments Joined 09/09
05 Dec 2011

I used mathematica to create these pictures.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Bertax 2 comments Joined 12/11
12 Dec 2011

Very good job ulrich thank you. This article starts to address and resolve the dependency issue in Teradata but i'm not able to apply this to Procedures. I saw SHOW CALL..... is not admitted as SHOW EXEC is, so, teradata isn't able to resoleva utomatically dependencies on SP.

The only method i found to achieve this goal is to grab procedure DDL and use an external parser (i use regexp engine) to find references patterns as [database].[dbobject] inside the SP DDL text.
Actually i'm able to extract the same info you define and push into OBJ_DEPENDENCY from outside with BTEQ.

You think there's another way using only teradata to extract dependencies from SP ?

Thank you

ulrich 51 comments Joined 09/09
12 Dec 2011

Procedures - at least the one with dynamic SQL - will be always challenging as the dynamic SQL can access at each execution different DB objects. So no chance to get this. But can't comment why show call procedure is not allowed. I think parsing the DDL might be your only chance for the time being. Maybe someone from TD might explain the background of this restriction?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

carrie 595 comments Joined 04/08
13 Dec 2011

Hi Ulrich,

Good posting!

There are several reasons why SHOW is not supported for a stored procedure.

One reason is that you may execute only a subset of the DML requests for a given execution of a stored procedure, and there is no way to know that until you run it. You could end up with more object detail than you would ever experience if you actually ran the SP. Secondly, the order of the DML may differ from one execution of the SP to another, also not known until execution time, although that may not matter if you were just looking for associations. Thirdly, as you have already noted, if there is dynamic SQL within the SP you will not know some of the database object names until the time of execution.

The SHOW option is intended for and designed for single requests at a time. It was never intended to cover stored procedures. I am not aware of any plans to change that.

Thanks, -Carrie

ratzesberger 3 comments Joined 02/11
15 Dec 2011

Ulrich,

Nice graph. Would love to hear more about how you did it with Mathematica (never used it). The eBay China team has written a DBQL parser that generates XML of all the dataflows in a system. You can select any target table and it shows you where the data came from, including the critical path. They too used an open source visualization library on top of the data.

Thanks for sharing!

Oliver

twitter: @ratzesberger https://twitter.com/ratzesberger

16 Dec 2011

@ratzesberger: Hey! Share that too!!! ; ))
@Ulrich: Share the report of Mathematica too!!! ; ))P

regards and thanks for the post!

regards,

Carlos G. Varela
Freelance - BI Project Manager / Solutions Architect
+34 616307704
carlos@varela.com

ulrich 51 comments Joined 09/09
17 Dec 2011

As some requested I added the mathematica code for creating this kind of graphs as an appendix.
In can become a bit more complicated in case you want some specific subgraphs but mathematica is also providing some more powerful functions to support this.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Willimoa 11 comments Joined 10/09
19 Dec 2011

Thanks Ulrich,
Great Graphs. I am a fan of dynamically generated diagrams from metadata, from dbc or other system metadata sources. I have done something similar for Data Lineage diagrams, but using open source tools, including Python and Graphviz layouts. Users can query for a given database object start point through a web interface and the diagrams are dynamically generated. Graphviz can give you Left to Right or Top to Bottom layouts which are useful for Data Sourcing diagrams.

It has proven very useful, particularly for team members who don't get access to DBC information but still need to know "where did this data come from".

Did you use Graphviz for that Oliver ?

I was trying to add an image but it doesn't seem possible in a reply. Anyone know how ?

ratzesberger 3 comments Joined 02/11
20 Dec 2011

@yottadata: Yes our team used graphviz for that.

twitter: @ratzesberger https://twitter.com/ratzesberger

ulrich 51 comments Joined 09/09
20 Dec 2011

@yottadata: yes graphviz seems to be a tool which can be used to create similar graphs. I would be interested to see the interactive tool.
It was never an intention to vote for a particular tool here. There are more available which might do similar things. I added the graph only to make it more obvious that the information in the database object relationships contains useful information.
I personally use mathematica currently for most of my analytic work as is offering a very wide range of powerful functionality within a single tool which let me spend less time in learning new syntax, move data between different tools etc. and give me more time on the analytic work – but this is a very personal statement. The analytic open source area is quite interesting and powerful and worth to spend time on.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Willimoa 11 comments Joined 10/09
20 Dec 2011

Thanks Ulrich, I agree - the concepts and the capabilities that you are showing are the important things, there are many ways to deliver the benefit.
I'm not particularly pushing graphviz, but the hardest thing I find is getting the right layout for your chosen problem domain, and graphviz had the layouts I was looking for.

david clough 4 comments Joined 12/09
29 Dec 2011

I'd just like to thank you Ulrich for the SQL. Coincidentally I was trying to work out how to do 'dependents' within a View, i.e. what objects does a particular View use.

Going the other way is easy, in fact there is the References option within T Administrator for the very action.

I was considering outputting a series of SHOW VIEW definitions to a file, use a scripting language to 'pull out' the object names within it, and then push that back into Teradata for interrogation. Do-able, indeed not particularly difficult, but didn't really want to have to go down that path.

Using your SQL solution provides that solution - which is what we're going to do - without the need to go 'offbox'.

Oh, by the way, love your fancy graphs.

Happy New Year.
Dave

ulrich 51 comments Joined 09/09
03 Jan 2012

@David: I implemented it this way - generate show select and show exec statements, run them and extract the info out of it. As you state, it is doable but you would also face some issues as
1. not all DDL retured will have a DB in the create / replace
2. not all DDLs are complete
3. you would need to remove all comments from the DDLs you get to be sure that the Create / Replace is the right one.
4. Might run some time in a production environment.

The SQL version has the benefit that it seems much more complete in this respect - contains also UDFs for example.
As mentioned I would hope that this information becomes a standard view / table in DBC in the future.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

pauldancer 4 comments Joined 05/09
03 Jan 2012

I have also done something similar using Graphviz and the Perl interface to it (see CPAN).
First I run a series of stored procedures (but could be a BTEQ) that use SQL to populate a single table of dependencies (can take a while) with the derived "direction" (insert, select, updates delete, collect statistics) of the dependency to illustrate data flow.
I then run the perl program which is has multiple configuration options to pass to GraphViz. It then runs recursive stored procs or macros over the table upstream or downstream or both to a specified recursion depth level to draw the diagram in SVG, PDF or whatever) and produce 2 text files as output (one for the nodes, one for the edges). These files can also be manipulated wiht a text editor to add nodes/edges of your own and then be used as a re-input into the perl program to redraw the diagram (i.e. perhaps you want to illustrate a data flow that doesn't exist yet).
As Ulrich rightly points out, it can never be 100% accurate. e.g. There are some ways of writing SQL that I cannot derive the relationships for with pure SQL, particularly those with "UPDATE alias FROM (....." type statements.
One thing I have discovered is that if you output from GraphiViz as the native format (gv/dot), you can also import into Gephi (An open source Java GUI tool that can do some clever things with grouping nodes, identifying clusters etc). - See http://gephi.org for a download.

JimmyLee 5 comments Joined 06/10
13 Jan 2012

I also use GraphViz.
I have a stored procedure that accepts a database and object name and returns either a record set with all the referencing objects, or optionally, the command lines to feed into Graphviz.
My intent is to get this so we can just call it from a webpage.

Code:
create table dev_eis_t.dbcolors
(databasename varchar(30) not null
,colornm varchar(50) not null)
unique primary index (databasename)
;

CREATE MULTISET GLOBAL TEMPORARY TABLE dev_eis_t.deps ,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
LOG
(
PrntObjDB VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
PrntObjNm VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
PrntObjKind CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
ChldObjDB VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
ChldObjNm VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
ChldObjKind CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL)
PRIMARY INDEX ( PrntObjDB ,PrntObjNm )
ON COMMIT PRESERVE ROWS;

show table dev_eis_t.dep_text_temp

CREATE MULTISET GLOBAL TEMPORARY TABLE dev_eis_t.dep_text_temp ,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
LOG
(
dep_text VARCHAR(600) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
sectionnbr SMALLINT NOT NULL)
UNIQUE PRIMARY INDEX ( dep_text )
ON COMMIT PRESERVE ROWS;

REPLACE PROCEDURE dev_eis_etl.Dep_Analysis
(
IN p_dbname varchar(30),
IN p_objname varchar(30),
In p_output char(1)
)
dynamic result sets 1

BEGIN

-- =============================================================================
-- DBMS Name : Teradata
-- Script Name : update_GeoRptdNetRev_Stg
-- Description : Identify downstream dependencies for database objects (tables, views, join indexes)
-- Output is either tabulate data or the text for a command file to be used with Graphviz.
-- Graphviz is an open source graphing program
-- Use : Call Dev_EIS_ETL.Dep_Analysis(database name, object name, output type)
-- where database name and object name identify the object for which to determine downstream dependencies
-- and Output type select either tabulated data (T) or Graphviz commands (G)
-- Default output is Graphviz
-- Generated by : WhereScape RED Version 6.5.4.0 ( Build 110222 )
-- Generated for : Nike
-- Generated on : Monday, August 01, 2011 at 14:00:22
-- Author : james Lee
-- =============================================================================
-- Notes / History
-- fix join indexes-- fixed
--, remove recursion in seed insert
-- add option to just output data from deps.
-- command line for dot is :
-- dot -Tpdf -o
--=====================================================
-- Declarations
--=====================================================

Declare v_tablekind char(1);

-- Create GraphViz commands
declare cur_Graphviz
cursor with return only for
select dep_text
from dev_eis_t.dep_text_temp
order by sectionnbr, dep_text
;

-- Create Dependcies
declare cur_Dependencies
cursor with return only for
select *
from deps
;

--=====================================================
-- MainLine
--=====================================================

delete from deps;
delete from dep_text_temp;

--Determine the type of object passed in
select tablekind
into v_tablekind
from dbc.tables
where databasename = p_dbname
and tablename = p_objname
;

-- Create seed records
Insert into Deps
select p_dbname, p_objname, v_tablekind, db.DatabaseName,t.TVMName,t.TableKind
from DBC.tvm T, DBC.dbase db
where db.databaseid = t.databaseid
and position('"'||p_dbname||'"."'||p_objname||'"' in t.createtext) > 0
and
(
db.databasename <> p_dbname
or
t.TVMname <> p_objname
)
union all
SELECT p_dbname, p_objname, v_tablekind, db.DatabaseName,t.TVMName,t.TableKind
FROM DBC.tvm t,
DBC.texttbl tt,
DBC.dbase db
WHERE t.DatabaseId = db.DatabaseId
AND t.TVMId = tt.TextID
AND Tt.TextType = 'C'
and position('"'||p_dbname||'"."'||p_objname||'"' in tt.textstring) > 0
and
(
db.databasename <> p_dbname
or
t.TVMname <> p_objname
)
;

-- Loop through to find all dependencies until we don't find anymore

L1:
Loop
Insert into Deps
select d.chldobjdb, d.chldobjnm, d.chldobjkind, db.DatabaseName,t.TVMName,t.TableKind
from DBC.tvm T, DBC.dbase Db , deps d
where db.databaseid = t.databaseid
and position(d.chldobjdb||'"."'||d.chldobjnm||'"' in t.createtext) > 0
and (d.chldobjdb, d.chldobjnm) not in
(select prntobjdb, prntobjnm from deps)
and
(
d.chldobjdb <> db.databasename
or
d.chldobjnm <> t.TVMname
)
union all
SELECT d.chldobjdb, d.chldobjnm, d.chldobjkind, db.DatabaseName,t.TVMName,t.TableKind
FROM DBC.tvm t,
DBC.texttbl tt,
DBC.dbase db,
deps d
WHERE t.DatabaseId = db.DatabaseId
AND t.TVMId = tt.TextID
AND tt.TextType = 'C'
and position(d.chldobjdb||'"."'||d.chldobjnm||'"' in tt.textstring) > 0
and (d.chldobjdb, d.chldobjnm) not in
(select prntobjdb, prntobjnm from deps)
and
(
d.chldobjdb <> db.databasename
or
d.chldobjnm <> t.TVMname
)
;
if ACTIVITY_COUNT = 0 then
Leave L1;
end if;

End Loop L1;

-- If Output mode is Grpahviz (G), then create the commands
if coalesce(p_output,'G') = 'G' then

-- populate the output text table
insert into dep_text_temp
select 'Digraph '||upper(trim(p_dbname)) ||'_'|| lower(trim(p_objname))||'_dep {' , 1
;
insert into dep_text_temp
select 'rankdir=LR;' , 2
;
insert into dep_text_temp
select '"'|| lower(trim(objdb) ||'_'|| trim(objnm) ||'" ['||shape||', fillcolor='||fillcolor||', label="')||upper(trim(objdb))||'.'||lower(trim(objnm))||'"]' as db_object, 3
from
(select prntobjdb as objdb
, prntobjnm as objnm
, prntobjkind as objkind
,coalesce( c.colornm, 'white') as fillcolor
, case when prntobjkind = 'I' then 'shape=polygon, sides=4, skew=.4,style=filled'
when prntobjkind = 'M' then 'shape=invtriangle, style=filled'
when prntobjkind = 'T' then 'shape=box, style="solid,filled"'
when prntobjkind = 'V' then 'shape=box, style="dashed,filled",peripheries=2'
end as shape
from deps d
left outer join dbcolors c
on d.prntobjdb = c.databasename
)parents
union
select '"'|| lower(trim(objdb) ||'_'|| trim(objnm) ||'" ['||shape||', fillcolor='||fillcolor||', label="')||upper(trim(objdb))||'.'||lower(trim(objnm))||'"]' as db_object, 3
from
(select chldobjdb as objdb
, chldobjnm as objnm
, chldobjkind as objkind
,coalesce( c.colornm, 'white') as fillcolor
, case when chldobjkind = 'I' then 'shape=polygon, sides=4, skew=.4,style=filled'
when chldobjkind = 'M' then 'shape=invtriangle, style=filled'
when chldobjkind = 'T' then 'shape=box, style="solid,filled"'
when chldobjkind = 'V' then 'shape=box, style="dashed,filled",peripheries=2'
end as shape
from deps d
left outer join dbcolors c
on d.chldobjdb = c.databasename
)parents
;

insert into dep_text_temp
select distinct '"'|| lower(trim(prntobjdb) ||'_'|| trim(prntobjnm) ||'"->"'||trim(chldobjdb) ||'_'|| trim(chldobjnm) ||'";') as dependency, 4
from deps
;

insert into dep_text_temp
select '}', 5
;

open cur_Graphviz;

-- If output is T, just dump the dep table
elseif p_output = 'T' then
open cur_Dependencies;

-- Otherwise spit out an error message.
else
insert into dep_text_temp
select 'Invalid output mode specified. Mode must be G (Graphviz Commands) or T (Tabulated data).', 1
;
open cur_Graphviz;
end if;

END;

"To not give your best is to sacrifice the gift" -- Steve Prefontaine

12 Feb 2013

Hi JimmyLee,
thank you very much for your stored.

I made some little changes in the script due to the very big metadata table (dbc.TVM, etc.) I have to manage and also to pick up also other dependencies written in different way.
In details:

1 - In -- Create seed records
I add the following OR condition:

and
(
position('"'||p_dbname||'"."'||p_objname||'"' in t.createtext) > 0
OR position(p_dbname||'.'||p_objname in t.createtext) > 0
)
to pick up also other dependencies written in different way (without double quotes)

2 - In -- Loop through to find all dependencies until we don't find anymore
In the second part of UNION I change the join conditions btw tables in order to force teradata join TVM to DBASE berofe texttbl. So I change:

FROM DBC.tvm t,
DBC.texttbl tt,
DBC.dbase db,
deps d
WHERE t.DatabaseId = db.DatabaseId
AND t.TVMId = tt.TextID
AND tt.TextType = 'C'

with
FROM
(
sel distinct
t.TVMName
,db.DatabaseName
,t.TableKind

, t.DatabaseId
,t.TVMId
from DBC.tvm t
,DBC.dbase db
WHERE t.DatabaseId = db.DatabaseId
)t,
DBC.texttbl tt,
deps d
WHERE 1=1
AND t.TVMId = tt.TextID
AND tt.TextType = 'C'

May I ask you if you have a stored or other ways to finds the dependencies in the opposite way ?
That's say:
give one object (dbname.objectname) finds all objects used by it.

I know that the show command returns all these informations (for example if I have a view dbname.viewname, the command show select * from dbname.viewname returns all the DDL of the object used by dbname.viewname till the DDL tables). So I can build a parser to get all the objects referenced
But I'd like to do this without using the client's environment (for example export the result of show command in client's file system and apply a parser).
Can you help me ?

thank you very much and regards.

Pietro Nardella
pietro.nardella@teradata.com
Teradata Italia

eddy.balan 1 comment Joined 08/10
17 Apr 2013

Hi Guys,
I'm also looking into finding a way to get also the SP dependecies. Unfortunately there is no way to do it using the DBC objects.
Does anybody have an external parser to deal with the SHOW PROCEDURE return text.?
 Thanks,
Eddy Balan
Teradata Sweden

ulrich 51 comments Joined 09/09
23 Apr 2013

Hi eddy,
no nothing I am aware of. In any case it would work only if you are not dealing with dynamic SQL.
Did you consider to run them with DBQL object logging and queryband on? This should give you all Objects used in this run...
Ulrich

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

darcyzam 1 comment Joined 06/11
02 Oct 2013

Thanks Ulrich! This is awesome. It helps me a lot in reverse engineering the relationships in a table level for a specific databases in production  between to create data models in ERwin.  Rather than doing it manually by checking the references of each and every view via TD Administrator and going  through the pain of checking each and every table dependencies esp. if it is a nested view. At least now, I can focus on checking the join access path on a table level as basis for the relationships in the data model.  Again, Thanks and more power!
 
Regards,
Darcy

 

newton1965 1 comment Joined 06/13
01 Feb 2016

ulrich,
This is great information, so thanks for posting.
What if a person needs to perform bottom up analysis at a more granular level like by column name used within an object?  Using the DBC.TVM.CREATETEXT is the right source to use when the DDL is small.  However, when the DDL is large, Teradata uses the DBC.TEXTTBL.TEXTSTRING column which seems to be a LOB.  How would you go about searching within each LOB  (TextString###.txt) for the column that is being used?
Thanks

ulrich 51 comments Joined 09/09
01 Feb 2016

not sure that I understand your requirement correctly. For views and tables you can get the column information from dbc.columnsV. But I guess this is not what you are looking for...
If you want complex views / macros to come up with data lineage / mapping informatin I guess you need to invest in an SQL parser - I won't recommend to program this on your own. Derived tables with reusage of column names are not easy to deal with....

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

alirana78 3 comments Joined 03/16
07 Mar 2016

Ulrich this is a great article and i have used part of your code to generate a heat map on the table as well as semantic view level based on analytic users. is there a way we can develope a metadata lineage between attributes of each object. this code resolves parent child relationship between objects but is there a way to determine the lineage of attribute from scemantic layer down to base tables. is there any table or view in DBC which maintains this information and how does the optimizer resolve these dependencies down to base tables.

ali rana

alirana78 3 comments Joined 03/16
07 Mar 2016

Ulrich this is a great article and i have used part of your code to generate a heat map on the table as well as semantic view level based on analytic users. is there a way we can develope a metadata lineage between attributes of each object. this code resolves parent child relationship between objects but is there a way to determine the lineage of attribute from scemantic layer down to base tables. is there any table or view in DBC which maintains this information and how does the optimizer resolve these dependencies down to base tables.

ali rana

ulrich 51 comments Joined 09/09
08 Mar 2016

Hi Ali,
I guess it will become a challange to do what you want based on DBC infos. You might check the infos in the DBQL object view. But this requires that DQBL object logging is on and that you run the statements. But even then the infos are limited and in case of nested views I doubt that you get what you want.
You need to parse the DDL SQL itself to retrieve the data lineage infos. This is something you don't want to invent yourself if writing SQL parser is not your core business ;-).

There are different tools available which can support you there.
Just two examples:
https://mantatools.com/manta-flow/
https://sqldep.com/
For sure they need to make some money to support you. And I am pretty sure more tools exists.
Ulrich

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

alirana78 3 comments Joined 03/16
08 Mar 2016

Thank you for the answers and the reference links. i am going to be checking on the Teradata MDS suite to see if we can obtain that with the install free of charge so that this type of analysis can be performed.

ali rana

You must sign in to leave a comment.