On 16-Jul-2014 08:31 -0500, Charles Wilt wrote:
On Wed, Jul 16, 2014 at 12:40 AM, Roger Harman wrote:
After reading a number of posts over time about slow IFS save
Interesting thought...though I assume you meant BLOB instead of
issues, I was wondering.
Would there be any benefit to saving them as CLOB's in a table
instead of as IFS objects?
I have no experience whatsoever with this but just wondering what
are the pro's and con's and if anyone has tried it.
Based on what little I know about how BLOBs are stored and the fact
that the OS backs up large objects faster than a bunch of small
ones, I would expect the answer to be yes.
But I don't know for sure. Might be worthwhile asking IBM about.
I suspect the reduction in time required to save can be rather
dramatic, and as I recall, that is supported empirically, even when
indexing and authority\access-control requirements also are moved into
the database; not sure, perhaps in some /customer experience/ reports.
As I recall, each STMF is stored as a "dataspace" object. However
the STMF is like a database file /member/ in that there is also a
"cursor" object. Thus for 10M STMF, a request to save would have to
/touch/ and page 20M object headers separately. So even if each LOB
were implemented as a separate dataspace object [they are not, except at
run-time they may be], then already the amount of objects paged is
reduced to an absolute maximum of 10M+2; i.e. a request to save that
same data as BLOB in row data would require /touching/ [likely far]
*fewer* than 10M+2 objects, paging the database dataspace and db cursor
and potentially every LOB. IIRC however, each LOB [at least any that
does not overflow some minimal accommodation] is stored as part of the
variable length [variable segment] overflow defined for the
VARLEN\varying-length columns of the] database dataspace; many fewer
objects would need to be /touched/ by the save, than the fixed minimal
number for saving STMFs. Note: LOBs are implicitly VarLen. The overall
number of pages for the actual data would be similar, although due to
rounding for allocations and alignment, the database pages likely can
and will be more compact; thus further reduction in overall data paging
with saving as database LOB data, as contrasted with saving as STMFs.
The following seems to support, at least in part, some of my
You should also be aware that VARCHAR columns with a small allocate
value can cause performance problems if the table also contains LOB
(large object) columns and the VARCHAR column is referenced frequently.
The VARCHAR and LOB columns are all stored in the same auxiliary
overflow storage area. This means that if you reference one of the
columns in that area, the database has to page in all the overflow
storage columns. In general, DB2 UDB for iSeries tries to avoid paging
in large LOB columns, but it can't be avoided when the row contains
VARCHAR columns that are stored in the same auxiliary storage area.