|
|
|
 |
 |
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 | ||||||||||||||||||||||||||||||||||||||||||||||||
