Find tables with 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 tables that cointain columns with LOB data types.


select c.tabschema as schema_name,
       c.tabname as table_name,
       count(*) as columns
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')
group by c.tabschema, 
order by c.tabschema, 


  • schema_name - schema name
  • table_name - table name
  • columns - number of LOB columns in a table


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

Sample results

List of tables with 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.