Find large object (LOB) data type columns in Db2 database

Large objects in IBM Db2 are columns with following data types: BLOB, CLOB, or DBCLOB. In a Unicode database, NCLOB can be used as a synonym for DBCLOB.

Query below lists all columns with LOB data types.


select c.tabschema as schema_name,
       c.tabname as table_name,
       c.colname as column_name,
       c.typename as data_type
from syscat.columns c
inner join syscat.tables t on 
      t.tabschema = c.tabschema and t.tabname = c.tabname
where t.type = 'T'
    and t.tabschema not like 'SYS%'
    and typename in ('BLOB', 'CLOB', 'DBCLOB')
order by c.tabschema, 


  • schema_name - schema name
  • table_name - table name
  • column_name - column name
  • data_type - data type


  • One row represents one column with LOB data type
  • Scope of rows: all rows containing LOB data types in current database
  • Ordered by schema name, table name and column name

Sample results

List of LOB columns in database:

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.