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.

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 

Columns

• 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

Rows

• 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: