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.

Query

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, 
    c.tabname  
order by c.tabschema, 
    c.tabname  

Columns

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

Rows

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