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.

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    
where schemaname not like 'SYS%'
and  schemaname not IN ('SQLJ', 'NULLID')
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, excluding default SYS* , SQLJ, NULLID
  • Ordered by schema name

Sample results

0
There are no comments. Click here to write the first comment.