List schemas in Db2 database

Query below lists all schemas in Db2 database. List includes default SYS* , SQLJ, NULLID schemas.

If you want to list user only schemas use this query.


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
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, including default ones
  • Ordered by schema name

Sample results

Here is a view of database schemas in IBM Data Studio:

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.