List user created schemas in Db2 database

Query below lists schemas in database created by users, i.e. excluding default SYS*, SQLJ, and NULLID schemas.

Use this query to list all schemas.


select schemaname as schema_name,
    owner as schema_owner,
    case ownertype 
        when 'S' then 'system'
        when 'U' then 'individual user' 
    end as schema_owner_type,
    definer as schema_definer,
    case definertype 
        when 'S' then 'system'
        when 'U' then 'individual user'
    end as schema_definer_type
from syscat.schemata    
where schemaname not like 'SYS%'
and  schemaname not IN ('SQLJ', 'NULLID')
order by schema_name


  • schema_name - schema name
  • schema_owner - authorization ID of the owner of the schema
  • schema_owner_type - owner type:
    • system
    • individual user
  • schema_definer - authorization ID of the definer of the schema
  • schema_definer_type - definer type
    • system
    • individual user


  • One row represents one schema in a database
  • Scope of rows: all schemas in a database, excluding default SYS* , SQLJ, NULLID
  • Ordered by schema name

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.