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

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