/** Additional info on collected statistics, including Date, Time, Rowcount... 2003-03-12 dn initial version 2003-07-22 dn modified to use dbc.???Stats views instead of base tables 2004-01-20 dn added ColumnCount 2004-11-08 dn added StatsVersion fixed SampleSize for pre-V2R5 stats, now displays 100% 2004-11-15 dn added version based on base tables to display CollectDuration, modified/reformatted source code 2007-05-22 dn modified base table version to include the pseudo-column PARTITION (V2R6.1+) 2007-08-09 dn modified to work for both 32-bit and 64-bit versions 2007-10-22 dn added FORMAT for date/timestamp typecasts to prevent errors 2007-11-02 dn implemented changes for version 3 statistics (TD12), added NumAMPs (TD12), NumIntervals, AvgAmpRPV (TD12), NumAllNulls (TD12) Encoding changed for 64-bit version: 8 Bytes added (byte[13..16] and [29..32]) usually, but not always '00'XB. To align on 8 byte boundaries? There's no way to check for 32/64 bit using SQL, so this must be hardcoded: Search for "PadBytes" and adjust for 32 or 64 bit **/ /** How to cast Bytes to Floats: Following definition is from http://www.psc.edu/general/software/packages/ieee/ieee.html The IEEE double precision floating point standard representation requires a 64 bit word, which may be represented as numbered from 0 to 63, left to right. The first bit is the sign bit, S, the next eleven bits are the exponent bits, 'E', and the final 52 bits are the fraction 'F': S EEEEEEEEEEE FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 0 1 11 12 63 The value V represented by the word may be determined as follows: * If E=2047 and F is nonzero, then V=NaN ("Not a number") * If E=2047 and F is zero and S is 1, then V=-Infinity * If E=2047 and F is zero and S is 0, then V=Infinity -> * If 0 where "1.F" is intended to represent the binary number created by -> prefixing F with an implicit leading 1 and a binary point. * If E=0 and F is nonzero, then V=(-1)**S * 2 ** (-1022) * (0.F) These are "unnormalized" values. * If E=0 and F is zero and S is 1, then V=-0 -> * If E=0 and F is zero and S is 0, then V=0 I use the -> marked calculation, as there are hopefully only normalized and valid numbers. e.g. '000000000000f03f'xb = 1 '000000000000f0bf'xb = -1 '0000000030831E41'xb = 499916 BYTE(8) --> IEEE Float value SELECT CASE WHEN b = '00'xb THEN 0 ELSE (-1**(w1 / 32768)) ---sign * (2**((w1/16 MOD 2048) - 1023)) ---exponent * (1 + ((w1 MOD 16) * 2**-4) + (w2 * 2**-20) + (w3 * 2**-36) + (w4 * 2**-52)) ---fraction END AS FloatValue FROM ( SELECT b, HASHBUCKET(SUBSTR(b, 8, 1) || SUBSTR(b, 7, 1) (byte(4))) AS w1, HASHBUCKET(SUBSTR(b, 6, 1) || SUBSTR(b, 5, 1) (byte(4))) AS w2, HASHBUCKET(SUBSTR(b, 4, 1) || SUBSTR(b, 3, 1) (byte(4))) AS w3, HASHBUCKET(SUBSTR(b, 2, 1) || SUBSTR(b, 1, 1) (byte(4))) AS w4 FROM (SELECT '0000000030831E41'xb AS b) dt )dt ; ***/ /** Base table version, select rights needed on: dbc.dbase, dbc.tvm, dbc.tvfields, dbc.Indexes **/ 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)), /** Time needed to collect stats I don't know if it's really correct, because CollectDuration is sometimes negative for sample stats, That's why i use ABS ;-) **/ ABS(CollectDuration) AS CollectDuration, /** 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 /** Encoding changed FOR 64-bit version: 8 Bytes added (byte[13..16] and [29..32]) usually, but not always '00'XB. To align on 8 byte boundaries? There's no way to check 32/64 bit using SQL, so this must be hardcoded for 32 or 64 bit **/ 0 AS PadBytes, -- 32 bit 4 AS PadBytes, -- 64 bit /** 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, CollectTimestamp- ( COALESCE( MAX(CollectTimestamp) OVER ( PARTITION BY DatabaseName, TableName, LastAlterTimestamp ORDER BY CollectTimestamp ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) ,LastAlterTimestamp) ) HOUR(4) TO SECOND(2) AS CollectDuration, 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 d.databasename AS DatabaseName, t.tvmname AS TableName, MAX(CASE WHEN i.FieldPosition = 1 THEN (CASE WHEN IndexType = 'D' THEN 'PARTITION' ELSE TRIM(c.FieldName) END) 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, MAX(i.LastAlterTimestamp) AS LastAlterTimestamp, COUNT(*) AS ColumnCount, CASE IndexType WHEN 'M' THEN 'M' WHEN 'D' THEN 'D' ELSE 'I' END AS StatsType, MAX(SUBSTR(i.IndexStatistics, 1, 114)) AS STATS FROM dbc.Indexes i JOIN dbc.tvm t ON t.TVMid = i.tableid JOIN dbc.dbase d ON t.databaseid = d.databaseid LEFT JOIN dbc.tvfields c ON c.tableid = i.tableid AND c.fieldid = i.fieldid GROUP BY DatabaseName, TableName, StatsType, i.IndexNumber HAVING STATS IS NOT NULL UNION ALL SELECT d.databasename AS DatabaseName, t.tvmname AS TableName, c.fieldname AS ColumnName, c.LastAlterTimestamp, 1 AS ColumnCount, 'C' AS StatsType, SUBSTR(c.fieldstatistics, 1, 114) AS STATS FROM dbc.dbase d JOIN dbc.tvm t ON d.databaseid = t.databaseid JOIN dbc.tvfields c ON t.tvmid = c.tableid WHERE STATS IS NOT NULL ) dt ) dt ORDER BY DatabaseName, TableName, ColumnName ; /** View version, select rights needed on: dbc.ColumnStats, dbc.IndexStats, dbc.MultiColumnStats **/ 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 /** Encoding changed FOR 64-bit version: 8 Bytes added (byte[13..16] and [29..32]) usually, but not always '00'XB. To align on 8 byte boundaries? There's no way to check 32/64 bit using SQL, so this must be hardcoded for 32 or 64 bit **/ 0 AS PadBytes, -- 32 bit 4 AS PadBytes, -- 64 bit /** 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 ;