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.