/*
    IBM Db2 LUW database data dictionary

    Returns:
      - Schema, table and field names
      - Data type, length, precision and scale
      - Column position, nullability and default value
      - Estimated record count
      - Data, index, LOB, XML and total physical space
      - Statistics date
*/

WITH table_space AS
(
    SELECT
        TABSCHEMA,
        TABNAME,

        SUM(
            COALESCE(DATA_OBJECT_P_SIZE, 0)
          + COALESCE(COL_OBJECT_P_SIZE, 0)
        ) AS DATA_SPACE_KB,

        SUM(COALESCE(INDEX_OBJECT_P_SIZE, 0))
            AS INDEX_SPACE_KB,

        SUM(
            COALESCE(LONG_OBJECT_P_SIZE, 0)
          + COALESCE(LOB_OBJECT_P_SIZE, 0)
          + COALESCE(XML_OBJECT_P_SIZE, 0)
        ) AS LOB_SPACE_KB,

        SUM(
            COALESCE(DATA_OBJECT_P_SIZE, 0)
          + COALESCE(COL_OBJECT_P_SIZE, 0)
          + COALESCE(INDEX_OBJECT_P_SIZE, 0)
          + COALESCE(LONG_OBJECT_P_SIZE, 0)
          + COALESCE(LOB_OBJECT_P_SIZE, 0)
          + COALESCE(XML_OBJECT_P_SIZE, 0)
        ) AS TOTAL_SPACE_KB

    FROM SYSIBMADM.ADMINTABINFO

    GROUP BY
        TABSCHEMA,
        TABNAME
)

SELECT
    c.TABSCHEMA                              AS "schema",
    c.TABNAME                                AS "tableName",
    c.COLNAME                                AS "fieldName",
    c.TYPENAME                               AS "type",
    c.LENGTH                                 AS "length",

    CASE
        WHEN c.TYPENAME IN ('SMALLINT', 'INTEGER', 'BIGINT', 'DECIMAL', 'NUMERIC', 'DECFLOAT', 'REAL', 'DOUBLE')
        THEN c.LENGTH
        ELSE NULL
    END                                      AS "precision",
    c.SCALE                                  AS "scale",
    c.COLNO + 1                              AS "column_id",
    CASE c.NULLS
        WHEN 'Y' THEN 'YES'
        ELSE 'NO'
    END                                      AS "nullable",

    c.DEFAULT                                AS "defaultValue",

    /* Estimated record count from RUNSTATS */
    CASE
        WHEN t.CARD < 0 THEN NULL
        ELSE t.CARD
    END                                      AS "RowCount",

    COALESCE(s.DATA_SPACE_KB, 0)             AS "DataSpaceKB",
    COALESCE(s.INDEX_SPACE_KB, 0)            AS "IndexSpaceKB",
    COALESCE(s.LOB_SPACE_KB, 0)              AS "LobSpaceKB",
    COALESCE(s.TOTAL_SPACE_KB, 0)            AS "TotalSpaceKB",

    t.STATS_TIME                             AS "LastAnalysed"

FROM SYSCAT.COLUMNS c

JOIN SYSCAT.TABLES t
  ON t.TABSCHEMA = c.TABSCHEMA
 AND t.TABNAME   = c.TABNAME
 AND t.TYPE      = 'T'

LEFT JOIN table_space s
  ON s.TABSCHEMA = c.TABSCHEMA
 AND s.TABNAME   = c.TABNAME

WHERE c.TABSCHEMA NOT LIKE 'SYS%'
  AND c.TABSCHEMA NOT IN
  (
      'NULLID',
      'SQLJ',
      'SYSTOOLS'
  )

-- AND c.TABSCHEMA IN ('APPLICATION_SCHEMA')

ORDER BY
    c.TABSCHEMA,
    c.TABNAME,
    c.COLNO;