--# Copyright IBM Corp. All Rights Reserved.
--# SPDX-License-Identifier: Apache-2.0

/*
 * Shows the average encoded length in bytes for columns in COLUMN ORGANIZED tables
 */

CREATE OR REPLACE VIEW DB_COLUMN_ENCODING AS
SELECT
    TABSCHEMA
,   TABNAME
,   COLNO
,   COLNAME
,   CASE WHEN TYPENAME NOT IN ('CLOB','LOB','DBCLOB') THEN DECIMAL(AVGENCODEDCOLLEN,9,3) END AS AVGENCODEDCOLLEN -- Ignore spurious values for LOBs. They are not encoded in BLU.
,   PCTENCODED
,   TYPENAME
,   LENGTH
,   TYPESTRINGUNITS
,   SCALE
,   NULLS
,   AVGCOLLEN
,   QUANTIZE(AVGCOLLEN::DECFLOAT/NULLIF(AVGENCODEDCOLLEN,0) ,00.001) AS RATIO
,   AVGCOLLENCHAR
,   QUANTIZE(AVGCOLLEN::DECFLOAT/NULLIF(AVGCOLLENCHAR,0)    ,00.001) AS CHAR_RATIO
,   COLCARD
,   NUMNULLS
,   T.CARD        AS TABLE_CARD
FROM
    SYSCAT.COLUMNS 
INNER JOIN
    SYSCAT.TABLES T USING ( TABSCHEMA, TABNAME )
WHERE
    T.TABLEORG = 'C'
    
