How to know if a tablespace contains LOB or LF data

[Former Member]

SAP release the note 1347302 - DB6: Bad page for tablespaces larger than 2TB.

The issue occurs with large object (LOB) or long field (LF) data in table spaces that are larger

than 2 TB in size.

How can i know if a tablespace contains LOB or LF data ?

Please help.

[Former Member]

Malte, Anjali

Thanks for your help. Malte's SQL is very helpful to my question.

[Former Member]

I think, you can see in the properties of the tablespaces.

there you can see the 'type of data', 'managed by', 'Autmatic storage' 'Autoresize' etc kind of properties of a tablespace.

'Type of data' will show you whether the tablespace is 'User temporary' , 'System temporary' , 'Regular' or the 'Large' one.

I am not sure, by which transaction you can see this, may be DB02 or any query at DB level. Or, db2cc tool will obviously tell you.

*Edit: couldn't see the reply from Malte. I will also try with his sql query

Edited by: Anjali Sharma2 on Jul 21, 2009 1:00 PM

[malte_schuenemann]

Run

db2 "select a.tabschema, a.tabname, a.colname, 
        a.typename, b.tbspace
from syscat.columns a, syscat.tables b 
where a.tabschema = b.tabschema 
and a.tabname = b.tabname 
and a.typename in ('BLOB','CLOB','DBCLOB','LONG VARCHAR')"

You will get tables with schema, the LOB / LF column name, the type of the column, the tablespace name. With SUBTR you may get that in a format more convenient for you

db2 "select substr(a.tabschema,1,30) as tabschema, 
         substr(a.tabname,1,30) as tabname) ...

Malte