IDS Forum

Re: space taken by table - including smart large o

Posted By: david@smooth1.co.uk
Date: Thursday, 21 March 2013, at 10:07 a.m.

In Response To: space taken by table - including smart large obj, (MIKE DUNHAM-WILKIE)

The simplest approach is to select sum(length(column)) for the whole table.
which also works for simple large objects (BYTE and TEXT).

FYI The normal calculation for the table will include include

- the 72 byte smart LO descriptor which is stored on the date page for the
associated row.

- the 56 byte descriptor stored in the data row for simple large objects.

If you want a complete answer then also include sbspace metadata which is much
more complex.

The sbspace metadata area contains:

- Sbspace description partition a single structure to describe the smart
blobspace which includes the attributes for the smart blobspace (-Df attributes
used at creation time)

- Chunk adjunct partition - information about each chunk in the partition
including location and size of user-data metdata areas and location of the LO
header partition for
each chunk. This contains 1 row per chunk and it in the first chunk of the
sbspace

- LO header partition - For each smart LO in the chunk this describe the
datetime the LO was created, the size of the LO and other attributes and
includes an extent list
allocated to the smart LO. There is one LO header partition per chunk

- UD free list partition which tracks free extents in the user data areas of a
chunk

oncheck -pe will display this information including one line per smartblob

e.g. SBLOBSpace [4,4,1] - Smart LO handle (sbspace,chunk,logical sequence
number).

In addition oncheck -cS will show metadata and extent information including the
partnum for the LO partition header. This can then be fed into oncehck -pD to
get the contents
of the metadata tables.

oncheck -g smb has several options

smb s - sbspaces
smb c - sbspace chunks
smb h - LO header table header
smb e - LO header and FD entries
smb lod - LO header table header and entries
smb fdd - LO file descriptor (FD) entries

The e and lod options provide an list of entries in the LO header table which
represent each smart LO in the sbspace including.

- size Smart LO size
- Smart LO handle

Regards,
David.

On 21 March 2013 at 01:44 MIKE DUNHAM-WILKIE <mike@barrodale.com> wrote:
> I'm looking for a way to determine the amount of space "attributable" to a
> table, including the space taken by any smart large objects referenced by rows
> in the table. I realize that smart large objects technically aren't considered
> part of a single table, since there is can be a many-to-many relationship
> between smart large objects and table rows, but in the case I have in mind
> every smart large object is referenced by a single row of a single table. I've
> found lots of SQL for determining space taken by a table when you don't
> consider smart large objects, but nothing for the case I'm interested in.
> Since table rows contain smart large object references I figure there must be
> a way to follow those references and aggregate the space consumption of all
> the referenced objects.
>
> Thanks for any help you can provide!
>
> Mike
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• space taken by table - including smart large obj,
  MIKE DUNHAM-WILKIE -- Wednesday, 20 March 2013, at 9:44 p.m.
  • Re: space taken by table - including smart large o
    david@smooth1.co.uk -- Thursday, 21 March 2013, at 10:07 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.