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, c.tabname order by c.tabschema, c.tabname
- 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
List of tables with LOB columns in database: