Teradata - need retention data for all DBC.TABLES

Analytics

Teradata - need retention data for all DBC.TABLES

Hello,

I am new to SQL/Teradata so please forgive me if this is a dummy question. 

I've been asked to write SQL that will report retention period ( along with number of columns etc) on all DBC.TABLES.

I've put together an SQL to best of my ability to display this information for one table but I need to be able to do it for all the tables.

What is the best way to do this? Thank you in advance

I'm sure SQL I came up with could be better but again, being new to SQL this is the best I could do

SELECT 
DATABASENAME
, TABLENAME
, A.MAX_DT
, A.MIN_DT
, A.RETENTION
, COUNT (*) AS COLUMN_CNT
FROM DBC.COLUMNS
INNER JOIN (
SELECT
'D_XYZ' AS DB
,'TABLE_ABC' AS TBL
, MAX(PROC_DT) AS MAX_DT
, MIN(PROC_DT) AS MIN_DT
, (MAX(PROC_DT) - MIN(PROC_DT) MONTH(3) )AS RETENTION
, (SELECT COUNT(*)
FROM D_XYZ.TABLE_ABC
WHERE PERF_MTHLY_DT > (ADD_MONTHS(LAST_DAY(DATE),-2)) AND PERF_MTHLY_DT < CURRENT_DATE) AS MTH_CNT
FROM D_XYZ.TABLE_ABC
) A
ON (DATABASENAME=DB AND TABLENAME=TBL)
WHERE (TRIM(TABLENAME ) LIKE 'TABLE_ABC' )
AND (DATABASENAME LIKE 'D_%'
OR DATABASENAME LIKE 'T_%' )
GROUP BY 1,2

Result I got for the table I hard-coded was :

DatabaseName    TableName      MAX_DT       MIN_DT    RETENTION    COLUMN_CNT
D_XYZ TABLE_ABC 5/31/2015 1/31/2009 76 293

My goal is to have this info for all DBC.TABLES 

Thank you in advance!

1 REPLY

Re: Teradata - need retention data for all DBC.TABLES

I found some information about BTEQ and  tried to resolve this with BTEQ,  but ran into another issue : I can run bteq in a while loop and get the information I need. But if I have  2000 dbc tables  my bteq will  log into teradata 2000 times. I think this is not an efficient way to get the results. 

while IFS=\| read schema table date_clmn

do 

bteq << eof 

.run file=/dsap/home/dblogon.txt 

.set width 50000

.EXPORT FILE = /dsap/home/bteq_rslt.txt;

SELECT 

    '$schema' AS DATABASENAME

    ,'$table' AS TABLENAME

   , ( SELECT  COUNT(*) 

         FROM DBC.COLUMNS 

         WHERE TRIM(TABLENAME)='$table' AND TRIM(DATABASENAME)='$schema'

         GROUP BY  DATABASENAME, TABLENAME ) AS  COLUMN_CNT 

  ,   MAX($date_clmn) AS MAX_DT

   ,  MIN($date_clmn) AS MIN_DT

   ,  (MAX($date_clmn) - MIN($date_clmn) MONTH(3) )AS RETENTION

 FROM  $schema.$table;

.logoff;

.quit;

eof

done < 1.dat  

I've also tried doing BTEQ with REPEAT but in that case I'm not able to dynamically generate 'FROM  TABLE'. 

bteq << eof 

.run file=/dsap/home/dblogon.txt 

.IMPORT VARTEXT ',' FILE=insert_select.txt;

.QUIET ON 

drop table  schema.my_table;   

CREATE SET TABLE schema.my_table ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      dbnm VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      tblnm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC

      )

PRIMARY INDEX ( dbnm );

.REPEAT * 

USING 

(   dbnm VARCHAR(30)

    ,tblnm VARCHAR(100)

    ,all_nm VARCHAR(130)

)        

INSERT INTO S_C360.FS_INS_SEL_TEST

select 'a', 'b', to_char(count (*)) from :all_nm;

.LOGOFF;

.QUIT

eof

I get the following error with REPEAT

select 'a', 'b', to_char(count (*)) from :all_nm;

                                           $

 *** Failure 3707 Syntax error, expected something like a name or a Unicode 

 delimited identifier or '(' between the 'from' keyword and ':'.

                Statement# 1, Info =166 

 *** Total elapsed time was 1 second.

Repeat runs ok if I use variables in select clause but not when I use them with 'from'.

I'm not sure if I should be able to use :var.:var1 in 'from'  at all. Any help would be appreciated!