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.
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'
--columnname as Field_Name,
COUNT(*) AS Num_Occurance,
Object_Details_Tbl.databasename as Database_Schema,
'bhpm1.edc.cingular.net' 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 = 'B' then 'COMBINED AGGREGATE AND ORDERED ANALYTICAL FUNCTION'
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'
end as Object_Type
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;
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 !