Re: IFS Save Issues - Save as CLOB's instead?

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
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.

Interesting thought...though I assume you meant BLOB instead of
CLOB...

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 recollection:
<http://archive.midrange.com/midrange-l/200911/msg01287.html#>
"...
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.
..."