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.

Query

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, 
    c.tabname, 
    c.colname;

Columns

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

Rows

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