Archives of the TeradataForum

Message Posted: Tue, 02 Dec 2003 @ 13:02:47 GMT

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

Hope this BTEQ script can help.

This script generate the COLLECT STAT command for columns with old statistics :

.export file='c:\travail\Stats_DEV.txt',CLOSE
--------------------------------------------------------------------
-- generate collect stat command for :
--
--       . non index column
--       . index with one column
--       . qualified index
--------------------------------------------------------------------
SELECT 'COLLECT STATISTICS ON =
'||TRIM(dbase.DatabaseName)||'.'||TRIM(tvm.TVMName)||
       (CASE WHEN TD.databasename IS NULL
             THEN ' COLUMN '||TRIM(tvfields.FieldName)||';'
             WHEN td.indexname is null
             THEN ' INDEX ('||TRIM(tvfields.FieldName)||');'
             ELSE ' INDEX '||TRIM(td.indexname)||';'
        END) (TITLE '')

FROM   DBC.Dbase

INNER JOIN DBC.TVM ON tvm.DatabaseId = dbase.DatabaseId

INNER JOIN DBC.tvfields ON tvm.tvmid = tvfields.tableid

LEFT JOIN (SELECT =
databasename,tablename,IndexNumber,indexname,MIN(Columnname) AS COL
           FROM    DBC.Indices
           GROUP   BY 1,2,3,4
           HAVING MIN(Columnname) = MAX(Columnname) OR IndexName IS NOT NULL) AS TD
ON TD.databasename = dbase.DatabaseName AND
   TD.tablename = TVM.tvmname AND
   TD.COL = tvfields.FieldName

WHERE  tvfields.FieldStatistics IS NOT NULL
  AND (Dbase.OwnerName <> 'desire_databasename'

ORDER BY 1
;
.FOLDLINE ON 1,2
.SEPARATOR 0
--------------------------------------------------------------------
-- generate collect stat command for :
--         non quaified index and multiple column index
--------------------------------------------------------------------

SELECT 'COLLECT STATISTICS ON =
'||TRIM(TD.DatabaseName)||'.'||TRIM(TD.tableName) (TITLE ''),
       '       INDEX('||
       TRIM(TD.col1)||TRIM(TD.col2)||TRIM(TD.col3) (TITLE ''),
       '      =

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