Re: Where is CLOB data physically stored?

On 03-Mar-2015 10:39 -0600, Needles,Stephen J wrote:

I'm trying to understand a few of things and so far, my research
hasn't satisfied me. We are trying to get our heads around our disk
usage and have a couple of questions we've been unable to find
answers for.

The physical storage location(s) for CLOB data is an implementation detail subject to change, and also is mercurial as the data is maintained. I expect that a LIC [formatted] dump of the DataSpace object will reveal the location(s) of the CLOB data as a snapshot.

1) If a table has a column defined as CLOB(1M)...where is the data
actually stored as it is referenced with a *POINTER?

The actual data must be de-referenced using the pointer. An interface that does not support access to the data of the data type offers the presentation of the symbolic text "*POINTER" instead, to suggest that the data is available elsewhere.

2) I've been told that the use of a CLOB allows the data to only
take up the physical space it actually uses as opposed to a CHAR
column where all of the defined space is allocated immediately. Is
this true?

Effectively, for the initial storage; all LOB data is varying length, so the concept for the storage is most similar to what could be found to describe VARCHAR. As the data is maintained, movement of the data will open-up space and take new space or reuse old space. Thus the accounting for the storage will necessarily exceed the actual requirements. Also like with general dataspace sizing, the algorithm will tend toward growth to accommodate incoming data so as to limit the number of /extents/ to any particular storage location; e.g. as each row is added, the database would not /extend/ the storage space to accommodate just the one new row being added, rather in anticipation of more rows being added there will be storage /carved out/ for multiple rows worth of data.

After two successive Reorganize Database File Member (RGZPFM) requests using the option to _not_ allow cancellation [i.e. no the online reorg] using the ALWCANCEL(*NO) specification, the storage for the LOBs [and the dataspace object generally] should be truncated to a [at least somewhat] minimal amount such that the very next insert would likely require new extent(s) for the various data segments that make up the DataSpace (*QDDS) object.

3) How can I determine the actual amount of CLOB space is physically
used?

There may be some tooling [perhaps something on developerworks in the form of a UDTF to get some dataspace statistics], but the LIC dump of the dataspace is the only way I know should give the most explicit information.