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.

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:

0
There are no comments. Click here to write the first comment.