Find required (non nullable) columns in Db2 database

Query below lists all non-nullable columns in a database.

Query

select c.tabschema as schema_name, 
    c.tabname as table_name,
    c.colname as column_name, 
    c.colno as position,
    c.typename as data_type,
    c.length,
    c.scale
from syscat.columns c
where c.tabschema not like 'SYS%' 
    and c.nulls = 'N'
order by c.tabschema, c.tabname, c.colname; 

Columns

  • schema_name - schema name
  • table_name - table name
  • column_name - name of column
  • position - number of this column in the table (starting with 0)
  • data_type - name of the data typ
  • length - maximum length of the data; 0 for distinct types.
  • scale
    • scale if the column type is DECIMAL,
    • number of digits of fractional seconds if the column type is TIMESTAMP,
    • 0 otherwise

Rows

  • One row represents one table column
  • Scope of rows: all non-nullable columns in all tables in a database
  • Ordered by schema, table name, column id

Sample results

Non-nullable columns in sample database.