Archives of the TeradataForum

Message Posted: Wed, 13 Oct 2004 @ 11:41:17 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Greg

We use the following to detect tables (on specified databases - mydatabase in example below) with existing stats so that we can refresh them. The output from this SQL is used as the input to a second job.

Howard

     SEL
     'COLLECT STATISTICS '||
     TRIM (BOTH FROM DATABASENAME)||'.'||
     TRIM (BOTH FROM TVMNAME)||';' (CHAR(80))
     FROM DBC.INDEXES T1
     ,DBC.TVM T2
     ,DBC.DBASE T3
     WHERE
     T1.TABLEID=T2.TVMID
     AND
     T1.DATABASEID=T3.DATABASEID
     AND
     INDEXSTATISTICS IS NOT NULL
     AND
     DATABASENAME  IN
     ('mydatabase')
     GROUP BY 1
     UNION
     SEL
     'COLLECT STATISTICS '||
     TRIM (BOTH FROM DATABASENAME)||'.'||
     TRIM (BOTH FROM TVMNAME)||';' (CHAR(80))
     FROM DBC.TVFIELDS T1
     ,DBC.TVM T2
     ,DBC.DBASE T3
     WHERE
     T1.TABLEID=T2.TVMID
     AND
     T1.DATABASEID=T3.DATABASEID
     AND
     DATABASENAME IN
     ('mydatabase')
     AND
     FIELDSTATISTICS IS NOT NULL
     GROUP BY 1
     ORDER BY 1;

	<Prev	Next>		<<First	<Prev	Next>	Last>>