Home Page for the TeradataForum
 

 

Library: White Papers


 

MACRO gen_db_collstats

Written by Donald Stubbs    (NCR)


The following macro takes all the stats from one database and generates the collect stats statements for another database.

This macro was taken from the thread: Statistics expert needed


MACRO GEN_DB_COLLSTATS


     REPLACE MACRO  gen_db_collstats
        ( ToDBName       (varchar(30), default ' '),
          FromDBName     (varchar(30), default ' ')
        )
     AS (


       /*  much thanks to Dieter Noth  */


     select trim(chartext) (title '')
       from (

     select  'collect statistics on '
                 || trim(:ToDBName) || '.' || trim(tablename)
                 || case when statstype = 'i' then ' index ('
                         else                      ' column ('
                    end
                 || trim(columnname) || ');'  (named chartext, char(2000))
       from (


       /*  much thanks to Dieter Noth  */


      SELECT DatabaseName      as databasename,
             TableName         as tablename,
             ColumnName        as columnname,

               /** Number of columns within MultiColumn or Index stats **/
             ColumnCount,

               /** stats collected on:
                  'C' --> Column
                  'I' --> Index
                  'M' --> Multiple columns (V2R5)
               **/
             StatsType         as statstype,

               /** collect stats date **/
             CollectDate       as collectdate,

               /** collect stats time **/
             CollectTime       as collecttime,

             CollectTimestamp  as collecttimestamp,

               /** Time needed to collect stats **/
               /*  CollectDuration,*/

               /** Version
                   1: pre-V2R5
                   2: V2R5
               **/
             StatsVersion      as statsversion,

               /** V2R5: sample size used for collect stats**/
             SampleSize        as samplesize,

               /** Row Count, Estimated when SampleSize < 100 **/
             NumRows           as numrows,

               /** Distinct Values, Estimated when SampleSize < 100 **/
             NumValues         as numvalues,

               /** Number of NULLs, Estimated when SampleSize < 100 **/
             NumNulls          as numnulls,

               /** Frequency of Mode / value, Estimated when SampleSize < 100 **/
             ModeFreq          as modefreq

        FROM
            (
             SELECT DatabaseName   as databasename,
                    TableName      as tablename,
                    ColumnName     as columnname,
                    ColumnCount    as columncount,
                    Stats          as stats,
                    StatsType      as statstype,

                    (
                      (HASHBUCKET
                        (SUBSTR(Stats, 2, 1) ||
                         SUBSTR(Stats, 1, 1) (BYTE(4))
                        )  - 1900
                      ) * 10000
                      +
                      (HASHBUCKET
                        ('00'xb || SUBSTR(Stats, 3, 1) (BYTE(4))
                        )
                      ) * 100
                      +
                      (HASHBUCKET
                        (
                         '00'xb || SUBSTR(Stats, 4, 1) (BYTE(4))
                        )
                      ) (DATE)
                    ) AS CollectDate,

                    (
                      (HASHBUCKET
                        (CAST('00'xb || SUBSTR(Stats, 5, 1) AS BYTE(4))
                        ) (FORMAT '99:')
                      ) ||
                      (HASHBUCKET
                        (CAST('00'xb || SUBSTR(Stats, 6, 1) AS BYTE(4))
                        ) (FORMAT '99:')
                      ) ||
                      (HASHBUCKET
                        (CAST('00'xb || SUBSTR(Stats, 7, 1) AS BYTE(4))
                        ) (FORMAT '99.')
                      ) ||
                      (HASHBUCKET
                        (CAST('00'xb || SUBSTR(Stats, 8, 1) AS BYTE(4))
                        ) (FORMAT '99')
                      ) (TIME(2))
                    ) AS CollectTime,

                    CAST(CollectDate AS TimeStamp(2))
                      + ((CollectTime - time '00:00:00') hour to second)
                    AS CollectTimestamp,

                    HASHBUCKET (SUBSTR(Stats, 9+1, 1)
                             || SUBSTR(Stats, 9+0, 1) (BYTE(4)))
                    AS StatsVersion,

                      /** V2R5: sample size used for collect stats**/
                    CASE WHEN HASHBUCKET ('00'xb || SUBSTR(Stats, 11, 1) (BYTE(4))) = 1
                         THEN HASHBUCKET ('00'xb || SUBSTR(Stats, 12, 1) (BYTE(4)))
                         ELSE 100
                    END AS SampleSize,

                    HASHBUCKET(SUBSTR(Stats, 13+7, 1)
                            || SUBSTR(Stats, 13+6, 1) (BYTE(4))) AS NumNullsw1,
                    HASHBUCKET(SUBSTR(Stats, 13+5, 1)
                            || SUBSTR(Stats, 13+4, 1) (BYTE(4))) AS NumNullsw2,
                    HASHBUCKET(SUBSTR(Stats, 13+3, 1)
                            || SUBSTR(Stats, 13+2, 1) (BYTE(4))) AS NumNullsw3,
                    HASHBUCKET(SUBSTR(Stats, 13+1, 1)
                            || SUBSTR(Stats, 13+0, 1) (BYTE(4))) AS NumNullsw4,

                    (-1**(NumNullsw1 / 32768))
                    * (2**((NumNullsw1/16 MOD 2048) - 1023))
                    * (1 + ((NumNullsw1 MOD 16) * 2**-4) + (NumNullsw2 * 2**-20)
                         + (NumNullsw3 * 2**-36) + (NumNullsw4 * 2**-52)) (DECIMAL(18,0))    AS NumNulls,

                      /** Numbers are stored AS Floats after the data and data may be 16 or 32 BYTEs **/
                      /** depending on BYTE[23]**/
                    CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END               AS Offset,

                    HASHBUCKET(SUBSTR(Stats, 41+Offset+7, 1)
                            || SUBSTR(Stats, 41+Offset+6, 1) (BYTE(4))) AS ModeFreqw1,
                    HASHBUCKET(SUBSTR(Stats, 41+Offset+5, 1)
                            || SUBSTR(Stats, 41+Offset+4, 1) (BYTE(4))) AS ModeFreqw2,
                    HASHBUCKET(SUBSTR(Stats, 41+Offset+3, 1)
                            || SUBSTR(Stats, 41+Offset+2, 1) (BYTE(4))) AS ModeFreqw3,
                    HASHBUCKET(SUBSTR(Stats, 41+Offset+1, 1)
                            || SUBSTR(Stats, 41+Offset+0, 1) (BYTE(4))) AS ModeFreqw4,

                    (-1**(ModeFreqw1 / 32768))
                    * (2**((ModeFreqw1/16 MOD 2048) - 1023))
                    * (1 + ((ModeFreqw1 MOD 16) * 2**-4) + (ModeFreqw2 * 2**-20)
                         + (ModeFreqw3 * 2**-36) + (ModeFreqw4 * 2**-52)) (DECIMAL(18,0))    AS ModeFreq,

                    HASHBUCKET(SUBSTR(Stats, 49+Offset+7, 1)
                            || SUBSTR(Stats, 49+Offset+6, 1) (BYTE(4))) AS NumValuesw1,
                    HASHBUCKET(SUBSTR(Stats, 49+Offset+5, 1)
                            || SUBSTR(Stats, 49+Offset+4, 1) (BYTE(4))) AS NumValuesw2,
                    HASHBUCKET(SUBSTR(Stats, 49+Offset+3, 1)
                            || SUBSTR(Stats, 49+Offset+2, 1) (BYTE(4))) AS NumValuesw3,
                    HASHBUCKET(SUBSTR(Stats, 49+Offset+1, 1)
                            || SUBSTR(Stats, 49+Offset+0, 1) (BYTE(4))) AS NumValuesw4,

                    (-1**(NumValuesw1 / 32768))
                    * (2**((NumValuesw1/16 MOD 2048) - 1023))
                    * (1 + ((NumValuesw1 MOD 16) * 2**-4) + (NumValuesw2 * 2**-20)
                         + (NumValuesw3 * 2**-36) + (NumValuesw4 * 2**-52))(DECIMAL(18,0))     AS NumValues,

                    HASHBUCKET(SUBSTR(Stats, 57+Offset+7, 1)
                            || SUBSTR(Stats, 57+Offset+6, 1) (BYTE(4))) AS NumRowsw1,
                    HASHBUCKET(SUBSTR(Stats, 57+Offset+5, 1)
                            || SUBSTR(Stats, 57+Offset+4, 1) (BYTE(4))) AS NumRowsw2,
                    HASHBUCKET(SUBSTR(Stats, 57+Offset+3, 1)
                            || SUBSTR(Stats, 57+Offset+2, 1) (BYTE(4))) AS NumRowsw3,
                    HASHBUCKET(SUBSTR(Stats, 57+Offset+1, 1)
                            || SUBSTR(Stats, 57+Offset+0, 1) (BYTE(4))) AS NumRowsw4,

                    (-1**(NumRowsw1 / 32768))
                    * (2**((NumRowsw1/16 MOD 2048) - 1023))
                    * (1 + ((NumRowsw1 MOD 16) * 2**-4) + (NumRowsw2 * 2**-20)
                         + (NumRowsw3 * 2**-36) + (NumRowsw4 * 2**-52)) (DECIMAL(18,0))     AS NumRows

               FROM
                    (
                     SELECT DatabaseName    as databasename,
                            TableName       as tablename,

                      /** Concat up to 16 columnnames into a comma delimited list **/
                            MAX(CASE WHEN ColumnPosition = 1  THEN        '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 2  THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 3  THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 4  THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 5  THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 6  THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 7  THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 8  THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 9  THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 10 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 11 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 12 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 13 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 14 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 15 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 16 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 17 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 18 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 19 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 20 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 21 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 22 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 23 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 24 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 25 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 26 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 27 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 28 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 29 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 30 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 31 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 32 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 33 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 34 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 35 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 36 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 37 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 38 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 39 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 40 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 41 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 42 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 43 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 44 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 45 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 46 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 47 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 48 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 49 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 50 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 51 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 52 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 53 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 54 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 55 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 56 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 57 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 58 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 59 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 60 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 61 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 62 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 63 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 64 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition > 64 THEN ',...' ELSE '' END)
                            AS ColumnName,

                            COUNT(*) AS ColumnCount,

                            'I' AS StatsType,

                              /** Floats are stored after the data and data may be 16 or 32 bytes **/
                              /** depending on byte[23]**/
                            MAX(CASE WHEN SUBSTR(IndexStatistics, 23, 1) = '00'XB THEN 16 ELSE 0 END) AS Offset,

                            MAX(SUBSTR(IndexStatistics, 1, 80)) AS Stats

                       FROM dbc.IndexStats
                      where indextype in ('p','q','s','n','k','v','h','o')
                        and databasename in (:FromDBName)
                      GROUP BY DatabaseName,
                               TableName,
                               StatsType,
                               IndexNumber

               /*  include statement below to find indexes with existing statistics   */
               /*
                     HAVING Stats IS NOT NULL
               */
                     UNION ALL

                       /*Remove for pre-V2R5 --> **/
                     SELECT DatabaseName  as databasename,
                            TableName     as tablename,

                      /** Concat up to 16 columnnames into a comma delimited list **/
                            MAX(CASE WHEN ColumnPosition = 1 THEN         '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 2 THEN ','  || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 3 THEN ','  || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 4 THEN ','  || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 5 THEN ','  || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 6 THEN ','  || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 7 THEN ','  || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 8 THEN ','  || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 9 THEN ','  || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 10 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 11 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 12 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 13 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 14 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 15 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 16 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 17 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 18 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 19 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 20 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 21 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 22 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 23 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 24 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 25 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 26 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 27 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 28 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 29 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 30 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 31 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 32 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 33 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 34 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 35 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 36 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 37 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 38 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 39 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 40 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 41 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 42 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 43 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 44 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 45 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 46 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 47 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 48 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 49 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 50 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 51 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 52 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 53 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 54 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 55 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 56 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 57 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 58 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 59 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 60 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 61 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 62 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 63 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition = 64 THEN ',' || '"' || TRIM(ColumnName) || '"'  ELSE '' END) ||
                            MAX(CASE WHEN ColumnPosition > 64  THEN ',...' ELSE '' END)
                            AS ColumnName,

                            COUNT(*) AS ColumnCount,

                            'M' AS StatsType,

                              /** Floats are stored after the data and data may be 16 or 32 bytes **/
                              /** depending on byte[23]**/
                            MAX(CASE WHEN SUBSTR(ColumnsStatistics, 23, 1) = '00'XB THEN 16 ELSE 0 END) AS Offset,

                            MAX(SUBSTR(ColumnsStatistics, 1, 80)) AS Stats

                       FROM dbc.MultiColumnStats
                      where databasename in (:FromDBName)
                      GROUP BY DatabaseName,
                               TableName,
                               StatsType,
                               StatisticsID
                     HAVING Stats IS NOT NULL

                     UNION ALL
                       /*  * <-- Remove for pre-V2R5 **/

                     SELECT DatabaseName                     as databasename,
                            TableName                        as tablename,
                            '"' || TRIM(ColumnName) || '"'   as columnname,
                            1                                AS ColumnCount,
                            'C'                              AS StatsType,

                              /** Floats are stored after the data and data may be 16 or 32 bytes **/
                              /** depending on byte[23]**/
                            CASE WHEN SUBSTR(fieldStatistics, 23, 1) = '00'XB THEN 16 ELSE 0 END AS Offset,

                            SUBSTR(fieldstatistics, 1, 80)   AS Stats

                       FROM dbc.ColumnStats
                      where databasename in (:FromDBName)
                        and Stats IS NOT NULL
                     ) dt
              ) dt

           )pds1
           )pds2
        ORDER BY 1
     ;

      );





 
  Top Home Privacy Feedback  
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky
Copyright 2016 - All Rights Reserved
Last Modified: 28 Dec 2016