DB2 - L

 View Only
  • 1.  Use of inline LOBs - is it already widespread?

    Posted Mar 25, 2022 01:22 PM
    Use of inline LOBs,
    Sometimes there are new functionalities in DB2 for z/OS that are used immediately by a large number of companies. But that's not always the case, and
    sometimes you encounter problems that you didn't expect when you use new functionalities early on. Hence my question: Who actually uses INLINE LOBs in an application with high insert rates - e.g. more than 10,000,000 inserts a day?

    Kind regards

    Norbert Wolf

    Datev eG

    NorbertWolfDATEV eG

  • 2.  RE: Use of inline LOBs - is it already widespread?

    Posted Mar 29, 2022 02:16 PM

    I was going to look into a similar situation with some vendor software originally designed for open systems but running on z/os, with a number of (small) LOB's , jokingly called SLOB's. The idea was are any of these LOB's sufficiently small enough to inline (fully or partially) to allow indexing and potentially index only access for queries.

    For a relatively small set of data this application consumed a lot of CPU. but was it related to the LOB's? I Won't get to know, as I got pulled off to higher priority tasks. But here are my random thoughts, that had me want to look into this initially

    What is the cost / benefit of in-lining the LOB?

    what will you save vs the cost of the increased length for each row / less rows per page?
    Will you be able to improve query response times/cpu times using the inlined portion of the LOB (+potential index)?
    Can you bear the cost of the increased row length, larger number of data pages for other queries?
    what is the impact to REORG/COPY elapsed times if I inline the LOB ?
    How much does this increase the logging intensity and volume?


    Jack Campbell
    Saxon Consulting, Inc.

  • 3.  RE: Use of inline LOBs - is it already widespread?

    Posted Mar 29, 2022 04:37 PM
    Jack's answer covers the basic tradeoff - quicker access to the LOB data vs larger rows (and therefore fewer rows per page and possibly lower bufferpool hit ratio).  This tradeoff is very similar to the design question of: if you have 2 tables that are 1 to 1, do you combine them into one table or keep them separate?

    If the majority of your access needs the LOB column data (either in the Select list or in a predicate), then inlining the LOB will probably greatly improve performance.  If only a small subset need the LOB, then the other queries may experience more I/O due to the larger row size.


    Joe Geller

  • 4.  RE: Use of inline LOBs - is it already widespread?

    Posted Mar 30, 2022 12:43 AM
    Hello Joe und Jack,

    There are certainly a number of ideas why and in which scenarios you can use inline LOBs. Some were pointed out in the discussion. Our triggers for thinking about inline LOBs are 2 things. First, in our high insert rate scenario the underlying data is appropriate (98 percent of the thumbnails are smaller than 32 KB) for the usage of inline LOBs and we've seen some serious insert behavior problems when using "classic" LOBs (not inline ) caused by the for "classic"LOBs used insert algorithm.


    NorbertWolfDATEV eG

  • 5.  RE: Use of inline LOBs - is it already widespread?

    Posted Mar 30, 2022 05:29 AM
    Hi guys,

    I know you're talking about Db2 for z/OS, but I'd like to share here some additional information which might be interesting in the same context.
    A customer of us had some performance issues and the magic word in the case was "avoid demoted I/Os".

    There is an official IBM tech note about it: https://www.ibm.com/support/pages/demoted-io-requests-may-lead-db2-performance-problems
    And I found following background article about Demoted-IO: https://www.scribd.com/document/256211670/Detecting-Dio-Performance-Problems

    We used this query to figure out, what tables and columns were affected:

    select current server as dbname, substr(t.OWNER,1,12) as owner, substr(t.TABSCHEMA,1,12) as schema, substr(t.TABNAME,1,20) as tab, substr(c.COLNAME,1,20) as col, t.TBSPACEID, t.TABLEID, t.card, t.stats_time from SYSCAT.COLUMNS c, SYSCAT.TABLES t where c.TYPENAME in ('BLOB','CLOB','LONG VARCHAR') and c.TABNAME = t.TABNAME and t.TBSPACEID <> 0 and t.LONG_TBSPACE is null

    and finally used ADMIN_MOVE_TABLE to move those tables into separate tablespaces for table, index and lob data.

    As said, this is for Db2 LUW, using direct IO for DMS tablespace access and LOBs not separated into a different tablespace (as it is custom in Db2/z). But someone might stumble over the topic and might find this helpful in his scenario.


    Roland Schock
    ARS Computer und Consulting GmbH