Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 27 May 2008 @ 13:43:57 GMT


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


Subj:   Dieter's collect stats query on v12
 
From:   Anomy Anom

<-- Anonymously Posted: Tue, 27 May 2008 08:14 -->

ISSUE

I just modified Dieter's Stats query to run on our 1 node, v12, LINUX box and it crashed with a 3610 (Internal error, do not resubmit.)


WHAT WAS DONE

I took the query as-is and removed the ") as Padbytes language. (Search for "only change is here" or " 4 AS PadBytes" ) )


QUESTION

Any thoughts on why the 3610?

     SELECT
        DatabaseName,
        TableName,
        ColumnName,

          /** Number of columns within multi-column or index stats **/

        ColumnCount,

          /** stats collected on:
             'C' --> Column
             'I' --> Index
             'M' --> Multiple columns (V2R5+)
             'D' --> Pseudo column PARTITION (V2R6.1+)
          **/

        StatsType,

          /** collect stats date **/

        CollectDate (DATE),

          /** collect stats time **/

        CollectTime (TIME(2)),

        CollectTimestamp (TIMESTAMP(2)),

          /** V2R5: sample size used for collect stats, NULL if not sampled **/

        SampleSize,

          /** Version
             1: pre-V2R5
             2: V2R5+
             3: TD12
          **/

        StatsVersion,

          /** TD12: Number of AMPs on the system **/

        NumAMPs,

          /** Number of intervals **/

        NumIntervals,

          /** TD12: All-AMPs average of the average number of rows per NUSI value

              per individual AMP, Estimated when Sampled **/

        AvgAmpRPV,

          /** Row Count, Estimated when Sampled **/

        NumRows (DECIMAL(18,0)),

          /** Distinct Values, Estimated when Sampled **/

        NumValues (DECIMAL(18,0)),

          /** Number of partly null and all null rows,

              Estimated when Sampled **/

        NumNulls (DECIMAL(18,0)),

          /** TD12: Number of all null rows in the column or index set,

              Estimated when Sampled **/

        NumAllNulls (DECIMAL(18,0)),

          /** Maximum number of rows / value, Estimated when Sampled **/

        ModeFreq (DECIMAL(18,0))

     FROM
       (
        SELECT

            /**  There's no way to check 32/64 bit using SQL,

                     so this must be hardcoded for 32 or 64 bit **/

                  /*** only change is here, removed 0 as Padbytes language  ***/

          4 AS PadBytes,

            /** TD12 changes the HASHBUCKET function (16 bit vs. 20 bit),

                on TD12 (using 20 bits for HashBuckets) the result must be divided
     by 16 **/

          ((HASHBUCKET()+1)/65536) AS TD12,

            /** TD12 introduces a new stats version with enhanced information,

                therefore header data is increased by 34 bytes **/

          CASE WHEN StatsVersion = 3 THEN 34 ELSE 0 END AS V3,

          /** Numbers are stored AS Floats after the data and data may be 16 or 32
     BYTEs

              depending on BYTE[23] for 32-bit or BYTE[27] for 64-bit **/

          CASE
            WHEN SUBSTR(STATS, 23+PadBytes, 1) = '00'XB THEN 16
            ELSE 0
          END + 2*PadBytes + V3 AS Offset,

          DatabaseName,
          TableName,
          ColumnName,
          ColumnCount,
          STATS,
          StatsType,

          (
           (HASHBUCKET
             (SUBSTR(STATS, 2, 1) ||
              SUBSTR(STATS, 1, 1) (BYTE(4))
             ) / TD12 - 1900
           ) * 10000
           +
           (HASHBUCKET
             ('00'xb || SUBSTR(STATS, 3, 1) (BYTE(4))
             ) / TD12
           ) * 100
           +
           (HASHBUCKET
             (
              '00'xb || SUBSTR(STATS, 4, 1) (BYTE(4))
             ) / TD12
           ) (DATE, FORMAT 'yyyy-mm-ddB')
          ) AS CollectDate,

          (
           (HASHBUCKET
             (CAST('00'xb || SUBSTR(STATS, 5, 1) AS BYTE(4))
              ) / TD12 (FORMAT '99:')
            ) ||
            (HASHBUCKET
              (CAST('00'xb || SUBSTR(STATS, 6, 1) AS BYTE(4))
              ) / TD12 (FORMAT '99:')
            ) ||
            (HASHBUCKET
              (CAST('00'xb || SUBSTR(STATS, 7, 1) AS BYTE(4))
              ) / TD12 (FORMAT '99.')
            ) ||
            (HASHBUCKET
              (CAST('00'xb || SUBSTR(STATS, 8, 1) AS BYTE(4))
              ) / TD12 (FORMAT '99')
            ) (TIME(2), FORMAT 'hh:mi:ss.s(2)')
          ) AS CollectTime,

          (CollectDate || (CollectTime (CHAR(11))))
            (TIMESTAMP(2), FORMAT 'yyyy-mm-ddBhh:mi:ss.s(2)') AS CollectTimestamp,

          HASHBUCKET (SUBSTR(STATS, 9+1, 1)
                   || SUBSTR(STATS, 9+0, 1) (BYTE(4)))  / TD12 AS StatsVersion,

          CASE
            WHEN HASHBUCKET ('00'xb || SUBSTR(STATS, 11, 1) (BYTE(4))) / TD12 = 1
            THEN HASHBUCKET ('00'xb || SUBSTR(STATS, 12, 1) (BYTE(4))) / TD12
            ELSE NULL
          END AS SampleSize,

          HASHBUCKET(SUBSTR(STATS, 13+PadBytes+7, 1)
                  || SUBSTR(STATS, 13+PadBytes+6, 1) (BYTE(4))) / TD12 AS NumNullsw1,

          HASHBUCKET(SUBSTR(STATS, 13+PadBytes+5, 1)
                  || SUBSTR(STATS, 13+PadBytes+4, 1) (BYTE(4))) / TD12 AS NumNullsw2,

          HASHBUCKET(SUBSTR(STATS, 13+PadBytes+3, 1)
                  || SUBSTR(STATS, 13+PadBytes+2, 1) (BYTE(4))) / TD12 AS NumNullsw3,

          HASHBUCKET(SUBSTR(STATS, 13+PadBytes+1, 1)
                  || SUBSTR(STATS, 13+PadBytes+0, 1) (BYTE(4))) / TD12 AS NumNullsw4,

          CASE WHEN SUBSTR(STATS, 13+PadBytes+0, 8) = '00'xb THEN 0
          ELSE
            (-1**(NumNullsw1 / 32768))
            * (2**((NumNullsw1/16 MOD 2048) - 1023))
            * (1 + ((NumNullsw1 MOD 16) * 2**-4) + (NumNullsw2 * 2**-20)
                 + (NumNullsw3 * 2**-36) + (NumNullsw4 * 2**-52))
          END AS NumNulls,

          HASHBUCKET(SUBSTR(STATS, 21+PadBytes+1, 1)
                  || SUBSTR(STATS, 21+PadBytes+0, 1) (BYTE(4))) / TD12 AS
     NumIntervals,

          HASHBUCKET(SUBSTR(STATS, 25+PadBytes+7, 1)
                  || SUBSTR(STATS, 25+PadBytes+6, 1) (BYTE(4))) / TD12 AS
     NumAllNullsw1,

          HASHBUCKET(SUBSTR(STATS, 25+PadBytes+5, 1)
                  || SUBSTR(STATS, 25+PadBytes+4, 1) (BYTE(4))) / TD12 AS
     NumAllNullsw2,

          HASHBUCKET(SUBSTR(STATS, 25+PadBytes+3, 1)
                  || SUBSTR(STATS, 25+PadBytes+2, 1) (BYTE(4))) / TD12 AS
     NumAllNullsw3,

          HASHBUCKET(SUBSTR(STATS, 25+PadBytes+1, 1)
                  || SUBSTR(STATS, 25+PadBytes+0, 1) (BYTE(4))) / TD12 AS
     NumAllNullsw4,

          CASE
            WHEN StatsVersion < 3 THEN CAST(NULL AS FLOAT)
            WHEN SUBSTR(STATS, 25+PadBytes+0, 8) = '00'xb THEN 0
          ELSE
            (-1**(NumAllNullsw1 / 32768))
            * (2**((NumAllNullsw1/16 MOD 2048) - 1023))
            * (1 + ((NumAllNullsw1 MOD 16) * 2**-4) + (NumAllNullsw2 * 2**-20)
                 + (NumAllNullsw3 * 2**-36) + (NumAllNullsw4 * 2**-52))
          END AS NumAllNulls,

          HASHBUCKET(SUBSTR(STATS, 33+PadBytes+7, 1)
                  || SUBSTR(STATS, 33+PadBytes+6, 1) (BYTE(4))) / TD12 AS AvgAmpRPVw1,

          HASHBUCKET(SUBSTR(STATS, 33+PadBytes+5, 1)
                  || SUBSTR(STATS, 33+PadBytes+4, 1) (BYTE(4))) / TD12 AS AvgAmpRPVw2,

          HASHBUCKET(SUBSTR(STATS, 33+PadBytes+3, 1)
                  || SUBSTR(STATS, 33+PadBytes+2, 1) (BYTE(4))) / TD12 AS AvgAmpRPVw3,

          HASHBUCKET(SUBSTR(STATS, 33+PadBytes+1, 1)
                  || SUBSTR(STATS, 33+PadBytes+0, 1) (BYTE(4))) / TD12 AS AvgAmpRPVw4,

          CASE
            WHEN StatsVersion < 3 THEN CAST(NULL AS FLOAT)
            WHEN SUBSTR(STATS, 33+PadBytes+0, 8) = '00'xb THEN 0
          ELSE
            (-1**(AvgAmpRPVw1 / 32768))
            * (2**((AvgAmpRPVw1/16 MOD 2048) - 1023))
            * (1 + ((AvgAmpRPVw1 MOD 16) * 2**-4) + (AvgAmpRPVw2 * 2**-20)
                 + (AvgAmpRPVw3 * 2**-36) + (AvgAmpRPVw4 * 2**-52))
          END AS AvgAmpRPV,

          CASE
            WHEN StatsVersion < 3 THEN CAST(NULL AS FLOAT)
            ELSE
              HASHBUCKET(SUBSTR(STATS, 57+PadBytes+1, 1)
                      || SUBSTR(STATS, 57+PadBytes+0, 1) (BYTE(4))) / TD12
          END AS NumAMPs,

          HASHBUCKET(SUBSTR(STATS, 41+Offset+7, 1)
                  || SUBSTR(STATS, 41+Offset+6, 1) (BYTE(4))) / TD12 AS ModeFreqw1,

          HASHBUCKET(SUBSTR(STATS, 41+Offset+5, 1)
                  || SUBSTR(STATS, 41+Offset+4, 1) (BYTE(4))) / TD12 AS ModeFreqw2,

          HASHBUCKET(SUBSTR(STATS, 41+Offset+3, 1)
                  || SUBSTR(STATS, 41+Offset+2, 1) (BYTE(4))) / TD12 AS ModeFreqw3,

          HASHBUCKET(SUBSTR(STATS, 41+Offset+1, 1)
                  || SUBSTR(STATS, 41+Offset+0, 1) (BYTE(4))) / TD12 AS ModeFreqw4,

          CASE WHEN SUBSTR(STATS, 41+Offset+0, 8) = '00'xb THEN 0
          ELSE
            (-1**(ModeFreqw1 / 32768))
            * (2**((ModeFreqw1/16 MOD 2048) - 1023))
            * (1 + ((ModeFreqw1 MOD 16) * 2**-4) + (ModeFreqw2 * 2**-20)
                 + (ModeFreqw3 * 2**-36) + (ModeFreqw4 * 2**-52))
          END AS ModeFreq,

          HASHBUCKET(SUBSTR(STATS, 49+Offset+7, 1)
                  || SUBSTR(STATS, 49+Offset+6, 1) (BYTE(4))) / TD12 AS NumValuesw1,

          HASHBUCKET(SUBSTR(STATS, 49+Offset+5, 1)
                  || SUBSTR(STATS, 49+Offset+4, 1) (BYTE(4))) / TD12 AS NumValuesw2,

          HASHBUCKET(SUBSTR(STATS, 49+Offset+3, 1)
                  || SUBSTR(STATS, 49+Offset+2, 1) (BYTE(4))) / TD12 AS NumValuesw3,

          HASHBUCKET(SUBSTR(STATS, 49+Offset+1, 1)
                  || SUBSTR(STATS, 49+Offset+0, 1) (BYTE(4))) / TD12 AS NumValuesw4,

          CASE WHEN SUBSTR(STATS, 49+Offset+0, 8) = '00'xb THEN 0
          ELSE
            (-1**(NumValuesw1 / 32768))
            * (2**((NumValuesw1/16 MOD 2048) - 1023))
            * (1 + ((NumValuesw1 MOD 16) * 2**-4) + (NumValuesw2 * 2**-20)
                 + (NumValuesw3 * 2**-36) + (NumValuesw4 * 2**-52))
          END AS NumValues,

          HASHBUCKET(SUBSTR(STATS, 57+Offset+7, 1)
                  || SUBSTR(STATS, 57+Offset+6, 1) (BYTE(4))) / TD12 AS NumRowsw1,

          HASHBUCKET(SUBSTR(STATS, 57+Offset+5, 1)
                  || SUBSTR(STATS, 57+Offset+4, 1) (BYTE(4))) / TD12 AS NumRowsw2,

          HASHBUCKET(SUBSTR(STATS, 57+Offset+3, 1)
                  || SUBSTR(STATS, 57+Offset+2, 1) (BYTE(4))) / TD12 AS NumRowsw3,

          HASHBUCKET(SUBSTR(STATS, 57+Offset+1, 1)
                  || SUBSTR(STATS, 57+Offset+0, 1) (BYTE(4))) / TD12 AS NumRowsw4,

          CASE WHEN SUBSTR(STATS, 57+Offset+0, 8) = '00'xb THEN 0
          ELSE
            (-1**(NumRowsw1 / 32768))
            * (2**((NumRowsw1/16 MOD 2048) - 1023))
            * (1 + ((NumRowsw1 MOD 16) * 2**-4) + (NumRowsw2 * 2**-20)
                 + (NumRowsw3 * 2**-36) + (NumRowsw4 * 2**-52))
          END AS NumRows

        FROM
         (
          SELECT
            DatabaseName,
            TableName,

            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 > 16  THEN ',...' ELSE '' END) AS ColumnName,

            COUNT(*) AS ColumnCount,

            'I' AS StatsType,

            MAX(SUBSTR(IndexStatistics, 1, 114)) AS STATS

          FROM
            dbc.IndexStats

          GROUP BY
            DatabaseName,
            TableName,
            StatsType,
            IndexNumber

          HAVING STATS IS NOT NULL


     /** Remove for pre-V2R5 --> **/

          UNION ALL

          SELECT
            DatabaseName,
            TableName,

            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 > 16  THEN ',...' ELSE '' END) AS ColumnName,

            COUNT(*) AS ColumnCount,

            MAX(CASE WHEN StatisticsId = 129 AND ColumnName = 'PARTITION' THEN 'D'
     ELSE 'M' END) AS StatsType,

            MAX(SUBSTR(ColumnsStatistics, 1, 114)) AS STATS

          FROM
            dbc.MultiColumnStats

          GROUP BY
            DatabaseName,
            TableName,
            StatisticsID

          HAVING STATS IS NOT NULL

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

          UNION ALL

          SELECT
            DatabaseName,
            TableName,
            ColumnName,
            1 AS ColumnCount,
            'C' AS StatsType,
             SUBSTR(fieldstatistics, 1, 114) AS STATS

          FROM
            dbc.ColumnStats

          WHERE STATS IS NOT NULL
         ) dt
       ) dt

     ORDER BY
         DatabaseName,
         TableName,
         ColumnName
     ;

Rgrds



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016