All Forums Database
vikramachandran 3 posts Joined 01/13
15 Jan 2013
SQL Query output from DBC table volatile

Hi All -
Newbie to teradata forums. Here is my problem statement/solution and problem with the solution out put.
Problem: The data type of a important surrogate key field is to be changed from INTEGER to DECIMAL(18,0) because of forecasted business growth. This field is used in close to 14000 tables in Teradata Environment and 4000 views and few other objects and the associated ETL processes.
Solutio: Thought will use SQL against DBC to get the list of all the objects that are impacted by this field. SQL attached for your reference. The SQL was run in production environment.
Problem with the solution: However, the same sql is producing different outputs everytime I run. That is when I run first time it produces say 1430 and after a minute 1440 and after another 1487 so on so forth. My theory is due to temporary objects created in production environment this is happening.
How do I get a categorical list of permanent database schema objects - tables/views/macros/indexes so on so forth where the particular field is referenced and/or defined.
Please help.
current_date as Load_Date,
NULL as Tower_Owner,
'Teradata Production' as Environment_Name,
Object_Details_Tbl.tablename as Object_Name,
case when Object_Type_Tbl.Object_Type is null then 'UNKNOWN'
else Object_Type_Tbl.Object_Type
as Object_Type,
--columnname as Field_Name,
COUNT(*) AS Num_Occurance,
Object_Details_Tbl.databasename as Database_Schema,
'' as Server,
'N/A' as Object_Repository,
'Teradata ' ||Object_Type  as Object_Description,
'N/A' as Object_Folder_Name,
'N/A' as Object_Directory_Name,
'A' as Object_Status
select databasename, tablename, columnname from
columnname LIKE '%sXX_aXXX_id%'
) as Object_Details_Tbl
left outer join
select databasename, tablename, tablekind,
case when tablekind = 'A' then 'AGGREGATE UDF'
when tablekind = 'E' then 'EXTERNAL STORED PROCEDURE'
when tablekind = 'F' then 'SCALAR UDF'
when tablekind = 'G' then 'TRIGGER'
when tablekind = 'H' then 'INSTANCE OR CONSTRUCTOR METHOD'
when tablekind = 'I'               then 'JOIN INDEX'
when tablekind = 'J'  then 'JOURNAL'
when tablekind = 'M' then 'MACRO'
when tablekind = 'N' then 'HASH INDEX'
when tablekind = 'P' then 'STORED PROCEDURE'
when tablekind = 'Q' then 'QUEUE TABLE'
when tablekind = 'R' then 'TABLE FUNCTION'
when tablekind = 'S' then 'ORDERED ANALYTICAL FUNCTION'
when tablekind = 'T' then 'TABLE'
when tablekind = 'U' then 'USER-DEFINED DATA TYPE'
when tablekind = 'V' then 'VIEW'
when tablekind = 'X' then 'AUTHORIZATION'
else 'UNKNOWN'
end as Object_Type
from dbc.tables
where tablename in
(select distinct(tablename) from dbc.COLUMNS WHERE
columnname LIKE '%srv_accs_id%')
) as Object_Type_Tbl
Object_Details_Tbl.databasename = Object_Type_Tbl.databasename
Object_Details_Tbl.tablename = Object_Type_Tbl.tablename
--GROUP BY 7,4,5
GROUP BY 1,2,3,4,5,7,8,9,10,11,12,13
ORDER BY 1,2,3,7,5,4;

KS42982 137 posts Joined 12/12
16 Jan 2013

It is very hard to believe that ETL jobs in production keep creating/dropping permanent or global temporary tables. Volatile tables do not get written into data dictionary, so those should not come up in your query results.
I would like to suggest few things.
1) as you know the exact name of the column (surrogate key) then do not use LIKE %..% while reading from dbc.columns but use the exact name to avoid the chances of similar kinds of columns anywhere in the database.
2) if you do not want global temporary tables in your query result then you can filter like -
SEL * FROM dbc.tables WHERE requesttext NOT LIKE '%global temp%'
3) Try to run your query when minimum or none ETL jobs are running.
Good luck !

vikramachandran 3 posts Joined 01/13
16 Jan 2013

Thanks KS42982 for your valuable inputs and time. Will try this approach and get back with my findings.

You must sign in to leave a comment.