SQL Query output from DBC table volatile

Database

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.

 SEL

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

end

as Object_Type,

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

FROM

(

select databasename, tablename, columnname from

dbc.COLUMNS

WHERE

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'

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

on

Object_Details_Tbl.databasename = Object_Type_Tbl.databasename

and

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;

Tags (1)
2 REPLIES
Enthusiast

Re: SQL Query output from DBC table volatile

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 !

Re: SQL Query output from DBC table volatile

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