Retrieving catalog information about LOBs

The SYSIBM.SYSAUXRELS table contains information about the relationship between a base table and an auxiliary table.

Procedure

Begin general-use programming interface information.

To retrieve catalog information about LOBs:

Query the SYSIBM.SYSAUXRELS table.

For example, this query returns information about the name of the LOB columns for the employee table and its associated auxiliary table schema and name:

SELECT COLNAME, PARTITION, AUXTBOWNER, AUXTBNAME
   FROM SYSIBM.SYSAUXRELS
   WHERE TBNAME = 'EMP' AND TBOWNER = 'DSN8C10';

Information about the length of a LOB is in the LENGTH2 column of the SYSCOLUMNS table. You can query information about the length of the column as it is returned to an application with the following query:

SELECT NAME, TBNAME, COLTYPE, LENGTH2, NULLS, DEFAULT
  FROM SYSIBM.SYSCOLUMNS
  WHERE TBNAME='DEPT'
  AND TBCREATOR = 'DSN8C10';

End general-use programming interface information.