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

       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 easy way to check 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<E<2047 then V=(-1)**S * 2 ** (E-1023) * (1.F)
->       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,

     /** collect stats time **/
   CollectTime,

   CollectTimestamp,

     /** 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,

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

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

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

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

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

     /** Maximum number of rows / value, Estimated when SampleSize < 100 **/
   ModeFreq

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

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

     (
       (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 (FORMAT 'yyyy-mm-ddB'))
     || CAST(CollectTime AS CHAR(11)) AS TIMESTAMP(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)))
     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+PadBytes+7, 1)
             || SUBSTR(STATS, 13+PadBytes+6, 1) (BYTE(4))) AS NumNullsw1,
     HASHBUCKET(SUBSTR(STATS, 13+PadBytes+5, 1)
             || SUBSTR(STATS, 13+PadBytes+4, 1) (BYTE(4))) AS NumNullsw2,
     HASHBUCKET(SUBSTR(STATS, 13+PadBytes+3, 1)
             || SUBSTR(STATS, 13+PadBytes+2, 1) (BYTE(4))) AS NumNullsw3,
     HASHBUCKET(SUBSTR(STATS, 13+PadBytes+1, 1)
             || SUBSTR(STATS, 13+PadBytes+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] 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 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
       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, 88)) 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, 88) 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,

     /** collect stats time **/
   CollectTime,

   CollectTimestamp,

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

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

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

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

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

     /** Maximum number of rows / value, Estimated when SampleSize < 100 **/
   ModeFreq

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

     DatabaseName,
     TableName,
     ColumnName,
     ColumnCount,
     STATS,
     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 (FORMAT 'yyyy-mm-ddB'))
     || CAST(CollectTime AS CHAR(11)) AS TIMESTAMP(2)) 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+PadBytes+7, 1)
             || SUBSTR(STATS, 13+PadBytes+6, 1) (BYTE(4))) AS NumNullsw1,
     HASHBUCKET(SUBSTR(STATS, 13+PadBytes+5, 1)
             || SUBSTR(STATS, 13+PadBytes+4, 1) (BYTE(4))) AS NumNullsw2,
     HASHBUCKET(SUBSTR(STATS, 13+PadBytes+3, 1)
             || SUBSTR(STATS, 13+PadBytes+2, 1) (BYTE(4))) AS NumNullsw3,
     HASHBUCKET(SUBSTR(STATS, 13+PadBytes+1, 1)
             || SUBSTR(STATS, 13+PadBytes+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] (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 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,
       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, 88)) 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, 88)) 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, 120) AS STATS

     FROM
       dbc.ColumnStats
     WHERE STATS IS NOT NULL
    ) dt
  ) dt
ORDER BY
   DatabaseName,
   TableName,
   ColumnName
;