|
Archives of the TeradataForumMessage Posted: Tue, 27 May 2008 @ 13:43:57 GMT
<-- 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
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||