Archives of the TeradataForum

Message Posted: Wed, 23 Mar 2005 @ 20:49:48 GMT

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

Here is a query that several people gave me versions. If I remember right it is V2R5 compatible.

     SELECT
     'collect stats on ' || trim(DatabaseName) || '.' || TableName || case
     when statstype = 'I' then ' Index ( ' else ' column ( ' end  ||
     ColumnName || ');',
     StatsType,
     (
     (HASHBUCKET
     (SUBSTRING(StatsData FROM 2 FOR 1) ||
     SUBSTRING(StatsData FROM 1 FOR 1) (BYTE(4))
     ) - 1900
     ) * 10000
     +
     (HASHBUCKET
     ('00'xb || SUBSTRING(StatsData FROM 3 FOR 1) (BYTE(4))
     )
     ) * 100
     +
     (HASHBUCKET
     (
     '00'xb || SUBSTRING(StatsData FROM 4 FOR 1) (BYTE(4))
     )
     )
     ) (DATE) AS CollectDate,
     (CAST(
     (HASHBUCKET
     (CAST('00'xb || SUBSTRING(StatsData FROM 5 FOR 1) AS BYTE(4))
     ) (FORMAT '99:')
     ) ||
     (HASHBUCKET
     (CAST('00'xb || SUBSTRING(StatsData FROM 6 FOR 1) AS BYTE(4))
     ) (FORMAT '99:')
     ) ||
     (HASHBUCKET
     (CAST('00'xb || SUBSTRING(StatsData FROM 7 FOR 1) AS BYTE(4))
     ) (FORMAT '99')
     )
     AS TIME(0))) AS CollectTime
     FROM
     (
     SELECT
     d.Databaseid as DATABASE_ID,
     t.tvmid as TVM_ID,
     d.databasename AS DatabaseName,
     t.tvmname AS TableName,
     MAX(CASE WHEN i.FieldPosition = 1 THEN TRIM(c.FieldName) ELSE ''
     END) ||
     MAX(CASE WHEN i.FieldPosition = 2 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 3 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 4 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 5 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 6 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 7 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 8 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 9 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 10 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 11 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 12 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 13 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 14 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 15 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition = 16 THEN ', ' || TRIM(c.FieldName)
     ELSE '' END) ||
     MAX(CASE WHEN i.FieldPosition > 16 THEN ',...' ELSE '' END) AS
     ColumnName,
     CASE WHEN IndexType = 'M' THEN 'M' ELSE 'I' END AS StatsType,
     MAX(SUBSTRING(i.IndexStatistics FROM 1 FOR 7)) AS StatsData
     FROM
     dbc.dbase d
     JOIN dbc.tvm t
     ON d.databaseid = t.databaseid
     JOIN dbc.tvfields c
     ON t.tvmid = c.tableid
     JOIN dbc.Indexes i
     ON c.tableid = i.tableid
     AND c.fieldid = i.fieldid
     GROUP BY
     Database_id,
     TVM_ID,
     DatabaseName,
     TableName,
     StatsType,
     i.IndexNumber
     UNION ALL
     SELECT
     d.Databaseid as DATABASE_ID,
     t.tvmid as TVM_ID,
     d.databasename AS DatabaseName,
     t.tvmname AS TableName,
     c.fieldname AS ColumnName,
     'C' AS StatsType,
     SUBSTRING(c.fieldstatistics FROM 1 FOR 7) AS StatsData
     FROM
     dbc.dbase d
     JOIN dbc.tvm t
     ON d.databaseid = t.databaseid
     JOIN dbc.tvfields c
     ON t.tvmid = c.tableid
     ) dt
     WHERE
     StatsData IS NOT NULL

     order by databasename, tablename
     ;

Thanks,

Bob Diehl
Travelcoity.com

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